Send Multiple Emails From Excel VBA Macro

In a previous post, we have seen how to send an email from Excel (via Outlook) using VBA macros. We can add as many recipients as needed separated by a semi-colon when sending one email. However, if we want to send separate emails to each recipient, we need to loop through the list of recipient emails and send an email to each of them individually. In this post, we see how to send multiple emails to recipients listed in a worksheet in Excel. We can choose to send the same or a different subject, body, or attachments to each recipient.

Macro code:

 
  Sub SendMultipleEmails()
      Dim OutApp As Object, OutMail As Object, lastRow As Integer, r As Integer
      Dim bodyHeader As String, bodyMain As String, bodySignature As String      
      Set OutApp = CreateObject("Outlook.Application")
 
      lastRow = Cells(Rows.Count, "B").End(xlUp).Row
      bodySignature = "Sincerely," & vbLf & "Excel Macro Mania"
 
      For r = 2 To lastRow
          Set OutMail = OutApp.CreateItem(0)
          With OutMail
              .To = Range("C" & r).Value
              .Subject = Range("D2").Value
     
              'email body with various components
              bodyHeader = "Dear " & Range("B" & r).Value & ","
              bodyMain = Range("E2").Value
              .Body = bodyHeader & vbLf & vbLf & bodyMain & vbLf & vbLf & bodySignature
     
              .Attachments.Add Range("F2").Value              
              .Display 'optional
              '.Send 'uncomment to send
          End With
      Next r
      Set OutMail = Nothing
      Set OutApp = Nothing
  End Sub
 

Macro explained:

• First, we declare two object variables to represent the Outlook application (OutApp) and mail item (OutMail) objects respectively. We also have numerical variables to store the last row and row counter, and string variables to store body text. Note that we are using late binding, so we then need to initialize Outlook with CreateObject and assign it to the variable OutApp. We can also do it with early binding (explained in the previous post).
• Now we start a loop through the list of recipients to get the email address and other info needed to send the emails. We start the loop from row 2 (to skip the headers), up to the last row with content in column B (where we have the list of email address for each recipient).
• For each email recipient in the list we perform the following tasks:
• Create a mail item object assigned to OutMail (index 0 corresponds to a mail item)
• We use mail item properties to fill in the necessary information such as recipient email, subject, body, and attachment, referring to the corresponding range/cell. We can choose to have the same or different subject, body, or attachment for each email recipient. In the code above, we use the same subject, main body, and attachment (as per row 2), just changing the body header for each email recipient. In case we have a specific subject, body, or attachment for each email recipient, we just need to update the target range as follows:
•  .Subject = Range("D" & r).Value
   bodyMain = Range("E" & r) .Value
   .Attachments.Add Range("F" & r).Value

• The recipient can be one or more email address separated by a semi-colon (;). We can also add CC and BCC recipients with the respective properties, use HTMLBody to add the email body in that specific format, and add more attachments simply repeating that line of code and referring to the appropriate cell with attachment information. Note that the attachment requires a full path and name of the file to be attached.
• The Display method shows the email in Outlook before sending. If we want to fully automate it, we have to use the Send method (no need for Display).
• Finally, we clear the object variables or set both to nothing – this is good practice when coding VBA.

IMPORTANT: If you do not have Outlook installed and setup in your machine, the macro will not work.

This is how we Send Multiple Emails from Excel using VBA macros.

Other examples:

• How to Convert Range To HTML Table with Excel VBA macros
• How to Export Table To Word with Excel VBA macros
• How to Add Button To Cell Right-Click Menu with Excel VBA macros