VBA & Macro Problems

How to Create Outlook Emails from Excel with VBA and Attachments

WPS Editor Oct 9, 2026 868 views

Question details

The user needs to automate the process of generating multiple Outlook emails with custom recipients, CCs, subjects, body text, signatures, and attachments using an Excel VBA macro.

How to Create Outlook Emails from Excel Using VBA with Attachments

Product: Excel
Device & OS: not provided
Scenario: Automating batch email generation from a spreadsheet containing recipient lists, subject lines, and message body data.
Observed behavior: The user wants to efficiently read row-by-row data from an Excel sheet to populate and display or send about 50 Outlook emails, including specific attachments and automatic signatures.

Before you start

Ensure you have the Microsoft Outlook Desktop application installed, logged in, and set as your default email client before running the VBA macro.

Solution 1Recommended

Create Outlook Emails using a VBA Loop

Use a VBA script to loop through your dataset row by row, creating an individual Outlook email for each entry while pulling data from specific columns.

This approach uses the Outlook Application object within VBA to automate email creation. By assigning different columns to corresponding email fields (To, CC, Subject, Body), you can batch-process dozens of customized emails quickly.

Prepare your Excel Data

Organize your data into specific columns: put the recipient list in Column A, CC addresses in Column E, formula-generated subjects in Column F, and message bodies in Column L.

Open the VBA Editor

Press ALT + F11 in Excel to open the Visual Basic for Applications editor. Go to Insert > Module to create a new blank script window.

Write the VBA Loop Script

Write a VBA loop starting from the first data row down to the last used row. Inside the loop, declare an Outlook.Application object and use the .CreateItem(0) method to generate a new MailItem for each row.

Assign Values and Add Attachments

Map the email fields to your cell values (e.g., .To = Cells(i, 1).Value). Use the .Attachments.Add method followed by the file path or ThisWorkbook.FullName to attach files to each email.

Test with .Display

End your loop with the .Display command instead of .Send. Run the macro by pressing F5 to pop up the drafted emails. This allows you to verify the formatting and attachments before actually sending them.

Handling Automatic Signatures: To retain your default Outlook signature, use the .Display command first to load the default signature into the .HTMLBody property, then concatenate your custom message text before the existing .HTMLBody.

Automate with WPS Office

Run VBA Macros Seamlessly in WPS Spreadsheet

WPS Office offers robust built-in VBA support, allowing you to run your Excel macros to automate email generation, process large datasets, and handle attachments without missing a beat.

1. Download and Install WPS Office: Get the latest version of WPS Office, which includes comprehensive VBA module support for spreadsheets.
2. Enable Macros: Open your macro-enabled workbook (.xlsm) in WPS Spreadsheet and click 'Enable Macros' in the yellow security warning bar.
3. Access the VBA Editor: Navigate to the 'Developer' tab on the ribbon and click on 'Visual Basic', or press the ALT + F11 shortcut.
4. Run Your Email Script: Paste your Outlook automation script into the module and press F5 to execute the batch email creation.

Full support for executing VBA scripts and automating repetitive workflows.Highly compatible with Microsoft Excel (.xlsm) formats and standard macro codes.Free, lightweight, and features a familiar user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

How can I automatically add my Outlook signature using Excel VBA?

When assigning text to an email via VBA, using the standard .Body property often overwrites the default signature. To keep it, use the .Display method first to load the signature into .HTMLBody, and then insert your text using .HTMLBody = yourMessage & .HTMLBody.

Why does my VBA macro send emails automatically without letting me review them?

If your VBA script ends with the .Send command, the emails will be placed directly in the Outbox and dispatched. Change the .Send command to .Display in your code to have the macro open the drafted emails on your screen for manual review.

Can I attach different files for each recipient in the VBA loop?

Yes. You can add a new column in your Excel worksheet that contains the exact file paths for each recipient's attachment. In your VBA loop, reference that specific column's cell value using .Attachments.Add(Cells(i, "AttachmentColumnIndex").Value).

Excel & Data Problems