Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Closed Thread
 
Thread Tools Display Modes
  #1  
Old 04-08-2018, 05:02 PM
macropod's Avatar
macropod macropod is offline Microsoft Word Catalogue/Directory Mailmerge Tutorial Windows 7 64bit Microsoft Word Catalogue/Directory Mailmerge Tutorial Office 2010 32bit
Administrator
Microsoft Word Catalogue/Directory Mailmerge Tutorial
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,927
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default 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.

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
Attached Files
File Type: zip Catalogue Mailmerge.zip (84.6 KB, 633 views)
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Closed Thread

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Word Catalogue/Directory Mailmerge Tutorial Microsoft Word Field Maths Tutorial macropod Word 0 04-08-2018 04:58 PM
Microsoft Word Catalogue/Directory Mailmerge Tutorial 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
Microsoft Word Catalogue/Directory Mailmerge Tutorial Pictorial directory using mailmerge JohnFinNC Mail Merge 6 10-04-2012 06:36 PM
Microsoft Word Catalogue/Directory Mailmerge Tutorial Directory Mailmerge rohitjain80 Mail Merge 1 02-12-2012 03:42 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:18 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2023, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2023 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft