VBA Code for excel to send multiple emails using Outlook

In this tutorial we will explain you, how you can send the multiple e-mails from Microsoft Excel using Outlook. The best part of this code is, each recipient will receive the email individually. Before proceeding further there are some prerequisite:

You need to make the excel sheet format as defined below:

1. Place the list of e-mail addresses under column "A"
2. Place the e-mail subject line in cell "B1"
3. Place the e-mail body message in cell "B2" and then,
4. Use the following VBA code to send the email to each individual email address using MS outlook.

Note: This code will send the e-mail to each e-mail address listed in the Column "A" starting at row 2 down to the last row.

At the moment this code is set to display before sending. If you don't want to see or review each e-mail before sending then, you can disable or comment the display line and enable or un-comment the Send line.

Code:

Dim i As Integer, Mail_Object, Email_Subject, o As Variant, lr As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
Set Mail_Object = CreateObject("Outlook.Application")
For i = 2 To lr
       With Mail_Object.CreateItem(o)
           .Subject = Range("B1").Value
           .To = Range("A" & i).Value
           .Body = Range("B2").Value
           '.Send
           .display 'disable display and enable send to send automatically
    End With
Next i
       MsgBox "E-mail successfully sent", 64
       Application.DisplayAlerts = False
Set Mail_Object = Nothing
End Sub