How to Send Bulk Email From Outlook Using Excel VBA

Do you need to deliver personalized mass emails to your clients, customers, friends, or any other contacts? Do you want to do that with a single click? That’s possible! There’s a magical tool in Microsoft Excel that does all of these. It’s the Visual Basic for Applications aka Excel VBA.

Continue reading to know how to send bulk email from outlook using Excel VBA. There’s more! You can practice that by following the simplest steps mentioned in the tutorial below. Let’s dig in!

How to Send Bulk Email From Outlook Using Excel VBA

The current tutorial will provide you with the VBA code you need to automate bulk emailing from Excel. All you need to do is gather your data in one Excel file and perform some quick formatting.

The VBA program that you’ll run will use Microsoft Outlook as the email sending app. Thus, you need to install and configure Outlook on your computer. The VBA code won’t be able to use the web browser-based Outlook program.

Once you’re ready with the Excel file and Outlook app, perform these steps:

• Organize the bulk email worksheet into 6 columns.
• Type in column headings in row A1: Email To (A); Email CC (B); Email Subject (C); Email Body (D), Attachment (E), Status (F).
• Add all the “To Addresses” under the column A heading. You can add thousands of emails if you want.
• Similarly, type in the “CC Addresses” below the column B heading.
• It’s time for the personalization of the email subject line. You can add custom email subjects for each contact under the column C heading.
• The same goes for the email body content. Copy-paste email content in the rows beneath the column D heading.
• Copy-paste file attachment locations below the column E heading.
• To get the attachment location, press Shift and select the target file on your computer.
• Now, right-click and select Copy as path to copy the file address in the PC clipboard.
• You can now paste the address directly into the rows of the E column.
• Leave the F column blank as the VBA code will auto-populate statuses.

• Save the Excel sheet as a Macro-enabled file.
• Now, press Alt+F11 to bring up the VBA code editor.
• Click on Tools in the upper menu and then select References.
• Look for the Microsoft Office 16.0 Object Library in the list that follows and check the box.
• Click Ok to save Outlook as an object for VBA.

• Now, click Insert and then choose Module to open a code editor.
• Copy-paste the below-mentioned code in the Module.

Sub Send_Bulk_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Worksheet_Name")
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 emails have been sent"

End Sub

• Enter the current worksheet name separated by underscores in code line 3 where it says (“Worksheet_Name“).
• Click Save from the menu to save the script.
• You can press F5 to run the macro and Excel will automatically send the emails through Outlook.

• You can also access this macro later from the Developer tab of the Excel ribbon menu.

Conclusion

Now that you’ve followed along with how to send bulk emails from outlook using Excel VBA tutorial, you can easily send mass emails without wasting time on manual emailing.

Want to learn another VBA trick for Excel? Go through this tutorial to learn how to automate spell check in Excel using VBA.

Read Full Bio

Tamal Das

Tamal is a freelance writer at AddictiveTips. After gaining substantial experience in technology, finance, and business processes in his previous job in an IT consulting company, he adopted writing as a full-time profession 3 years ago. While not writing about the latest technology, he loves to play COD and binge-watch

Latest from Author

How to Check a Box in Word Doc How to Type an Em Dash: Top 5 Methods