How to Create One Mail Merge Email per Person in Microsoft Access
Question details
The user wants to group multiple company records associated with a single person into one combined mail merge email, avoiding the need to transpose rows into a variable number of columns.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Creating customized emails where each recipient receives a single email containing a list of multiple associated records (e.g., multiple companies).
- Observed behavior
- Standard mail merge setups typically generate one email per record, resulting in the user receiving multiple emails instead of one consolidated email.
Ensure you have a basic understanding of Microsoft Access forms and VBA (Visual Basic for Applications), as you will need to create a custom loop script to filter and dispatch the emails.
Use an Access Report and VBA to Filter and Send
Instead of transposing data, create an Access report filtered by the person ID, and use a VBA loop to automatically email the grouped report to each recipient.
Transposing data into multiple columns is often cumbersome and rigid, especially if the number of associated companies varies per person. A more robust solution is leveraging the built-in reporting features of Access, grouped by the recipient.
By combining a filtered query, a simple user form, and VBA's DoCmd.SendObject method, you can dynamically generate and send a consolidated email (or PDF attachment) for each person in your database.
Write a query to filter records based on the current person selected on your form. For example, use the SQL syntax: SELECT fieldlist FROM Table WHERE PersonID = Forms!YourFormName!PersonIDControl.
Create an Access report based on this new query. Format the report layout so that the multiple company records are displayed clearly for the recipient, keeping in mind how it will look when exported as HTML or a PDF.
Create an Access Form (e.g., 'YourFormName') containing a text box control to hold the PersonID and a Command Button to trigger the mail merge process.
In the Command Button's 'On Click' event, write a VBA script that opens a recordset of your recipients and loops through them. Inside the loop, update the PersonID control on the form to match the current recipient's ID.
Within the same VBA loop, use the DoCmd.SendObject method to generate the filtered report and send it as an email. The script will iterate through the recordset until all individuals have received their unique, consolidated email.
Simplify Your Document Workflows with WPS Office
While complex database grouping and VBA loops require Microsoft Access, many standard mail merge and data reporting tasks can be easily handled using WPS Office. As a free, lightweight, and easy-to-use Office alternative, it provides a familiar UI and seamless migration for your everyday document and spreadsheet needs.
Frequently Asked Questions
What is a many-to-one mail merge?
A many-to-one mail merge groups multiple associated records (such as several purchased items or associated companies) under a single recipient. This allows you to send one consolidated document or email per person rather than a separate email for every single record.
Can I do a many-to-one mail merge directly in Microsoft Word?
Standard Word mail merge does not natively support grouping multiple records into a single table per recipient without using third-party add-ins or advanced macro scripts. Using an Access report or a specialized directory merge is typically the recommended workaround.
How does DoCmd.SendObject work in Microsoft Access?
DoCmd.SendObject is a VBA macro method in Microsoft Access that packages a specified database object (such as a Report or Form) into a designated format (like PDF, Excel, or HTML) and attaches it to an email message using your computer's default email application.
Do I need to transpose data to send grouped emails?
No. Transposing rows into columns is generally not required and makes database management much harder. Using a filtered query to generate a customized report per person is a cleaner, more reliable, and scalable approach.
