Send email from Excel to Multiple recipients using VBA and Outlook

Note: You must have Ms-Office Outlook installed in your computer. Therefore, please make sure you have the latest Outlook installed and configured.

Now, let us assume in your Excel worksheet, you have a list of email ids of your clients and you want to send emails to all those ids (all of it at one go), with the click of a button. See the picture below.

The worksheet has two columns, name and email id. It also has a button (an ActiveX control). I’ll write the macro (the VBA code) inside the button's click event.

After you have opened the Excel file (it should be a Macro enabled file), add a button control in your worksheet. We'll write the macro in the button's click event.

Next, press Alt+F11 keys together to open the VBA editor. In the project explore, find Sheet1 and open it. Now, write the below code.

👉 Remember: Before you start working with the macro, you must add a Reference of Microsoft Outlook 16.0 Object Library (version 12.0 or later). I have explained it here.

Option Explicit

Private Sub CommandButton1_Click()

    On Error GoTo ErrHandler
    
    ' Set Outlook object.
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    ' Create email object.
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
    
    Dim myDataRng As Range
   
    ' We'll now set a range.
    Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    Dim cell As Range
    Dim iCnt As Integer             ' Its just a counter.
    Dim sMail_ids As String         ' To store recipients email ids.
    
    ' Run a loop to extract email ids from the 2nd column.
    For Each cell In myDataRng
        If Trim(sMail_ids) = "" Then
            sMail_ids = cell.Offset(1, 0).Value
        Else
            sMail_ids = sMail_ids & vbCrLf & ";" & cell.Offset(1, 0).Value
        End If
    Next cell
    
    Set myDataRng = Nothing         ' Clear the range.
    
    With objEmail
        .to = sMail_ids    ' Assign all email ids to the property.
        .Subject = "This is a test message from Arun Banik"
        .Body = "Hi, there. Hope you are doing well."
        .Display        ' Display outlook message window.
    End With
    
    ' Clear all objects.
    Set objEmail = Nothing:    Set objOutlook = Nothing
ErrHandler:
    '
End Sub

Do you know... you can email to multiple recipients from Excel using VBA but without Outlook. There is an alternative method. Check it out.

Let me explain. To get access to Microsoft Outlook methods and properties in VBA, you'll have to first create an instant of Outlook, using the "CreateObject" function. That's what I did in the beginning of the code.

' Set Outlook object.
Dim objOutlook As Object
Set objOutlook = CreateObject("Outlook.Application")

I have also created an email object. Set objEmail = objOutlook.CreateItem(olMailItem)

Next, to get access to all the email ids from my worksheet, I’ll define a Range. The email ids are in 2nd column. Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)

What is a Named Range in Excel? Read these posts here and here.

Using a For Each … loop, I’ll iterate through each row in the specified range to extract the email ids and store the values in a variable.

Finally, send an email to each recipient’s id.

In the above example, the variable that has the id’s are assigned to the .to property of the objEmail object.

.to = sMail_ids

Now when you click the button, it will open the Outlook message window, which will have all the emails ids in the "To..." box, with the subject and body. If you are ready, click the Send button. See the image below.

Send email to multiple recipients as CC (or Carbon Copy) in Excel

Similarly, you can assign one (the first) id to the .to property and assign other remaining ids to the .CC property.

Option Explicit

Private Sub CommandButton1_Click()
    On Error GoTo ErrHandler
    
    Dim objOutlook As Object
    Set objOutlook = CreateObject("Outlook.Application")
    
    Dim objEmail As Object
    Set objEmail = objOutlook.CreateItem(olMailItem)
    
    Dim myDataRng As Range
    Set myDataRng = Range("B1:B10" & Cells(Rows.Count, "B").End(xlUp).Row)
    
    Dim cell As Range
    Dim iCnt As Integer

    Dim sTo_Mail_id, sCC_Mail_id As String         ' To store recipients email ids.
    
    ' Run a loop to extract email ids from the 2nd column.
    For Each cell In myDataRng
        If Trim(sTo_Mail_id) = "" Then
            sTo_Mail_id = cell.Offset(1, 0).Value
        Else
            ' ids for CC.
            If Trim(sCC_Mail_id) = "" Then
                sCC_Mail_id = cell.Offset(1, 0).Value
            Else
                sCC_Mail_id = sCC_Mail_id & vbCrLf & ";" & cell.Offset(1, 0).Value
            End If
        End If
    Next cell
    
    Set myDataRng = Nothing         ' Clear the range.

    With objEmail
        .To = sTo_Mail_id
        .CC = sCC_Mail_id
        .Subject = "This is a test message from Arun"
        .Body = "Hi, there. Hope you are doing well."
        .Display
    End With

    Set objEmail = Nothing:    Set objOutlook = Nothing
ErrHandler:
    '
End Sub

If you want to send the emails using BCC, simply use the .BCC property (instead of .CC) like this,

.BCC = sMail_id

Remember, the properties are case sensitive. Define the properties carefully.

Now, if you want to add attachments with all the emails that you wish to send. I have explained the attachment and other properties here.

Thanks for reading. ☺

← PreviousNext →