Microsoft Word Catalogue/Directory Mailmerge Tutorial

macropod · #1 04-08-2018, 05:02 PM

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.

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

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:
https://www.msofficeforums.com/mail-...-multiple.html
https://www.msofficeforums.com/mail-...tml#post151706
Merge excel list into Word Receipt
(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

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Microsoft Word Field Maths Tutorial	macropod	Word	0	04-08-2018 04:58 PM
Microsoft Word Date Calculation Tutorial	macropod	Word	0	04-08-2018 04:57 PM
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge	Berryblue	Mail Merge	1	11-13-2014 05:36 PM
Pictorial directory using mailmerge	JohnFinNC	Mail Merge	6	10-04-2012 06:36 PM
Directory Mailmerge	rohitjain80	Mail Merge	1	02-12-2012 03:42 AM