How to Send Bulk Email From Outlook Using Excel VBA

Importance Of Outlook in the Office

You may search not only for emails in Microsoft Outlook but also for calendar events, tasks, and contacts. It is standard fare for any email client. It’s a one-stop shop for accessing any of Microsoft Outlook’s many features and functions.

We have little doubt that you have received emails from your co-workers outlining plans for upcoming meetings. Microsoft Outlook allows users to integrate meeting details, calendar events, and email correspondence into one convenient location. All of next week’s commitments are easily accessible with a single click.

We can all attest that fraud and scam emails pose a growing threat. Even when we are well-versed in what we should avoid, it is easy to get distracted by life’s many demands and forget to exercise caution. Thanks to Microsoft Outlook’s built-in email scanner, you will be notified if there’s a problem with your email. Microsoft Outlook’s download function has been thoroughly inspected and verified as safe for even greater peace of mind. The system’s configuration also allows you to turn off live links and block specific individuals at any moment.

Although Microsoft Outlook has been around for quite some time, it has undergone consistent development and improvement. Regrettably, not everyone uses the service to its full potential since they are unaware of how user-friendly it is or the additional features that can be added.

It’s important to remember that having everything in one place means you can better manage your time and get more done when you are not connected to the internet. Although Microsoft Outlook has been around for quite some time, it has undergone consistent development and improvement. Regrettably, not everyone uses the service to its full potential since they are unaware of how user-friendly it is or the additional features that can be added.

It’s important to remember that having everything in one place means you can better manage your time and get more done when you are not connected to the internet.

VBA Code to Send Bulk Email in Outlook

Do you need to send many customised emails to clients, customers, friends, or other contacts? Do you prefer a one-click solution? Yes, that could work! All these tasks may be easily accomplished with the help of a single miraculous tool in Microsoft Excel.

That is Excel VBA or Visual Basic for Applications. Follow this guide to get the Excel VBA code you need to send mass emails automatically. A single Excel file and some simple formatting are all that is required. When you run the VBA code, Microsoft Outlook helps send emails. It means you must download Outlook and set it up on your PC.

It would be best if you had an Excel document and Outlook; follow these steps:

Step 1: Split the mass emailing spreadsheet into six columns.

Step 2: In row A1, create the column headings “Email To,” “Email CC,” “Email Subject,” “Email Body,” “Attachment,” and “Status” (F).

Step 3: Fill out column A with all the “To” addresses. Thousands of email addresses can be added if desired.

Step 4: Enter the “CC Addresses” below the column B heading in the same fashion.

Step 5: The moment to customise your email’s subject line. For each contact, you’ll find a column labelled “C”, where you may provide personalised subject lines for their emails.

Step 6: The message body of an email should be treated the same way. Fill out the rows under column D with your copied email content.

Step 7: Use the space below column E to paste the addresses of any attached files.

Step 8: Attachment locations can be retrieved by selecting the desired file on your computer while holding down the Shift key.

Step 9: To save the file’s location in the computer’s temporary storage, right-click on it and pick the Copy as a path option.

Step 10: Pasting the address into the E column rows is now possible.

Step 11: To have the VBA code automatically fill in the statuses, leave the F column blank. It would be best if you could save the Excel document with the Macro functions turned on.

Step 12: Open the VBA code editor by pressing the Alt key and F11 key simultaneously.

Step 13: In the main menu, pick Tools and then References.

Step 14: Select the Microsoft Office 16.0 Object Library in the following list.

Step 15: Just hit the OK button to store Outlook as a VBA object.

Step 16: You can access a code editor by selecting Insert followed by Module.

Step 17: Paste this code into the Module

Sub Send_Bulk_Outlook_Mails()

Dim sht As Worksheet

Set sht = ThisWorkbook.Sheets("SheetName")

Dim i As Integer

Dim oOutlook As Object

Dim msgOutlook As Object

Set oOutlook = CreateObject("outlook.application")

Dim last_row As Integer

last_row_number = Application.CountA(sht.Range("A:A"))

For i = 2 To last_row_number

Set msgOutlook = oOutlook.createitem(0)

msgOutlook.to = sht.Range("A" & i).Value

msgOutlook.cc = sht.Range("B" & i).Value

msgOutlook.Subject = sht.Range("C" & i).Value

msgOutlook.body = sht.Range("D" & i).Value

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

msgOutlook.attachments.Add sht.Range("E" & i).Value

End If

msgOutlook.send

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

Next i

MsgBox "Email sent successfully"

End Sub

Step 18: In the third line of code, where it says “Worksheet Name,” type the name of the active worksheet separated by underscores.

Step 19: Select Save from the file menu to store the script.

Step 20: Press F5 to activate the macro and use Outlook to send emails from within Excel automatically.

Sub Send_Bulk_Outlook_Mails_Example()

Dim A As Double

Dim OutApp As Object

Dim OutMail As Object

Set OutApp = New Outlook.Application

OutApp.Session.Logon

Set OutMail = OutApp.CreateItem(0)

With OutMail

.To = "email here.."

.CC = "email here.."

.BCC = "email here.."

.Subject = txtBox_Suject

.HTMLBody = "<HTML><H4>Dear,</H4><BODY> Please enter the message text here. </BODY><H4>Thanks</H4></HTML>"

.Display

.Attachments.Add "Attachment here.."

.Send

End With

End Sub

Benefits of Sending Bulk Email From Outlook Using Excel VBA

Visual Basic for Applications makes it easier to operate Outlook within Outlook. Outlook VBA macros automate complex or repetitive operations. You can write code to respond to Outlook events, automating typical processes (such as arranging windows when Outlook starts).