Share via

Mail Merge into MS Word From Multiple Rows in Excel

Anonymous
2024-10-12T01:56:31+00:00

Hello: Despite trying many different things after many searches, I have not yet found a solution to merging multiple Excel rows into an MS Word document.

A very small sample of the data is below. Imagine that this data is actually 140 rows long, and goes to column AP. Standard mail merge creates all 140 Word documents, however, there should actually only be about 45 total MS Word documents after the Mail Merge completes.

As you can see, the data is every complex. There are other rows for emails, and phone numbers in all different formats, with a large variety of multiples in one cell, many purposeful duplicates, and/or even blanks.

One mail merge document should be 0.1.

The next mail merge document should be 1.1, 1.2, and 1.3 combined.

The third mail merge document should be just 1.4

The fourth mail merge document should be 1.5, 1.6, 1.7, 1.8, 1.9, 1.10, 1.11, 1.12, 1.13, and 1.14 combined.

The fifth mail merge document should be 1.15 and 1.16 combined.

In the MS Word document, the Partner Contact Name will be one field in one spot. When Partner Contact Name includes 1.1, 1.2, and 1.3, it should combine the Partner Contact Names from 1.1, 1.2, and 1.3 in that one spot. All other fields will be the same in this regard, noting not all columns A through AP will be used (just select columns). There are more than 15 fields that need to be filled in the Word document.

Some Partner Numbers, and/or Partner Number Ranges are listed as ? or ??, instead of as numbers. Many of the numbers are stored as text.

Again, this is just a small, generic sample.

Is there a way to accomplish this with Mail Merge to save everyone a lot of manual copy and paste efforts? NOTE: We are not allowed to download anything. I believe it is Office 365 that we use. The final product must be outputted into MS Word.

Partner Number Partner Number Range Partner Name Partner Contact Name
0.1 Multiple Company 1
1.1 1.1-1.3 Company 2 Name 1
1.2 1.1-1.3 Company 2 Name 2 <br>Name 3
1.3 1.1-1.3 Company 2 Name 1 <br>Name 3 <br>Name 4
1.4 1.4 Company 2 Name 5
1.5 1.5-1.14 Company 3 Name 6 <br>Name 7 <br>Name 8
1.6 1.5-1.14 Company 3 Name 6 <br>Name 7 <br>Name 8
1.7 1.5-1.14 Company 3
1.8 1.5-1.14 Company 3 Name 6 <br>Name 8
1.9 1.5-1.14 Company 3 Name 7 <br>Name 8
1.10 1.5-1.14 Company 3 Name 6 <br>Name 7 <br>Name 8
1.11 1.5-1.14 Company 3 Name 6 <br>Name 7 <br>Name 8
1.12 1.5-1.14 Company 3 Name 6 <br>Name 7 <br>Name 8
1.13 1.5-1.14 Company 3 Name 6 <br>Name 7 <br>Name 8
1.14 1.5-1.14 Company 3 Name 7
1.15 1.15-1.16 Company 3 Name 8
1.16 1.15-1.16 Company 3 Name 7
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

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Paul Edstein 82,861 Reputation points Volunteer Moderator
    2024-10-12T12:40:10+00:00

    You can do this without downloading anything (one of your criteria), using a Word DATABASE field in conjunction with a normal ‘letter’ mailmerge main document. An outline of this approach can be found at:

    https://answers.microsoft.com/en-us/msoffice/forum/msoffice_word-mso_winother-mso_2010/mail-merge-to-a-word-table-on-a-single-page/4edb4654-27e0-47d2-bd5f-8642e46fa103

    In your case, you will need to set up a worksheet in your Excel workbook with a table containing just a single instance of each number range in your 'Partner Number Range' column.

    For some working examples, see:

    https://www.msofficeforums.com/mail-merge/37844-mail-merge-using-one-excel-file-multiple.html

    https://www.msofficeforums.com/mail-merge/45044-using-mailmerge-include-grouped-information-letter-2.html#post151706

    https://www.excelforum.com/excel-general/1273421-merge-excel-list-into-word-receipt.html#post5110813

    (the second of these uses a macro to apply some additional formatting).

    1 person found this answer helpful.
    0 comments
  2. Doug Robbins - MVP - Office Apps and Services 322.9K Reputation points MVP Volunteer Moderator
    2024-10-12T08:05:30+00:00

    The nearest that you are going to get to that is to use the Many to One Merge facility on my MergeTools 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 and the subject of the message can include data from a field in the data source.
    • 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.

    The MergeTools Add-in will NOT work with the “New Outlook”, which is just a re-badged version of Windows Mail and like its predecessor, it does not support automation and hence will not send the messages created by the MergeTools Add-in.

    Be aware, that any messages that you had tried to send with the “New Outlook” will have been placed in the Outbox of the original Outlook and they will be sent as soon as you revert to that version of Outlook.   If you do not want that to happen, you should put Outlook Off-line, or disconnect your computer from the Internet.

    For a demonstration of the use of the facility, prepared by a Microsoft employee, see

    https://www.youtube.com/watch?v=yj_s3cdfVDY

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2024-10-12T05:19:36+00:00

    Hi Jenny,

    I went through your post carefully and understand that you want to use the Mail Merge feature to make the workbook with 140 rows to generate total 45 Word documents after the Mail Merge completes based on the logic combination from relationship in the Partner Number Range column.

    In this situation, currently the default functionality of Mail Merge in Word is not available to achieve such logic sorting based on numerical column. And my idea that first coming up is to make a step further by organizing the workbook first in Excel with formulas to make it generate the final 45 rows and then things will get really easy and smoothly working in Word Mail Merge.

    However, I will keep your thread open and welcome other community members and MVPs like Doug who is experienced at Word Mail Merge to share their suggestions and other feasible workarounds based on your scenario.

    Please correct me if I made any misunderstanding or if there have anything else I can do for you.

    Hope you a nice day and stay safe!

    Best Regards,

    Mia

    0 comments