VBA Email

---

The VBA code makes it possible to send emails, the simplest way to do this is with the help of Outlook.

---

Enabling the Outlook Library

As seen in VBA Libraries, it is first necessary to enable the reference library by performing the procedure in the Early Binding format.

For this:

1. In VBE ( + ), on the Menu Bar, click Tools
2. Open References (the References - VBAProject dialog box will appear)
3. Select the Microsoft Outlook Object Library
4. Click OK

Remember that changing the code for Late Binding allows for greater backward versions compatibility.

Be sure to set up an account in Outlook before you run the macros.

---

Simple Email

Choose between the Early or Late.

Code for Early Binding

In this case enable the Outlook library as explained above.

    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem

    Set OutApp = New Outlook.Application 'Object for Outlook
    Set OutMail = OutApp.CreateItem(olMailItem) 'Object for mail within Outlook

Code for Late Binding

    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application") 'Object for Outlook
    Set OutMail = OutApp.CreateItem(0) 'Object for mail within Outlook

We associate values to some properties of the OutMail object for the email construction.

With OutMail
    .body = "Hello," & vbNewLine & "This is an example email"
    .Subject = "Example of subject"
    .To = "RECIPIENT_EXAMPLE@EMAIL.COM" 'Replace the email of this line
    .Display 'Method to show content
End With

The .Display method assists in constructing the code, since it leaves the email visible for validation, although it is not necessary for the code to work.

If everything is as planned just send it to the recipient:

OutMail.Send 'Send email

As best practices, disassociate objects with keyword Nothing to clear them from memory.

  Set OutMail = Nothing
  Set OutApp = Nothing

We will then have the following process:

Sub simpleEmail()

    'Using Late Binding
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application") 'Objeto para Outlook
    Set OutMail = OutApp.CreateItem(0) 'Objeto para email dentro do Outlook

    With OutMail
        .body = "Hello," & vbNewLine & "This is an example email"
        .Subject = "Example of subject"
        .To = "RECIPIENT_EXAMPLE@EMAIL.COM" 'Replace the email of this line
        .Display 'Method to show content
    End With

    OutMail.Send 'Send email

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

---

Email with Attachment and Image

We can build a macro to send an email with an attached report and its preview as an image in the body of the message.

For this exercise we will have two Workbooks: SalesReport.xlsx (the report) and SendingEmail.xlsm (file with the macro).

Preview of SalesReport.xlsx

Within a module in SendingEmail.xlsm we can add the following code:

Sub sendEmail()
    'Declaration of variables
    Dim i as integer
    Dim WBk As New Workbook
    Dim reportSheet As Worksheet
    Dim Rng As Range
    Dim ReportFile As String
    Dim OutApp As Object
    Dim OutMail As Object

    'Set the report path
    ReportFile = "C:\...\SalesReport.xlsx" 'Replace with the path where SalesReport.xlsx is

    Set WBk = Workbooks.Open(Filename:=ReportFile, ReadOnly:=False, Notify:=False)

    ReportName = Right(ReportFile, (Len(ReportFile) - InStrRev(ReportFile, "\")))
    Set WBk = Workbooks(ReportName)
    'The above code is equivalent to: Set WBk = Workbooks("SalesReport.xlsx")

    Set reportSheet = WBk.Worksheets(1) 'Report tab from where an image will be generated

    'Range at which the image will be generated
    Set Rng = reportSheet.Range("A1:E6")

    'A tab called "Temp" will be created to temporarily store the messages of the body of the email
    Worksheets.Add After:=WBk.Sheets(WBk.Sheets.Count)
    ActiveSheet.Name = "Temp"

    'Email Body Text
    Cells(1, 1) = "Dear all,"
    Cells(2, 1) = "Below is the preview of the results report."

    'We will use the WordEditor property to paste into the body of the email
    'It is important to load Outlook so that the WordEditor property works properly
    Shell ("C:\Program Files (x86)\Microsoft Office\root\Office16\OUTLOOK.EXE")
    Application.Wait (Now + TimeValue("0:00:05")) 'Wait for Outlook to open

    'Early Binding
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    With OutMail
        .to = "RECEIVER@EMAIL.COM" 'Enter the recipients as String
        .CC = "COPIED1@EMAIL.COM; COPIED2@EMAIL.COM" 'Here are copied, also as String
        .Subject = "Results report" 'Email subject
        .Display
    End With

    Set wEditor = OutApp.ActiveInspector.WordEditor
    'Using WordEditor to paste elements in the body of the email

    OutApp.ActiveWindow.Activate 'Enable email window for pasting
    For i = 1 To 2
        Cells(i, 1).Select
        Cells(i, 1).Copy

        wEditor.Application.Selection.PasteSpecial xlPasteValues
    Next i

    reportSheet.Activate
    reportSheet.Range(Rng.Address).Select

    Selection.CopyPicture xlScreen, xlPicture 'Copy as image
    wEditor.Application.Selection.Paste

    'Anexando o arquivo SalesReport.xlsx no email
    With OutMail
        .Attachments.Add (ReportFile)
    End With

    Application.DisplayAlerts = False 'Disable the message alert for non-stop macro
    Worksheets("Temp").Delete 'Removal of the tab created only to contain the text that will be sent
    Application.DisplayAlerts = True

    'Run up to here, if you do not want to send the email
    OutMail.Send 'Enviar o email

    'Disassociate variables
    Set WBk = Nothing
    Set reportSheet = Nothing
    Set OutApp = Nothing
    Set OutMail = Nothing
    Set wEditor = Nothing

End Sub

Note that in this example the WordEditor property was used to perform collage-related tasks in the body of the email, unlike the simple example previously demonstrated.

WordEditor is a way to paste images together with text.

---

---

Consolidating Your Learning

Suggested Exercises

---

• Previous page
• Next page

---

• report error/feedback