Send & Receive Issues

How to Create Personalized Excel Tables in Outlook Mail Merge

Rana Garcia Oct 10, 2026 868 views

Question details

The user needs to send personalized emails where each recipient receives a specific set of Excel table rows embedded in the message body.

Create Personalized Excel Tables in Outlook Mail Merge Emails

Product: Microsoft Excel, Microsoft Outlook
Device & OS: not provided
Scenario: Sending bulk personalized emails to different recipients where the body text remains identical, but the embedded data table must be filtered and customized for each recipient.
Observed behavior: The standard mail merge creates a new email for every single row. The user wants to group multiple rows by recipient and insert them as an HTML table into individual Outlook emails.

Before you start

Ensure your Excel dataset is organized with clear headers in the first row, and verify that you have a dedicated column containing the recipients' email addresses or grouping identifiers (like an imaging center ID).

Solution 1Recommended

Use a VBA Macro to Filter Data and Generate HTML Tables

Since native Mail Merge does not support embedding dynamic multi-row tables for a single recipient, using a VBA script in Excel to generate HTML tables and send them via Outlook is the most effective method.

This method involves writing a VBA script that loops through your Excel data, filters the rows based on the recipient, constructs an HTML table, and assigns it to the Outlook email body.

Open the VBA Editor

In Excel, press Alt + F11 to open the Visual Basic for Applications (VBA) Editor. Click on 'Insert' in the top menu and select 'Module' to create a new workspace.

Enable the Outlook Object Library

In the VBA Editor, go to 'Tools' > 'References'. Scroll down the list, find 'Microsoft Outlook Object Library' (the version number may vary), check the box next to it, and click 'OK'.

Write the HTML Generation Script

Write a VBA loop that reads your dataset and groups rows by the recipient's identifier. For each unique recipient, concatenate the data into an HTML string starting with '<table>' and using '<tr>' for rows and '<td>' for cells.

Configure the Outlook MailItem

Use 'CreateObject("Outlook.Application")' to initialize Outlook. For each recipient, create a new MailItem. Set the '.To' property to the recipient's email address and the '.Subject' property to your desired subject line.

Insert HTML Body and Send

Assign your generated HTML table string to the 'MailItem.HTMLBody' property. Use the '.Display' method to review the emails first, or the '.Send' method to dispatch them immediately.

Use a VBA Macro to Filter Data and Generate HTML Tables

HTML Formatting: To ensure your table looks like an Excel grid in the email, you must add basic inline CSS styling to your HTML tags, such as <table border='1' style='border-collapse:collapse;'>

Solution 2

Use a Many-to-One Mail Merge Add-in

If you prefer not to write or manage VBA code, you can utilize third-party 'Many-to-One' mail merge add-ins that support grouping multiple Excel rows into a single email.

Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet and Document Needs

If you are looking for a highly compatible, lightweight, and cost-effective office suite, WPS Office provides excellent tools for handling complex spreadsheets, mail merges, and data processing. It is a fantastic alternative to Microsoft Office.

1. Download WPS Office: Visit the official WPS website to download the free, lightweight installer for your operating system.
2. Open Your Spreadsheets: Launch WPS Spreadsheets to seamlessly open, edit, and manage your existing .xlsx datasets without losing any formatting.
3. Use the Mailings Tab: Open WPS Writer and navigate to the 'Mailings' tab to start a new Mail Merge, linking your document directly to your WPS Spreadsheet data source.

Fully compatible with Microsoft Excel (.xlsx) and Word (.docx) formats.Built-in Mail Merge features in WPS Writer to easily connect your spreadsheets to your documents.Free, lightweight, and fast-loading across Windows, Mac, and Linux systems.Familiar user interface ensuring a seamless migration process with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can standard Word Mail Merge send multiple Excel rows to one recipient?

No, the native Mail Merge feature in Microsoft Word creates a new document or email for every single row of data. To send multiple rows to one recipient (a 'Many-to-One' merge), you must use VBA macros or a specialized third-party add-in.

Why is my Excel table losing its formatting when pasted into Outlook via VBA?

When generating emails via VBA using the HTMLBody property, you are constructing the email layout from scratch using HTML code. Standard text extraction does not carry over Excel gridlines or cell colors unless they are explicitly defined in the HTML string with inline CSS styles.

Does WPS Office support standard Mail Merge functionality?

Yes, WPS Office includes a robust Mail Merge feature within WPS Writer. It allows you to easily link data from WPS Spreadsheets to your documents for efficient bulk letter generation and standardized email merges.

Outlook & Email Problems