In this article, we have create an automation for send multiple emails on one click. This automation will send the emails using Microsoft Outlook.
Watch the step by step video tutorial:
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.
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