Send & Receive Issues

How to Send Bulk Outlook Emails with VBA and Attachments in Excel

Guest Writer Oct 9, 2026 868 views

Question details

The user needs to automate the process of sending multiple emails via Outlook directly from an Excel worksheet, assigning specific recipient addresses, subjects, and attachment paths to each email.

How to Send Bulk Outlook Emails with VBA and Attachments in Excel

Product: Excel, Outlook
Device & OS: not provided
Scenario: Automating bulk email generation based on structured Excel data, including dynamic attachments and multiple recipient addresses for each row.
Observed behavior: The goal is to implement a VBA script that iterates through the Excel rows and automatically creates and sends a uniquely configured email for each row using the Outlook application.

Before you start

Verify that your Excel worksheet is organized with consistent column structures (e.g., Column A for Subjects, B and C for To addresses, D for CC, and E for full Attachment File Paths) and ensure the file paths exactly match the actual document locations on your PC.

Solution 1Recommended

Use a VBA Macro to Loop Through Rows and Send Outlook Emails

This solution uses a custom VBA script to connect Excel with Outlook, automatically reading recipient data and file paths to generate and dispatch an email for each row.

By utilizing the 'CreateObject("Outlook.Application")' method, Excel can remotely control Outlook. The macro below loops from the second row down to the last occupied row in column A, reading data to populate the To, CC, Subject, Body, and Attachments fields.

For testing purposes, it is highly recommended to run the code using the '.Display' method first. This allows you to visually inspect the drafted emails without immediately sending them out.

Open the VBA Editor

In your open Excel workbook, press the Alt + F11 keys simultaneously to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

On the top menu bar of the VBA editor, click on Insert and select Module to create a blank workspace for your code.

Paste the Bulk Email Code

Copy and paste the following script into the module window: Sub SendBulkEmails() Dim OutApp As Object, OutMail As Object Dim i As Long Set OutApp = CreateObject("Outlook.Application") For i = 2 To Range("A" & Rows.Count).End(xlUp).Row Set OutMail = OutApp.CreateItem(0) With OutMail .To = Range("B" & i).Value & ";" & Range("C" & i).Value .CC = Range("D" & i).Value .Subject = Range("A" & i).Value .Body = "Hi everyone," & vbCrLf & vbCrLf & "Please find attached the report for " & .Subject & "." & vbCrLf & vbCrLf & "Best regards," .Attachments.Add Range("E" & i).Value .Display 'Change to .Send when ready End With Set OutMail = Nothing Next i Set OutApp = Nothing End Sub

Execute the Macro

Click anywhere inside the pasted code and press the F5 key (or click the green 'Run' triangle icon) to execute the macro. Verify that the Outlook email drafts open on your screen with correct attachments and addresses.

Use a VBA Macro to Loop Through Rows and Send Outlook Emails

Add Error Handling: To prevent the entire macro from stopping when it encounters a single invalid email address or missing attachment file, type 'On Error Resume Next' directly above the 'For i = 2' line, and 'On Error GoTo 0' below the 'Next i' line.

Advanced Automation with WPS

Automate Bulk Emails with WPS Spreadsheets

WPS Office features comprehensive VBA and macro support, allowing you to seamlessly run automation scripts—like bulk-emailing via Outlook—directly within WPS Spreadsheets without needing to rewrite your code.

1. Open your dataset in WPS: Launch WPS Spreadsheets and open your workbook containing the structured email addresses and attachment file paths.
2. Access the Developer Tools: Navigate to the 'Developer' tab on the main ribbon and click on the 'VBA Editor' icon to open the macro environment.
3. Run the Mail Macro: Insert a new module, paste your standard bulk email VBA script, and press F5 to execute the macro seamlessly, just as you would in Microsoft Excel.

Fully compatible with Microsoft Excel macro (.xlsm) formatsSupports standard VBA syntax for automating complex administrative tasksLightweight application that runs smoothly even with large datasetsSeamless integration with system-level mail clients

microsoft office alternative - wps office

Frequently Asked Questions

Why is the VBA macro failing on the Attachments.Add line?

This error typically occurs if the file path provided in your worksheet is misspelled, formatted incorrectly, or if the file no longer exists at that location. Double-check your path strings (e.g., C:\Reports\file.pdf) and verify you have system read permissions for the file.

How can I prevent Outlook from displaying a security warning for every email?

Outlook may prompt a security warning when external programs try to send emails on your behalf. To suppress this, ensure your antivirus software is up to date and recognized by Windows, or adjust the 'Programmatic Access Security' settings within the Outlook Trust Center Options.

Can I use rich HTML formatting in the VBA email body?

Yes. Instead of assigning text to the '.Body' property, use '.HTMLBody'. This allows you to construct the email using standard HTML markup, such as <br> for line breaks, <b> for bolding text, and <a> for inserting clickable hyperlinks.

How do I test the VBA macro without actually sending the emails?

In your VBA script, replace the '.Send' command with '.Display'. Running the script will generate and open all the email drafts on your desktop, giving you an opportunity to review the recipients, subject lines, body text, and attachments before dispatching them.

Outlook & Email Problems