1. Home
  2. MS Office
  3. How to send bulk email from outlook using excel vba

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!

A visualization of a VBA code and Excel logo

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.

How to send bulk email from outlook using Excel VBA worksheet formatting

  • 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.

Changing object reference for sending email with 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.

Sending bulk email using Excel VBA

  • 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.

7 Comments

  1. Hi I tried above code to attach PDF in the email as attachment, I am getting below error while running macro.

    msg.attachments.Add sh.Range(“E” & i).Value

    Please advise how to resolve/ fix it.

    Regards
    Tarun

  2. Thanks for the straightforward explanation on how to create this VBA!

    I have multiple outlook accounts (group emails) and would like to select which one to use to send the bulk emails from. Is there a way to do this?

  3. Kia ora Tamal, awesome code. Can you advise how to get formatting into the email like your example email (breaks between rows). Maybe even how I might copy a table into the email

  4. You can delete my previous comment. I figured out what I had done incorrectly. Worked perfectly! Thanks.

  5. Thanks for the instructions. This is going to be wonderful. When I run the macro, I get the error message “Compile error: End If without block If.” Have you seen that before? Thank you.

  6. It’s awesome work.. helped me a lot. thank you for that.

    but can you please show how to change the sender… i want to change it from my own company account to company’s own account .. thank you in advance

  7. This is awsome..my work time has been drastically reduced, now I could be able to send multiple attachments to multiple recipients ..thanks..

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.