How to Send bulk emails using VBA and Outlook?

You need to fill the below given template to send the multiple email.

In column “A”, fill “To” email Id.
In column “B”, fill the “CC” email id. You can keep it blank, if you don’t want keep anybody in CC.
In the column “C”, put the subject of email.
In the column “D”, put the mail body content.
Column “E” for attachments, Provide the complete path and file name with file extension. You can keep it blank, if you don’t want to attach file.
Column “F” is for status. This is an auto fill column. You don’t need to fill it. Once automation will sent the email it will filled automatically.

Click to buy Send bulk emails using VBA and Outlook

Below is VBA code which we have assigned on “Click to send mails” button.

Sub Send_Mails()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Send_Mails")

Dim i As Integer



Dim OA As Object

Dim msg As Object



Set OA = CreateObject("outlook.application")



Dim last_row As Integer

last_row = Application.CountA(sh.Range("A:A"))



For i = 2 To last_row

Set msg = OA.createitem(0)

msg.to = sh.Range("A" & i).Value

msg.cc = sh.Range("B" & i).Value

msg.Subject = sh.Range("C" & i).Value

msg.body = sh.Range("D" & i).Value



If sh.Range("E" & i).Value <> "" Then

msg.attachments.Add sh.Range("E" & i).Value

End If



msg.send



sh.Range("F" & i).Value = "Sent"



Next i



MsgBox "All the mails have been sent successfully"



End Sub

Click to buy Send bulk emails using VBA and Outlook

Want the ready-made Send Bulk Emails Using VBA and Outlook?

Skip the build and get the finished, fully working template, ready to use with your own data.

Click to Buy on NextGenTemplates.com →