Send Bulk Emails From Excel VBA Macro

Learn How to Send Bulk Emails from Excel Using VBA Macro: Sending Separate Emails to Each Recipient for Maximum Impact! Discover how to effortlessly send personalized emails to multiple recipients listed in an Excel worksheet, tailoring subject lines, bodies, and attachments for a truly engaging outreach strategy.

To send Bulk Emails From Excel follow the below step

1. Create a Table column as a screenshot or watch a video.
2. Integrate Outlook into the computer system.
3. Go to the developer Tab( if not show please watch the video to enable it).
4. Go to the developer tab–Click on Visual Basic.
5. Insert New Module and paste Macro VBA Code.

Sub SendBulkEmails()

Dim MsgApp As Object, OutMailMsg As Object, lastRow As Integer, r As Integer

Dim msgheader As String, MainMsg As String, Signature As String

Set MsgApp = CreateObject("Outlook.Application")lastRow = Cells(Rows.Count, "B").End(xlUp).Row

Signature = "Sincerely," & vbLf & "DevTech Edu Academy"

For r = 2 To lastRow
Set OutMailMsg = MsgApp.CreateItem(0)
With OutMailMsg
.To = Range("C" & r).Value
.Subject = Range("D2").Value

'email body with various components
msgheader = "Dear " & Range("B" & r).Value & ","
MainMsg = Range("E2").Value
.Body = msgheader & vbLf & vbLf & MainMsg & vbLf & vbLf & Signature

.Attachments.Add Range("F2").Value
.Display 'optional
'.Send 'uncomment to send
End With
Next r
Set OutMailMsg = Nothing
Set MsgApp = Nothing
MsgBox "Bulk emails sent successfully!", vbInformation
End Sub

Detail Code Explanation:

• Here we have declared two object variables to represent the Outlook application (OutApp) and mail item (OutMail) objects respectively. We also have numerical variables to store the last row and row counter, and string variables to store body text. Note that we are using late binding, so we then need to initialize Outlook with CreateObject and assign it to the variable OutApp.
• We use a loop through the list of recipients to get the email address and other info needed to send the emails. We start the loop from row 2 (to skip the headers), up to the last row with content in column B (where we have the list of email addresses for each recipient).

Each email recipient in the list we have: Send Bulk Emails

1. 1. Create a mail item object assigned to OutMailMsg.
   2. We use the details from our Excel sheet, like recipient email addresses, subject lines, message bodies, and attachments, to fill out each email. Depending on what we want, we can use the same information for all emails or customize it for each recipient. In the example above, we’re using the same subject, message, and attachment for all recipients, just changing a small part of the message for each person. But if we have different information for each recipient, we just need to update the specific parts in our Excel sheet.
   3.  .Subject = Range(“D” & r).Value
       bodyMain = Range(“E” & r) .Value
       .Attachments.Add Range(“F” & r).Value
   4. You can send emails to one or more people by listing their email addresses separated by a semicolon. You can also add people to the CC and BCC fields. If you want to format your email using HTML, you can use the HTMLBody property. To attach files, just repeat the line of code for each attachment, making sure to provide the full file path and name.

   5. The Display method(.Display) shows the email in Outlook before sending. If we don’t want to show, have to use the Send method (.Send).

   6. Clear the object variables or set both to nothing.

1. MsgBox “Bulk emails sent successfully!”, vbInformation

6. Save this macro  and go to the Excel sheet insert the button from the Developer tab

Assign Macro to Button and run this macro to send msg.

Conclusion:  Send Bulk Emails

Sending bulk emails from Excel using VBA macros is a powerful way to streamline your communication tasks, whether it’s for business outreach, marketing campaigns, or internal announcements. By automating the email-sending process, you can save time, reduce manual errors, and manage large lists of recipients efficiently.

Radha Krishna radha krishna krishna radha hare hare hare ram hare ram ram ram hare hare

Online Class Playlist 

How To Combine Multiple Excel Sheets with Pivot Tables (Use of Power Query)