Mail Merge in Excel: How to Automatically Generate Invoices, Billing Statements, and Send Emails in Bulk
Many people search for ways to mail merge in Excel or send multiple emails from Excel, especially when generating invoices or billing statements for multiple clients.
Unlike Microsoft Word, Excel doesn’t have a built-in mail merge feature. But with the right setup, you can achieve something very similar — and in some cases even more powerful.
Using Excel tables, formulas, and a little VBA automation, you can:
• Create an auto-populating invoice template
• Automatically generate client billing statements in bulk
• Export those statements as PDF files
• Prepare multiple client emails at once with attachments
This workflow is useful for freelancers, consultants, accounting teams, and small businesses that send recurring invoices or monthly billing statements.
In this guide, I’ll explain the key concepts behind the system demonstrated in these two video tutorials.
First, learn to set up your auto-filling invoice or billing statement template using XLOOKUP and other formulas:
Then, scale your process to mass generate multiple statements and email them out in bulk:
Step 1: Create an Auto-Populating Invoice Template in Excel
The first step is building an Excel invoice template that fills in information automatically.
Instead of manually typing customer information for every invoice, the template pulls data from two structured tables:
Clients Table
Contains information such as:
Client ID
Company name
Contact name
Email address
Phone number
Payment terms
Products or Services Table
Stores information about the items you bill for:
Service descriptions
Product names
Unit prices
Using Excel dropdown lists and XLOOKUP formulas, the invoice can automatically pull the correct information into the template.
For example:
Selecting a client name automatically fills in contact details
Selecting a service description automatically pulls in the unit price
Invoice totals calculate automatically
Payment terms calculate the due date
A unique invoice number can be generated automatically
Once this template is built, creating a new invoice takes only seconds.
📺 Watch the full tutorial here:Build an Auto-Populating Invoice Template in Excel
Step 2: Track Client Activity Using a Transactions Table
To generate billing statements, we need to track activity for each client.
Instead of creating separate invoices and statements manually, you can store all activity in a Transactions table.
Each row in the table represents a transaction for a client, such as:
Service charges
Product purchases
Payments received
Credits or adjustments
By storing everything in one structured table, Excel can automatically calculate totals for each client.
Step 3: Filter Transactions by Billing Period
Billing statements typically summarize activity for a specific time period.
To automate this, the workbook includes a Statement Settings table where you define:
Statement Start Date
Statement End Date
Output folder for generated statements
Excel then uses a spill formula with FILTER and SORT functions to automatically pull the correct transactions into the billing statement template.
This allows the statement to display:
Transaction dates
Descriptions
Charges
Payments
Running balances
Whenever the billing period changes, the statement updates automatically.
Step 4: Automatically Generate Client Billing Statements in Bulk
This is where Excel automation becomes extremely powerful.
Instead of creating each statement manually, a VBA macro can:
Loop through each client in the Clients table
Check whether transactions exist during the billing period
Populate the billing statement template
Export the statement as a PDF file
Save the file automatically
This allows you to generate dozens or even hundreds of client statements at once.
In other words, Excel can function as a mail merge-style system for billing statements.
Step 5: Send Multiple Emails from Excel
Once the statements are created, the next step is sending them to your clients.
Another VBA macro can connect Excel with Microsoft Outlook and automatically:
• Create draft emails for each client
• Insert the client email address
• Personalize the email greeting
• Attach the correct statement PDF
This allows you to generate multiple emails at once directly from Excel.
The emails are saved as drafts so you can review them before sending.
For businesses that send monthly statements, this can save a huge amount of time compared to attaching and sending each file manually.
Using Excel as a Mail Merge Alternative
Although Excel doesn’t have traditional mail merge functionality, this system essentially creates a mail merge alternative inside Excel.
Instead of merging documents like Word does, Excel:
• Pulls data from structured tables
• Generates personalized documents for each client
• Exports them automatically
• Prepares bulk emails with attachments
This approach is particularly helpful for:
Monthly billing statements
Recurring invoices
Client reports
Account summaries
Using AI to Generate Excel VBA Code
If you're building your own automation system, AI tools can help generate the base VBA code.
A simple prompt might look like this:
Create an Excel VBA macro that loops through a table of clients and generates a PDF report for each client using a template sheet. The macro should insert the client ID into the template, filter transactions by date range, export the report as a PDF, and save it to a specified folder.
You can then modify the generated code to match your workbook’s sheet names, table names, and cell references.
Watch the Full Excel Automation Tutorials
These videos demonstrate the complete workflow.
📺 Auto-Populating Invoice Template in Excel
📺 Generate Billing Statements and Send Emails in Bulk from Excel
Download the Excel Templates
If you'd like to use the exact templates demonstrated in these tutorials, they are available as a downloadable bundle.
The bundle includes:
✔ Auto-populating Excel invoice template
✔ Automated billing statement template
✔ Client and product database tables
✔ Transactions tracking sheet
✔ VBA macros for generating PDF statements in bulk
✔ Outlook email automation
✔ Instructions for setup and customization
Final Thoughts
Excel is capable of much more than simple spreadsheets.
By combining Excel tables, formulas, and VBA automation, you can create a powerful system that:
Generates invoices automatically
Produces billing statements in bulk
Sends multiple client emails at once
Once everything is set up, tasks that previously took hours can be completed in just a few minutes.
Frequently Asked Questions
Can you do mail merge in Excel?
Excel does not include a built-in mail merge feature like Microsoft Word. However, you can achieve similar results by combining Excel tables, formulas, and VBA automation to generate personalized documents and emails.
How do you send multiple emails from Excel?
You can use Excel VBA to connect with Microsoft Outlook and automatically generate multiple emails using client data stored in a spreadsheet.
Can Excel generate invoices automatically?
Yes. By using dropdown lists, lookup formulas such as XLOOKUP, and structured tables, Excel can automatically populate invoice templates with client and product information.
Can Excel create billing statements in bulk?
Yes. With Excel tables and VBA automation, you can generate billing statements for multiple clients at once and export them as PDF files.
