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.
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
sh.Range("F" & i).Value = "Sent"
MsgBox "All the mails have been sent successfully"
Meet PK, the founder of PK-AnExcelExpert.com! With over 15 years of experience in Data Visualization, Excel Automation, and dashboard creation. PK is a Microsoft Certified Professional who has a passion for all things in Excel. PK loves to explore new and innovative ways to use Excel and is always eager to share his knowledge with others. With an eye for detail and a commitment to excellence, PK has become a go-to expert in the world of Excel. Whether you're looking to create stunning visualizations or streamline your workflow with automation, PK has the skills and expertise to help you succeed. Join the many satisfied clients who have benefited from PK's services and see how he can take your Excel skills to the next level!
While create the Data Entry form in VBA, We need to put some validation in text boxes and combo boxes.
Privacy & Cookies Policy
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.