Share via

Mail Merge Many rows of Excel data into a table in a Merged Word letter

Anonymous
2022-11-12T06:08:19+00:00

Hi,

I have a very large excel spreadsheet that I need to merge into a word document in the form of a letter to go out to my sales reps.

I want to send one letter to the unique identifier (the Application Number) AND i want to present the information in the table below to my sales rep, so they know the dynamics they are dealing with when they contact the potential car buyers.

I am having issues with getting the merge coding to work and was hoping someone could help me.

The data looks like this AND I want the application number, name and value in the table. Because I will put the email address in paragraph.

Application Number Name Email Details Value APP_ID2
001 Fred Blogs ******@blogs.com likes blue cars not pink $5,000 001
001 Mary Blogs ******@blogs.com loves pink cars but not purple ones $10,000 001
001 James Blogs ******@blogs.com first car buyer $4,000 002
002 Wilma Smith ******@smith.com rides a bike $2,000 003

The words in red are where I have inserted a mergefield from the data source.

{IF {MERGESEQ]}="1" "{MERGEFIELD Application Number}" " "}

{SET APP_ID {MERGEFIELD Application Number}}

{SET APP_ID2 {MERGEFIELD Application Number}}

{IF {APP_ID} <> {APP_ID2}

I have put all of the code above in the Application number spot in the table and this has removed the Application number from the table. it has also just given me one row per letter.

Appreciate any help you can offer.

Thank you.

Microsoft 365 and Office | Word | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Paul Edstein 82,861 Reputation points Volunteer Moderator
    2022-11-12T14:06:07+00:00

    You can do this using either a Directory merge or a DATABASE field in a standard mailmerge main document. To see how, visit:

    Microsoft Word Catalogue/Directory Mailmerge Tutorial (msofficeforums.com)

    No 3rd-party addins required.

    1 person found this answer helpful.
    0 comments
  2. Doug Robbins - MVP - Office Apps and Services 322.9K Reputation points MVP Volunteer Moderator
    2022-11-12T07:26:02+00:00

    Use the Many to One facility on my Merge Tools Add-in that is contained in the MERGE TOOLS ADD-IN.zip file that you can download from:

    https://mergetoolsaddin.com/

    Extract the files from the archive and read the:

    “READ ME – Setting up and using the Merge Tools Add-in.pdf

    to see how to install and use the various tools.

    Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:

    • Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message .  The email messages can, if necessary, also be sent to CC and BCC addresses.
    • Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source
    • Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields
    • Merging to a document that will include a chart that is unique to each record in the data source
    • Merging a document with Content Controls
    • Merging a document that contains Legacy FormFields
    • Duplex Merges
    • Merging to a printer that will collate and staple the output created from each record in the data source.

    The requirements for using the system are:

    • The mail merge main document must be of the Letters type, though that does not mean that the output cannot be sent as an e-mail message where relevant.
    • For the Many To One, Merge with Attachments and Merge to Individual Docs utilities, the data source may be either a table or query in an Access database, or in the form of an Excel worksheet. For the Chart Merge utility, see the Mail Merging with Charts document that is included in the Merge Tools Add-in Zip file for additional requirements for the data source for use with that utility
    • For a data source in the form of an Excel worksheet, the field names must be in the first row of the worksheet and there must be a field name in all of the cells in that row that are within the range of columns that contain the data.
    • For both types of data source, the field names must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9). The number of characters in the field names, including spaces, must not be more than 40.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2022-11-13T22:45:42+00:00

    Pretty much. I went looking for it (I decided to give it a try) and it wasn't there.

    *edit* My system didn't allow me to add the add-in.

    0 comments
  4. Doug Robbins - MVP - Office Apps and Services 322.9K Reputation points MVP Volunteer Moderator
    2022-11-13T22:02:03+00:00

    Your system would need to locked down like Fort Knox for it to prevent the Merge Tools Add-in from working if it is installed in the Word\STARTUP folder.

    0 comments
  5. Anonymous
    2022-11-13T20:59:38+00:00

    Hi,

    Thank you for your reply. Unfortunately the system I use is quite heavily locked down and I am unable to use add-ins of any kind. I was looking for a coding solution.

    Thank you for your time.

    Kind regards

    0 comments