Mail Merge - To a Word Table on a Single Page

I am trying to Mail Merge multiple records using data extracted from an Excel 2010 Spreadsheet into the body of a table in Word 2010.

 

I want the records to "list" in the Word table on a single page, but everything I try ends up with 1 page and 1 table per record (multiple pages) instead of all on the same page in the same table.

 

Under the "Start Mail Merge", I have tried both Directory and Normal Word Document, but neither gives the outcome I'm trying to get.

 

Can anyone suggest what I might be doing wrong, please?

 

Thanks!

Mim

Answer
Answer
Word does not do Many to One merges. You have two choices. Either use a directory merge into a single row table (which will create a table containing all the records) and add it to the rest of the document later, or use a third party add-in such as my http://www.gmayor.com/ManyToOne.htm  which is not a true merge process but it will put data into a table in a merge document from a set of common records tied by a key field.
Graham Mayor (Microsoft Word MVP 2002-2019)
For more Word tips and downloads visit my web site
https://www.gmayor.com/Word_pages.htm

2 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Tables are usually only required when grouping data. This can be done using an ordinary letter merge containing a DATABASE field suitably coded for the table. All your data source needs is a separate sheet with rows containing a header and a single copy of each of the grouping criteria and any other fields you want to appear once per group. For example suppose you have a workbook named datasource.xlsx in the same folder as your mailmerge main document, with data like the following on Sheet1:

Manager ID Manager Name Subordinate ID Subordinate Forename Subordinate Surname Subordinate Job Title
12345 Joe Bloggs 11111 Adam Ant Analyst
12345 Joe Bloggs 22222 Bertie Bee Analyst
12345 Joe Bloggs 33333 Colin Caterpillar Specialist
23456 Jane Doe 15151 Derek Dog Field Agent
23456 Jane Doe 16161 Edward Elephant Field Agent

and you want to send a separate letter to each manager. In that case, you'd add another sheet (e.g. Sheet2) containing just:

Manager ID   Manager Name        
12345   Joe Bloggs        
23456   Jane Doe        

then use this sheet (Sheet2) as the data source for your the mailmerge main document, to which you'd add a DATABASE field coded along the lines of:

{DATABASE \d "{FILENAME \p}/../MM datasource.xlsx" \s " SELECT [Subordinate Forename], [Subordinate Surname], [Subordinate ID], [Subordinate Job Title] FROM [Sheet1$] WHERE [Manager ID] = {MERGEFIELD Manager_ID} ORDER BY [Subordinate Job Title] " \l "15" \b "49" \h}

If the mailmerge main document is NOT kept in the same folder as the data source, you'd need to replace all of "{FILENAME \p}/../MM datasource.xlsx" with the full path & filename (plus the encompassing double-quotes). The \l and \b switches control the table format, while the \h switch insert a table header row - see: https://support.office.com/en-us/article/Field-codes-Database-field-04398159-a2c9-463f-bb59-558a87badcbc?ui=en-US&rs=en-US&ad=US

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field constructions are all required.

Cheers
Paul Edstein
(Fmr MS MVP - Word)

10 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info


Last updated April 9, 2021 Views 21,626 Applies to: