Microsoft Office Forums - [Solved] Microsoft Word Catalogue/Directory Mailmerge Tutorial

Attached to the bottom of this post is a tutorial on how to use Word's mailmerge facility to create lists sorted by category.

Microsoft has similar articles at:
HOW TO: Use Mail Merge to Create a List Sorted by Category in Word 2000
and:
Use mail merge for bulk email, letters, labels, and envelopes - Office Support
but my tutorial goes way beyond those articles' scope by:
a ) providing a sample data source that can be used to demonstrate/test the use of different keys,
b ) including working mailmerge fields
c ) adding examples to show how:

• text can be categorised using two keys
• text can be added after the repeated data
• group and sub-group counts and totals can be calculated without the need for such fields in the source data
• to create a two-column output
• to create a series of tables from the merged data
• to shade alternate rows when merging to a table
• to merge to email

Plus, unlike the field coding suggested by the MSKB articles, my approach doesn't insert extra lines for Excel data sources.

The tutorial covers everything from list creation to the insertion & calculation of values in multi-record tables in letters. Do read the tutorial before trying to use the mailmerge document included with it.

Depending on what you're trying to achieve, the field coding for this can be complex. However, since the tutorial document includes working field codes for all of its examples, most of the hard work has already been done for you - you should be able to do little more than copy/paste the relevant field codes into your own mailmerge main document, substitute/insert your own field names and adjust the formatting to get the results you desire. For some worked examples, see the attachments to the posts at:
MS Office Forums : Mail merge with Duplicate Names but Different Dollar Amounts
MS Office Forums : Access to Word, Creating a list from multiple records

Note 1: Please read the tutorial before trying to use the mailmerge main document for a mailmerge - the merge field coding you'll need is in the tutorial and has to be copied from there into the mailmerge main document before you can do a mailmerge.

Alternative Approach: Using DATABASE fields to Create Lists Sorted by Category
Another option would be to use a DATABASE field in a normal ‘letter’ mailmerge main document and a macro to drive the process. Here is an outline of this approach:

Suppose you have a relational database or, an Excel workbook with a separate table listing each of the grouping criteria (e.g. Manager IDs) and any other fields that occur once per group, and a separate data set (e.g. a separate Excel Worksheet or Access tbale) that has fields for employees' Firstname, Surname, Employee ID, & Job Title, and that against each record is recorded their Manager's ID. Let's also assume the mailmerge main document is kept in the same folder as the data source. In that case, you could use a DATABASE field coded as:

Quote:

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

where 'MM datasource.xlsx' is the data source filename - it could even be an Access database filename, if that's what you use. 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 inserts a table header row - see: Field codes: Database field. The easiest way to visualize the output possibilities, though, is to open a separate document and use Insert|Quick Parts|Field>Database>Insert DataBase>Get Data (select your data source)>Query Options>Select Fields (select some fields from your data source)>Table AutoFormat. There, you will see a wide range of preset table formats to experiment with, including for the borders, shading, font, table autofit, etc. Watch what happens as to toggle the various options on/off for each preset. When you find a suitable format, choose Insert Data>Insert as field>OK. Then select the DATABASE field Word has inserted into your document, display the field code via Alt-F9, then copy the \l and \b switch values (and the \h switch if it's present) at the end of that field's code to the end of the DATABASE field code in your mailmerge main document.

Note 2: 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 here. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field constructions are all required.

For some working examples of this approach, see:
MS Office Forums : Mail Merge: Using One Excel File with Multiple Sheets
MS Office Forums : Using Mailmerge To Include Grouped Information In a Letter
Excel Forum : Merge excel list into Word Receipt
MS Answers : Mail Merge - To a Word Table on a Single Page
(the second of these uses a macro to apply some additional formatting).

If you'd like to have each group's mailmerge output from the above approach saved as a separate document/pdf, see the Send Mailmerge Output to Individual Files topic in my Mailmerge Tips & Tricks page: https://www.msofficeforums.com/mail-...ps-tricks.html

Conversely, if you're using a data source such as an Excel workbook where records such as the Manager IDs and other data that are to appear once per group (e.g. email addresses) are all recorded on every row, you could use a macro like the following to drive the process:

Code:

Sub Merge_by_Group()

Application.ScreenUpdating = False

Dim MainDoc As Document, StrMgrID As String, i As Long

Set MainDoc = ActiveDocument

With MainDoc

  For i = 1 To .MailMerge.DataSource.RecordCount

    With .MailMerge

      .Destination = wdSendToEmail

      .SuppressBlankLines = True

      With .DataSource

        .FirstRecord = i

        .LastRecord = i

        .ActiveRecord = i

      End With

      If .DataSource.DataFields("Manager_ID") <> StrMgrID Then

        StrMgrID = .DataSource.DataFields("Manager_ID")

        .MailAddressFieldName = "Email"

        .MailSubject = "Your Team's Details"

        .MailFormat = wdMailFormatHTML

        .Execute Pause:=False

      End If

    End With

  Next i

End With

Application.ScreenUpdating = True

End Sub

For a working example of this approach, see:
MS Answers : Many to one email merge using tables

A merge to email is assumed, but not necessary. If you want the output to go to a Word document, change:

Code:

.Destination = wdSendToEmail

to:

Code:

.Destination = wdSendToNewDocument

and delete:

Code:

        .MailAddressFieldName = "Email"

        .MailSubject = "Your Team's Details"

        .MailFormat = wdMailFormatHTML

Note 3: If you rename the above macro as 'MailMergeToEmail' (or 'MailMergeToDoc' to send the output to a document), clicking on the 'Send Email Messages' (or 'Edit Individual Documents') button will intercept the merge and the process will run automatically. The potential disadvantage of intercepting the 'Send Email Messages' (or 'Edit Individual Documents') process this way is that you no longer get to choose which records to merge at that stage. However, you can still achieve the same outcome - and with greater control - via the 'Edit Recipient List' tools.

Enjoy