Using VBA to Send Emails in Outlook

It’s easy to draft several emails and send them out using Outlook. But imagine if you need to send out hundreds of emails that address different people by name or that contain slightly different information. It would be daunting to write one after another and change just a little bit in each email. That’s why using VBA is much easier than using the CC or BCC function on hundreds of separate emails.

Say, for example, you want to send separate emails to all of your friends for Thanksgiving. You will need to list all of the recipients’ email addresses, subjects, email bodies, and the people being addressed in the “EmailList” worksheet, as demonstrated in the screenshot below:

Next, you can find the “Developer” tab on the top ribbon of Excel. On the “Developer” tab, you can find “Visual Basic.” Once you click “Visual Basic,” there will be a drop-down menu on the top left of the new screen where you can find “Module.” Once you click this, it opens the VBA editor. Alternatively, you can press “Alt + F11” to open the VBA editor. In order to access Outlook features, we need to check the “Microsoft Outlook 16.0 Object Library” box under “References...” on the “Tools” tab in VBA. A screenshot is provided below:

Then, you can write the following code in the VBA editor:

Sub EmailsThroughVBA()

'Create references to Application and MailItem Objects of Outlook
Dim outlookApp As Outlook.Application
Dim outlookMail As Outlook.MailItem
   
'Activate the "EmailList" worksheet
ThisWorkbook.Sheets("EmailList").Activate
  
'Get last row of the first column that contains Email
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  
'Initialize outlook object to access its features
Set outlookApp = New Outlook.Application
  
'Set the range to a variable and access all Emails from the variable
Set Emails = Range("A2:A" & lastRow)
  
'Loop through each Email and obtain their subject and Email message
For Each Email In Emails
    sendTo = Range(Email.Address).Offset(0, 0).Value
    subj = Range(Email.Address).Offset(0, 1).Value
    msg = Range(Email.Address).Offset(0, 2).Value
  
    'Create an instance for a new Email
    Set outMail = outlookApp.CreateItem(0)
      
    'Write and send Email
    With outMail
        .To = sendTo
        .Subject = subj
        .HTMLBody = msg
        .Send
          
    End With
      
    'Clear outmail object for next Email
    Set outMail = Nothing
      
  Next Email
  
End Sub 

The result of the code above looks like this:

With this code, you can effortlessly send out hundreds of emails with just one click.