Send an email to multiple recipients using Excel and Outlook

This article shows how to code a VBA macro to send a customized email to multiple recipients using Excel and Outlook. Additionally, it shows how the Code VBA add-in helps create the code easily by using its builders.

1. Create a loop to process rows/cells with recipients to create the email for;
2. Create the email object in Outlook;
3. Make an email body with customized salutation.

Before starting, create a table in Excel. In the worksheet with the table data, select the table range and from the ribbon group Insert select Table. After you pressed OK on the Insert Table dialog, the range gets a special formatting, and the ability to sort and filter becomes available. In addition, the range has acquired a name, e.g. 'Table1'.

Create a loop over the Excel Table

In a module, create an empty Sub 'MailingDemo' for the macro and place the cursor in it. We want to loop the rows with the data of Table1, but not the header. Conveniently, the ListObject

From the Code VBA menu, select Object » Table1 » ListObject » Range » Rows. This introduces a variable rngRows which is set to the Rows range of the DataBodyRange (the data!) of the ListObject linked to the Range named 'Table1':

Dim rngRows As Range
Set rngRows = Range("Table1").ListObject.DataBodyRange.Rows

We now move to the next line and create the For Each-loop over the range rngRows: Code VBA menu: Object » rngRows » For Each

This adds the code

Dim rngRow As Range
For Each rngRow In rngRows
    With rngRow
        <cursor>   
    End With
Next rngRow

We now need code that, in a row, moves from cell to cell to assign the values to string variables which we will use in the created email. When we add For Each code the same way as with rngRows for rngRow. This code does however not iterate the Cells. To achieve this, we need to add .Cells.

With rngRow
    Dim rng As Range
    For Each rng In rngRow.Cells  'Add The Cells property'
        With rng
            <cursor>
        End With
    Next rng
       
End With

Declare variables to pick up the values of the cells in the row for later use

We loop over the cells in the row and assign the value to a new variable depending on which Column the cell is.

We create a compact version of the Select Casestatement to handle the three columns using the If/Select Builder

From the Code VBA menu, select If » If/Select Builder. Because the statements for each case are simple and short, we use the Compact option.

Three variables are assigned from the corresponding to the three columns...

First type the column number (1) before the : to specify which cell value is handled by this case. Next, from the Code VBA menu, select Variable » Variable Builder. Select Type String and enter the variable name - the prefix str is automatically added. After Ok, the declaration is added to the top of the procedure, but the cursor does not move. You now can Control-Paste the variable name that was added to the cloboard by the builder. Finish by assigning the = .Value to the variable. Repeat the process for the other two columns.

Complete the select case as follows, and add the declaration of the string variables on top of the macro.

Select Case .Column
    Case 1: strLastName = .Value
    Case 2: strFirstName = .Value
    Case 3: strEmailAddress = .Value
End Select

We now have a loop making the required data available for the recipients and continue with adding code to create the emails in Outlook and showing how the data can be used there.

Using the Outlook MailItem

We want to send an email to each recipient. To achieve this we place the cursor on an empty line after Next rng.
Now on the Code VBA menu select Outlook > MailItem > Create

This opens a Fragment Builder dialog to specify the use of the MailItem object:

• Select for the To property;
• Enter text for subject line;
• Agree to have a new String variable strBody created to set the Body;
• Accept the default value .Display for the MailItem's Action setting. You will probably change this to .Send once things are working to your satisfaction.

After OK, the following code block is inserted

Dim mimEmail As Outlook.MailItem
Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application
 Dim strBody As String: strBody =
Set mimEmail = appOutlook.CreateItem(olMailItem)
With mimEmail
    .To = strEmailAddess
    .Subject = "Taskforce meetin"
    .Body = strBody
    .Display
End With

We don't want to call the opening of Outlook for each individual record, so we move the line with New Outlook.Application more to the top, outside the Do While loop.

Make a body with customized salutation

The line with variable strBody still needs completion. Put the cursor after the '=' and press Shift-Space to use Code VBA IntelliSense. This opens a context menu with many options to build your string from. Here, we chose the String Builder to make the combining text with variables easy.

As you see, the string builder dialog is filled with the email's invitation text. Using the variables we have a customized email with for each recipient a separate salutation. We added the first and last name variables from the popup selection box and the Add button. Pressing OK inserts the code.

strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
    "Just a reminder that our meeting to discuss the environment is later this week. See you Thursday!"

You can now run the macro (F5). The emails are opened in draft. If you extended this code to your satisfaction and all is good, change .Draft to .Send for future enjoy.

Some of the resulting emails:

Sub MailingDemo()   
Dim strEmailAddress As String
Dim strFirstName As String
Dim strLastName As String
    Dim appOutlook As Outlook.Application: Set appOutlook = New Outlook.Application 

    Dim rngRows As Range
    Set rngRows = Range("Table1").ListObject.DataBodyRange.RowsDim rngRow As Range
    Dim rngRow As Range
    For Each rngRow In rngRows
        Dim rng As Range
        For Each rng In rngRow.Cells
            With rng
                Case 1: strLastName = .Value
                Case 2: strFirstName = .Value
                Case 3: strEmailAddress = .Value
            End With
        Next rng
        
        Dim mimEmail As Outlook.MailItem
        Dim strBody As String: strBody = "Dear " & strFirstName & " " & strLastName & "," & vbNewLine & _
                                            "Just a reminder that our meeting to discuss the environment is later this week. See you Thursday!"
        Set mimEmail = appOutlook.CreateItem(olMailItem)
        With mimEmail
            .To = strEmailAddress
            .Subject = "Taskforce meeting"
            .Body = strBody
            .Display
        End With
    Next rngRow
End Sub