davesexcel.com

Excel information

Email from Excel using VBA

Click to add text, images, and other content

Excel Email Macro- using Office Outlook

Notice the ranges used to for email address, cc,bcc, subject and body.

Sub SendEmail()
    Dim OutlookApp As Object
    Dim mItem As Object
    Dim Cell As Range
    Dim Subj As String
    Dim EmailAddr As String
    Dim Body As String
    Dim cc As String
    Dim bcc As String

    Set OutlookApp = CreateObject("Outlook.Application")

    EmailAddr = Range("B1")
    cc = Range("B2")
    bcc = Range("B3")
    Subj = Range("B4")
    Body = Range("B6")

    Set mItem = OutlookApp.createitem(0)

    With mItem
        .To = EmailAddr
        .Subject = Subj
        .Body = Body
        .cc = cc
        .bcc = bcc
        .display
        ' .send   'use this when you want to send.
    End With

ExitPoint:
    Set OLMsg = Nothing

End Sub

 

Here is a sample workbook

 

Email using Outlook.xlsm