Creating a Mail Merge Email in Excel VBA

The previous example can be easily adapted to send a mail merge email to multiple recipients whose addresses are stored in an Excel worksheet.

Sub MailMergeEmail()
    Dim appOutlook As Outlook.Application
    Dim MailItem As Outlook.MailItem
    Dim i As Integer
    Dim BccList As String
    ' Build list of recipients from the worksheet
    ThisWorkbook.Worksheets("Sheet3").Activate
    i = 1
    Do While Cells(i, 3) <> ""
        BccList = BccList & Cells(i, 3).Value & ";"
        i = i + 1
    Loop
    ' Remove the trailing semicolon
    BccList = Left(BccList, Len(BccList) - 1)
    ' Start Outlook and create an email
    Set appOutlook = CreateObject("Outlook.Application")
    Set MailItem = appOutlook.CreateItem(olMailItem)
    ' Set properties and send
    MailItem.To = "moyofranck@gmail.com"
    MailItem.BCC = BccList
    MailItem.Subject = "Barbecue Party at 5 PM"
    MailItem.Send
    ' Quit Outlook and clean up
    appOutlook.Quit
    Set MailItem = Nothing
    Set appOutlook = Nothing
End Sub

Explanation:

• An Excel worksheet contains names, first names, and email addresses of several people.
• A loop constructs a string of all email addresses from column 3 of the worksheet, separating each with a semicolon. The trailing semicolon is removed using the Left() and Len() functions.
• The email is created similarly to the previous example, with the properties To, Bcc, and Subject assigned.
• The Bcc property receives the constructed string of recipients to ensure each recipient’s address remains hidden from others.
• The email is sent using the Send() method, placing it in the Outlook Outbox.