Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Enhanced Merge (Merge Many to One)
A Microsoft Word Pseudo Mail Merge Utility
(A Microsoft Word Help & Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


This Microsoft Word Tips & Microsoft Word Help page provides detailed instructions for employment and use of my "Enhanced Merge (Merge Many to One) Utility Add-In."

Background\Credits

Word's native letter type mail merge is strictly "One to One" (i.e., one data source record = one merge output).  Mail merge is not my forte and the concept of a "Merge Many to One" process (i.e., merging multiple [one or more] data source records to a merge output) certainly didn't originate with me.

For the last dozen years or so years, two friends and Word MVP's Doug Robbins and Graham Mayor have published and maintained template add-ins for expanding Word's native mail merge capabilities (including Merge Many to One processes).  Doug's Merge Tools is available at Doug Merge Tool and Graham Mayor's at Graham Merge Tool.

Both of these tools work remarkably well.  My intention is not to compete with or try to out do Doug or Graham. I entered this arena because of my interest in content controls and with a goal to provide a utility that breaks completely any connection with Word's Mailings tab.

I have long felt that content controls were well suited to fill the role of legacy merge fields and by using a custom dialog which establishes an ADODB connection with the data source, I have eliminated the annoying "baggage" tied to establishing a data source via the ribbon Mailings tab.

It is very unlikely that I would have every undertaken this project absent the foregoing work of Doug and Graham.  Several processes performed behind the scenes are a direct result of some of Doug's work or a collaborative effort with Graham. 

Additionally, Eric Delp has collaborated with the progress bar; the date picker used in the mail item transfer dialog, while extensively modified, originated with Trevor Eyre, Ron de Bruin's website provided a portal to countless articles on OUTLOOK development and Graham Mayor and Jay Freedman's sharp eyes have caught many spelling and grammar errors.

Tips Page Graphics

I used Word 2010 while developing the example merge processes and graphics in this tips page. Most reflect the initial add-in version 1.0 in the dialog captions.  When updating the add-in version and this tips page, those graphics will not be updated unless changed.

Version History

Function

The add-in performs a pseudo Word Mail Merge "letter" process simplified and enhanced through the use of content controls and external data arranged in "record sets."   In addition to one to one merge processes, the record set arrangement and processing instructions within the add-in allow multiple external data records "Many" to be combined to create a single merge output "One" for each record set.

Features/Capabilities

  1. Merge One to One (letter type) to Word documents, to PDF format documents, to email or to the printer.
  2. Merge One to One (letter type) to Word document or PDF format document attachments to email.
  3. Merge Many to One (letter type) to Word documents, to PDF format documents, to email or to the printer.
  4. Merge Many to One (letter type) to Word document or PDF format document attachments to email.

The e-mail features of  the add-in require that Microsoft Outlook is installed and set as the default mail application.

Where  the open source PDF Creator application is installed, additional options to use PDF Creator are available for both Merge Many to One and Merge One to One. PDF Creator provides options to add security measures to the merged PDF files not available with Word's built-in process.

Data Source Concepts/Requirements

While some users may wish to simply dive right in, the processes and facilities of this add-in are complex.

I encourage you to read this tips page in its entirety before downloading and attempting to use the add-in. As a minimum, you should read and understand these data source concepts/requirements and the merge template requirements below.

I am available to provide fee based personal assistance employing the add-in.  That fee applies even if that assistance is simply showing you what you should have already read.


Before presenting the actual use and employment of the add-in, it is essential that you understand the concepts of "record sets," "key field," "child fields," "list field" as used with the add-in and  the configuration and layout of the external data source (Access or Excel) which you associate with your merge template.

The data source can be an Excel worksheet or named range, or an Access table or query.

If using an Excel data source, row 1 must define unique data field names. The field names must be defined in a single line (multi-line field names are not permitted and will invalidate the data source with the add-in).

Excel Named Ranges

When an Excel Named Range is set as the data source, the named range must include a duplicate of the parent sheet first row (i.e., the column headings) as the first row of the named range.

EM_MTO 01

Likewise if using Access, the field names are defined as unique column headings in the table or query.

EM_MTO-02

Site Note IconNote: Use whichever data source (Access or Excel) you prefer.  However since with Access you can't duplicate field names or have multi-line field names. You might find Access easier to use.  

The two data sources shown above have certain common attributes related to the add-in:

The following depicts the simple Excel data source shown above revised and configured for a "Merge Many to One" process.  Note the two added columns (OCCUPATIONS and CHILDREN NAMES). 

EM_MTO-03

The data source shown above has certain attributes related to the add-in:

Record Set

As used with Enhanced Merge (Merge Many to One), a record set is all of the data source records in a continuous unbroken sequence of records defined by the same unique "key" field value.

Key Field

As used with Enhanced Merge (Merge Many to One), a "key" field is that field assigned by the user which defines the record sets.  Any non-child field can be defined as the key field. However, for best results the key field you define should be that field containing unique data not expected to be duplicated in multiple records sets.  E.g., "HAIR COLOR" could be defined and would work as the "key" field for processing the small data sample above. However, in larger data base it is highly likely that Red, Black or Blonde would be repeated with other named individuals therefore making "HAIR COLOR" a very poor choice.  If your are following along, you should see that "NAME" is a better choice and "SSN" even better.

Each record field in the defined "key" field must be filled in.  This is very important as any break or change in the key field value is used to terminate the current merge output and initiate the next merge output.
 

Child Field(s)

As used with Enhanced Merge (Merge Many to One), a "child" field(s) is/are those fields in the data source used to define a variable number of values to a named attribute of the key field e.g., "OCCUPATIONS" (i.e., all the jobs the "NAME" person has had).  "CHILDREN NAMES" (the name of each "NAME"  persons children).

Record fields in child fields may be blank (e.g., the three blank fields in the Wackford L. Squeers record set). However, the blanks must be at the end of the set.

EM_MTO-4

 As depicted in the above graphic, the key field must be filled in for all records.  During processing, it is the "change" in key field value (e.g., a change from Edward R. Murdstone to Vincent E Crummels) that triggers the creations of a new merge output.

Obviously all named persons in your data base won't have the same number of children or the same number of current and previous occupations.  Therefore empty record child fields are permitted out of necessity.

All other fields (non-key/non-child) should be completely filled in!  Fill-in is mandatory for any non-key/non-child field that you may wish to define as an email recipient field, email subject field or filename field.

If this requirement/recommendation appears pointless or confusing, consider a case where an advanced user wants to filter data and merge only records where the OCCUPATIONS field = "Tailor." In this case, two records would be returned.  If those records didn't include the data for DOB, SSN and HAIR COLOR then the merge result would be missing that data!

List Field

The list field is a single unique field that you can define and populate in your database to associate with a single unique content control in the merge template.  A list field provides an "independent list" that can be used to create highly customized content for use as closings, signature fields, etc.

The database example depicted above is overly simplistic and contains no list field. Going forward we will use a larger database in these examples. A snippet of this database is show below.

EM_MTO-8

In our larger database, the key field "OWNER" is shown in pale blue, the child fields are shown in salmon and the list field is shown in olive green. Individual list field elements defined in the database must be delimited using the pipe "|" character.  

EM_MTO-9

Merge Template

Now that you are familiar with the data source requirements, lets turn to the merge template.

The merge template fills the role of a mail merge main document in a traditional Word mail merge process.  The merge template uses content controls in place of merge fields as data field identifiers. It can be any Word .docx, .docm, .dotx or .dotm format file, but it can not be a mail merge main document.

The add-in Merge Template Tools - Convert Mail Merge Fields provides a basic utility for converting mail merge letter type main documents and associated merge fields into a merge template compatible with the add-in.

Note - It does not convert "Address Block" merge fields or conditional merge fields.  The add-in does support conditional If fields using content controls.  A complete discussion and examples are included in the add-in download package at the conclusion of this tips page.


The following illustrates a merge template configured for a Merge Many to One process using the example data source shown above:

EM_MTO-10

Eleven of the twelve content controls shown above are associated with the data source column headings using the content control title property.

EM_MTO-11

The column total is calculated using a traditional formula field.

EM_MTO-11A

The twelfth content control showing the placeholder text ("Independent List") is used to develop the independent list.  It is associated with the user defined data source list field by tag.

EM_MTO-12

The following depicts a Merge Many to One output using the data source and merge template shown above:

EM_MTO-13

The child fields in the merge template shown above are arranged in a table and the results are delimited in individual table rows.  While recommended, such arrangement is not mandatory.  However, some advanced features of the add-in are not applicable when child fields are arranged as a simple list (paragraphs) or other character delimited (e.g., comma separated list) list or if child fields in tables are not delimited using rows.

Description and Use of the Add-in Ribbon\User Interface

The add-in user interface consists of a custom ribbon tab, a primary userform dialog, a progress bar and several secondary userform dialogs.  The custom ribbon tab manifests when the template add-in is loaded either manually or automatically (when Word is started).

Site Note Icon Note: For more on template add-ins and how to load them, see: Organizing Your Macros/Template Add-ins at: Installing Macros

Ribbon Tab

EM_MTO-05

The "Enhanced Merge (Merge Many to One) ribbon tab appears between the ribbon Mailings and Review tabs.  Consisting of four custom control groups, it serves as the user's starting point for all functions associated with the add-in.  With the ribbon tab you:

Utilities Group

Since associating a data source with a merge template is the first step in both Merge Many to One and Merge One to One processes, I will start with the Utilities group.

Notification Tone

Several events can occur when using the add-in which display user notification or action requirements.  Notification Tone is a two state toggle button control that turns on\off an associated Windows media sound file.

Set or Change Data Source

Before a merge process can be performed, a data source must be associated with the merge template. This control launches a dialog from which you can select the Access file (table or query) or Excel file (sheet or named range) containing records to associate with the template.

When depressed, and if no data source is associated with the merge template or if a previously associated data source file is deleted or renamed, the following dialog is displayed.

EM_MTO-21

Click "Select Data Source" to display a file picker dialog from which you can select and associate a data source file.

EM_MTO-22

Select a table or sheet.

Site Note IconNote: Green and red font color is used in several of the add-in dialogs to indicate go/no go conditions.  As seen below, once a valid data source file is selected, the file name label is displayed in green.  Due to the inherent nature of the add-in userform dialogs, the sheet list is displayed in red until a listed sheet is selected.

EM_MTO-23

Set the data source.

EM_MTO-24

EM_MTO-24B

Excel Named Ranges

When an Excel Named Range is set as the data source, the named range must include a duplicate of the parent sheet first row (i.e., the column headings) as the first row of the named range.

Site Note IconNote:  Click the "SHOW" command button to review the data source and data structure.

EM_MTO-25

Excel is not a true database.  If after an Excel file is associated with a merge template the columns are rearranged or renamed, or if data in a complete record row is deleted the ADODB data structure can become corrupted.  You may see "F" series column headings in the column headings lists or receive notification during merge setup that null values where encountered in Key, e-mail, filename fields etc.

Should either occur you will need to open your Excel file and physically delete the problematic columns or rows.   

Reset Defaults

Resets the add-in default settings and configuration.

Data Merge Group/Initiate Merge and the Primary Userform Dialog

The Data Merge group, Initiate Merge control launches and displays the add-in primary userform dialog. The primary userform dialog (hereafter referred to as U\I) serves as the user interface for all merge processes. On first use, the U\I is displayed as shown below. 

EM_MTO-6

Site Note IconNote: When published, the add-in was compatible with Word versions 2007-2016. I expect it will be compatible with Word 2019.  This tips page was produced using screen graphics from Word 2010.  With future revisions and updates some of the captions in the graphics may reflect earlier versions.

The U\I consists of an eight page (seven showing) tabbed multi-page control, a "Resting Anchor" logo link, a Donate link, a CANCEL command button and a variable captioned primary execution command button.

Disclaimer Tab

The disclaimer releases the developer from any responsibility associated with use of the add-in.  It must be acknowledged before any other add-in processes can be enabled.

Site Note IconNote:  The U\I is presented with the Disclaimer showing on first use, with each user change, with each revision to the add-in and each time the add-in default values are reset.

Configuration Tab

After the disclaimer is acknowledged, the Configuration tab page is displayed.

EM_MTO-07
OUTLOOK INSTALLED CHECKBOX

Three of the possible seven merge output modes involve e-mail. To enable and use the e-mail modes, Microsoft OUTLOOK must be installed and configured as the default e-mail application. Check this box if you system meets this requirement and you desire to use one of the output modes using e-mail.

When the add-in is configured for use with OUTLOOK, OUTLOOK must be running regardless of the merge mode selected.  While OUTLOOK can be launched from the add-in, some users have experienced difficulty merging to the body of a e-mail message if OUTLOOK is started with the add-in or after Word is started.

Best practice is to start OUTLOOK before starting Word or initiating a merge process.


If configured for use with OUTLOOK and OUTLOOK is not running when a merge process is initiated, the following notification dialog is presented:

EM_MTO-14
VALIDATE E-MAIL ADDRESSES

Applicable only when the OUTLOOK is installed option is selected. The default value is checked.

It is essential that the e-mail addresses in the data source are both present and valid when an option to merge to e-mail is selected. In these cases, and with this option applied, the add-in examines all the e-mail addresses in the record set. When a required address does not meet e-mail address format standards, you will be notified of the erroneous e-mail address and associated record.  For example, here I have deliberately attempted a merge process with invalid e-mail addresses:

EM_MTO-15

and again with one or more missing e-mail data

EM_MTO-16

You are strongly encouraged to leave this option checked unless you use an Exchange Server with recipient nicknames that are not recognized by the add-in as e-mail addresses.

SUPPRESS OUTLOOK SPELL CHECKING

Applicable only when the OUTLOOK is installed option is selected. The default value is checked.

If you have your OUTLOOK application configured to check spelling before sending, the automatic bulk sending of messages generated by a the add-in could result in a tedious requirement to step through each message to validate spelling error flags.  You can turn this feature off in OUTLOOK or override OUTLOOK's spell checking with the Suppress Outlook spell checking option of the add-in.  

EM_MTO-17

Either way, e-mail body content is not spell checked so use the requisite care to ensure your message content is accurate and appropriate for sending.

PDF CREATOR INSTALLED

Two of the seven possible merge output modes utilize the open source third party application PDF Creator.  Check this option if you have PDFCreator Ver. 2.5 or higher installed and wish to use it with the add-in.

DELETE EMPTY PARAGRAPH AFTER MERGE

Deletes any empty paragraph resulting after a merge.  Default value is checked.

In certain cases when the data field for an associated content control is empty, the merge results in an empty paragraph.  Consider our example merge template and resulting output shown previously above.  In that example, we have a conditional ADDR LINE 2.  Some records will contain data in the ADDR LINE 2 field and some likely will not.

EM_MTO-18

EM_MTO-19

EM_MTO-20
REMOVE CONTENT CONTROLS AFTER MERGE

Removes all content controls (preserves content) from the resulting merge document after the merge. Default is unchecked.

Site Note IconNote:  Fill-in type content controls created as part of an advanced merge output are not removed.

ENABLE MACROS (PREMIUM)

This advance feature is available to private licensed clients. It permits additional automated processes to be developed and run as the individual merge outputs are created. Contact the developer for more information on licensing arrangements.

BROWSE\SET DOCUMENTS FOLDER

Identifies and allows the user to select and set the folder sued to store the merge output documents.

APPLY SETTINGS

Click to set and apply the individual configuration settings.  Refreshes the Add-In and advances U/I to the "Start" tab.

Start Tab

After Disclaimer acknowledgment and Configuration, Start is the default tab when initiating a merge process. Using this tab, you can:

EM_MTO_26

Site Note IconNote: The "Merge" tab is enabled automatically after an output option is selected.

If you show the data source, you can review the available records and see the number for records returned. With no filter applied, the default SQL Query returns all records in the data source.

EM_MTO-27

After selecting or verifying the data source, selecting the merge type and output, click "Next >>" This moves the U/I to the "Merge" tab.

Merge Tab (Phase I)

Merge to Individual Word Files

Using the controls on the Merge tab, you select the Key field, one or more child fields and Filename field.

Here you also configure how any child field data in the record set is delimited in your output merge fields. "Create new rows for child fields in tables" is checked by default.  Paragraph separators is default for any child fields in the merge template which are not in tables.

Site Note IconNote: See the associated Help tip for more information on delimiting child field data.

"Enable Advanced options" is enabled after identifying and filename field.  If checked, the "Filename" tab is enabled.

The illustration below depicts the Merge tab as first displayed with the data source and merge type and output selected from the Start tab.  The "Fields" list indicates all of the fields defined in the data source.  The rose shaded areas require user input to complete the merge process.

EM_MTO-28

This next illustration depicts the Merge tab after making typical selections for a merge base on the examples given above.

EM_MTO-29

The functionality and use of the Merge tab is similar for other merge types and output options.

The "MERGE" command button becomes enabled after all requirements for the merge are complete.  In this example, the Independent List option is enabled so to enable the MERGE command button and complete the merge, the list field needs to be defined.

List Field Tab

After the "Key" field is set on the Merge tab, the Add-In detects if the merge template contains a content control with the tag "Independent List." If found, the List Field tab is enabled. The List Field tab is used to define and link a user defined data source field with the merge template content control tagged "Independent List Field."

Site Note IconNote: See the example given in the Merge Template example discussed previously.

In the example template, the content for the Independent List content control is developed using the data source "ASSOCIATE" field.  Any data source field not previously selected as the key or a child field may be used to define the template independent list field content.

EM_MTO-30

While not illustrated in the running example scenario, the leading and trailing text fields could be used to create highly customized independent lists including signature lists.

EM_MTO-31

Obviously that is not very useful, but by using any combination of spacing (including the enter key) or text in the Leading and Trailing text boxes you can create custom lists with a wide variety of formatting.  Typing "[TAB]" in either text box as shown below results in actual tabs in the output document text when the merge is run.

The Independent List Layout field does not indicate the actual spacing provided by the pseudo tab character.  That is determined by the actual tab stops in the merge template.

See the two illustrations below which show how this function may be applied in practice.

EM_MTO-32
List Field Layout

EM_MTO-33
Resulting content in output document

Values entered in the List Field tab are stored in the registry with the SET command and remain stored until cleared or reset.  Stored values automatically repopulate in the List Field tab fields on subsequent add-in use.
 


Merge Tab (Phase II)

After setting the required and any optional List Field attributes, the U\I is returned to the Merge tab where the user can now execute the merge.

EM_MTO-34
Ready to execute merge

EM_MTO-35
Merge in process

EM_MTO-36
Merge complete

EM_MTO-37
Output Word documents

 Merge type, merge output format, key and filename field values are stored in the registry after executing each merge process.  These values automatically repopulate the respective U\I fields on add-in reuse.  Stored values can be reset using the Utilities Reset Defaults command.


Merge to Individual PDF Files

The process to merge output to Individual PDF Files is identical to the process to merge to Individual Word Documents.  In the next example, I will output to Individual PDF files and illustrate how to use the advanced file naming features.

EM_MTO-38

Using the same Merge and List Field tab settings illustrated in the previous example, check the box to enable advanced options.

EM_MTO-39

This enables and sets U\I focus in the Filename tab.

Filename Tab

Using the Filename tab, you can define an optional second data source field for use in file naming and/or fixed text and/or, define optional fixed text for use in the filename. The content of the primary field as set in the Merge tab [Field1] and optional secondary field [Field2] can be transposed.  Additionally, the fixed text can prefix the field content, suffix the field content or be sandwiched between the [Field1] and [Field2] content.  The following provides an example using the advanced filename options.  

EM_MTO-40
Filename options

EM-MTO-41
Files named using data source record ADDR_CITY field content, fixed text and data source record OWNER field content

Values entered in the Filename tab fields are single use only values and applied to the current merge process.  They are not stored in the registry for later recall or reuse.


Security Tab

Merge to PDFCreator

The Security tab is associated with and only appears on the U\I if the open source third party application PDFCreator is installed and the add-in user has indicates so in the add-in Configuration tab.

PDFCreator is an open source third party application offered by pdfForge.  The two add-in PDFCreator output modes were initially developed using PDFCreator version 2.5 (freeware) and follow on development using version 3.3.2. (freeware). The add-in developer can't guarantee or comment on performance
with any other PDFCreator version or level.

Note: When downloading PDFCreator from the pdfForge website, the download may include PDF Architect which is not required for the add-in and the installation may offer various plug-ins.  If installing PDFCreator, I recommend that you use the 'Expert' installation settings and choose any additional applications or plug-ins to suit your preferences. 


The Security tab is only enabled when one of the two available PDFCreator output options are selected as the merge output format.

EM_MTO-42

When the merge output is one of the PDFCreator formats, the default output format is unsecure PDF.

EM_MTO-43

The PDFCreator applications supports four PDF output formats.  PDF security features are only available in the base  PDF output format and after a Master Password has been defined.

EM_MTO-45

An attempt to execute a merge in either of the PDFCreator output modes without first verifying\setting security attributes presents the following user notification:

EM_MTO-44
Merge to Printer

The process for merge to printer is similar to the previous processes.  The primary difference being there is no requirement to provide an output filename.

EM_MTO-46

This mode is intended for merging output to a physical printer (i.e., one that produces a paper copy) where each resulting merge output is sent as a separate print job.

While you may have limited success with using a non-physical printers e.g., Adobe PDF, OneNote etc., an output filename is not supplied to the printer and selecting a non-physical printer may require direct user interaction with the selected printer.

EM_MTO-47

This concludes the discussion on the four direct output options. Next, I will illustrate and describe employing the add-in with OUTLOOK and the four email output modes.

Email Tab

The Email tab is enabled when one of the four email associated output modes is selected and all other required Merge and List Field (if applicable) requirements are met.

I will not address each of the four email associated output mode processes separately in detail as they are basically the same as the process for performing a direct merge.

 Similar to merge to printer, merge to email message differs from the other three email modes in that the content of the merge output constitutes the body of the email message.

Since I am just publishing this add-in and have admittedly taken much from Doug and Graham in its development, I have a dearth of feedback and experience on problems or bugs.  Fortunately, Graham has had plenty over the years and has expertly tuned the content of his tips page to accommodate.  Parts of the following are from his site.

IMPORTANT NOTE

When merging to the body of an e-mail message (and that will include using one of the three E-mail with * Attachment options and using a personalized HTML cover message), you should always start the Outlook applications before Word. Otherwise the merged output content (or cover page content) may not be transferred to the  email message bodies.

This is due to a peculiarity in the link between Word and Outlook that has not been resolved.

Flicker and Flash

The process for merging content directly to an email message body requires Outlook to briefly, but physically, open and close each merge output email message.

This opening and closing of the email message is in focus on the screen and will cause screen flickering and flashing as the individual merge output messages are created.  You should avoid watching the screen  and the merge process if flashing for flickering light aggravates your vision or other health issues.

Document vs. Email

They are not the same.  A Word document and formatted email messages are quite different in their presentation and capabilities. For example, email does not support pages nor headers and footers.  If you are going to send the merge content as a message body, you must check its layout carefully in Word's Web view, and it would be advisable to create a dummy merge run with a limited record set to ensure that the results are what you expect.

If you want to be certain that the recipient will see the merge content as it appears in a document, merging as an attachment - preferably in PDF format is the best way forward.

The following depicts the Merge tab configured for a merge to email message with all required Merge tab and List Field tab conditions met.  The Email tab is enabled and the primary command button caption reads "NEXT >>."

EM_MTO-48

The Email tab provides the U\I to fully develop and disposition the merge output email messages. It is arranged in three sections using tabs; Message, Attachments and Copy To/Reply To as shown below:

EM_MTO-49

As the illustrations shows, after all other Merge and List Field (in applicable) tab requirements are met, the minimum email requirements to complete the merge are one or more email addressees and subject.

Message Tab
Send To

The Send To frame is used to select the required data source field containing the primary addressee email address and to type any other optional primary addressee email addresses.  When checked, the checkbox Bcc directs that all addressees be treated as blind carbon copy addresses.  When checked, the checkbox Retain stores all Send To frame data in the registry which is recalled and applied if applicable with the next email merge.

Subject

The Subject frame is used to define the email subject.  The subject can be constructed using an optional data source field, using optional fixed text or a combination of both.  When field and fixed content is used together, the options buttons are used to arrange the subject field either before or after the fixed text. The checkbox Retain functions as previously described.

Send/Signature Options

The Send/Signature Options frame is used to for defining applicable send attributes.  Its display and the display of a conditional frame Attachment Cover, is configured based on the merge output type as shown below.

EM_MTO-50

EM_MTO-51

The Send from dropdown lists all the email accounts associated with the user's Outlook profile. If checked, the checkbox "Include Default Signature" appends the user's default e-mail signature (if defined) to the email message body.  The signature dropdown lists all defined signature in the user's Outlook profile.  If checked, the checkbox Exch. Serv. assists users who have Exchange Server email accounts. See tip note below.

Exchange Server Accounts

The add-in may be problematic for some users, when used with Exchange Server e-mail accounts in Outlook. This relates to the way the accounts are configured and a difficulty to send the messages created in the temporary folder.

However the differences fall into two camps and I have therefore made the alternatives selectable via a 'Quotes' check box that is available on the userform when the Exchange Server option is selected. One or other setting of the 'Quotes' check box should suit most Exchange Server users.

EM_MTO-52
Attachment Cover

The Attachment Cover frame when displayed is used to define the email cover message content and format.  The content can be text typed directly in the U\I textbox provided or, if checked, the checkbox "HTML Format?" enables the user to browse for and select a Word .docx format file to select and apply as rich HTML content.

EM_MTO-53
Plain Text

EM_MTO-54
Rich Text

The following provides an example of a suitable personalized message cover document for a merge to e-mail as attachment process and a representation of an output mail item.

EM_MTO-54A
The message cover document content

EM_MTO-54B
Data Source field used in message cover

EM_MTO-54C
Representative output mail item
Additional Message Options

The Additional Message Options features should be self explanatory.

Attachments Tab

The attachments tab is provided to define and apply attachments to email messages created using one of the four email merge output methods.  The attachments can be defined using one or more data source record fields which contain the file path of the attachment or by browsing and selecting a file for attachment using the U\I.

EM_MTO-55

Record specific attachment file paths

EM_MTO-57
Attachments Tab showing data source field used to define record specific attachments and a direct path for an attachment common to all merge outputs.

Take care to ensure that any content in a data source field selected to define the attachment path resolves to a valid file path.  Otherwise, OUTLOOK will error and not create a mail item for the associated record\record set. This error is handled via user notification during processing.

EM_MTO-57
Copy To/Reply To Tab

The Copy To/Replay To tab is provided to define and apply CC and BCC addressees to the mail item.

EM_MTO-58

One To One Merges

The merge examples I've provided illustrated the Many To One features of the add-in only. The One To One merge processes do not utilize child fields but are otherwise similar.

A example One To One merge template and associated data source is provided in the add-in download package using the link at the end of this tips page.

E-Mail Group/OUTLOOK Interface

OUTLOOK Mail Items created using one of the four merge output to e-mail options are never sent automatically or directly by the add-in.

Instead, the add-in attempts to create a sub-folder in the user's OUTLOOK Drafts folder labeled "Enhanced Merge." This folder is used to store any mail items created by a merge output process. Mail items in this draft folder can later be pushed to the user's OUTLOOK outbox using additional add-in features or transferred and send directly by the user from within OUTLOOK.

There are many variations of OUTLOOK and email account types.  In some cases, and particularly IMAP accounts, the specific configuration may prevent the add-in from creating the "Enhanced Merge" sub-folder.  In these cases, the merge output mail items are stored directly in the OUTLOOK Drafts folder.  In these cases, the add-in feature "Push to Outbox" will not function to transfer draft mail items to the OUTLOOK outbox. The user must use OUTLOOK directly to transfer and send mail items.

OUTLOOK Enhanced Merge Sub-Folder

The following images illustrate the Enhanced Merge sub-folder created under the user's OUTLOOK application Drafts folder and several of the associated mail items created after completing the merge discussed above.

EM_MTO-59
Showing four of nine mail items created in the merge process

EM_MTO-60

The Enhanced Merge sub-folder allows the safe examination of the merge output mail items and any attached documents produced, before committing to sending them. Mail items remain in the Enhanced Merge sub-folder until the E-Mail Group "Push to Outbox" is executed or through direct user actions from within the user's OUTLOOK application.

Push to Outbox

Push to Outbox opens a mail item transfer dialog which:

EM_MTO-61

EM_MTO-62

The deferred delivery date and time fields are validated to ensure appropriate entries. The date field employs an enhanced  custom VBA date picker form originally developed by Trevor Eyre.

EM_MTO-63

Site Note IconSince its original release, Trevor and I have collaborated extensively to enhance the date picker form.  You can review my dedicated tips page on the date picker and download it for your personal use in other projects here.

When the Message Transfer dialog "SET" is executed, the resulting action is determined by the user's OUTLOOK application settings.  If the user's OUTLOOK application is configured to "Send immediately when connected" is set, then any mail item transferred will be sent when OUTLOOK is connected.  Otherwise, the transferred mail items will remain in the OUTLOOK outbox until connected and synchronized (i.e., manual send/Receive) through further user action either with the add-in features or directly from within OUTLOOK.

EM_MTO-64

Executing "SET" displays one of two conditional dialogs shown below.

Be aware that executing "Push to Outbox" will send ALL the mail items in the Enhanced Merge sub-folder to the OUTLOOK Outbox- INCLUDING ANY TEST MESSAGES YOU MAY HAVE LEFT THERE!!! If Outlook is configured to send items immediately, then all those mail items will be sent.

EM_MTO-65

Transfer "All" mail items

EM_MTO-66

Transfer mail item subset

Initiate Send (All Messages)

This is aimed at mail items which appear to be stuck in the Outbox. Instead of having to switch to OUTLOOK and open and send each such message again, the function aims to automate that task. It does not affect messages in the Enhanced Merge sub-folder created by the add-in. For that you should use "Push to Outbox."

EM_MTO-67

Merge Template Tools

The two commands buttons in the Merge Template Tools group are provided to assist user's in developing merge templates for use with the add-in.

Insert List Field

As discussed above, a special content control tagged "Independent List" can be employed in the merge template and used to develop highly customizable lists. This command inserts the "Independent List" content control at the selection.

While you can use this command repeatedly to insert multiple "Independent List" titled content controls in a merge template, only one will serve as the independent list associated with the add-in merge processes.  Use with discretion. 

Convert Mail Merge Fields

Recall that one of the main reasons for creating Enhanced Merge (Merge Many to One) was to break dependence on Word's native mail merge utility.  This means out with the old and in with the new!  If you initiate merge with a mail merge document open as the active document, you receive the following notification.

EM_MTO-68

Executing "Convert Mail Merge Fields" does not alter your existing mail merge document.  Instead it creates and saves a new file in the same folder containing the open mail merge document file.  The new file is named "EM - *." Where * represents the original mail merge document file name.  The image below left depicts a simple mail merge document showing merge fields.  The image below right depicts the resulting new document. Notice the merge fields have been replaced with content controls.  The placeholder text of the content control (as well as its title) are set to the old merge field name.

EM_MTO-69 EM_MTO-70


EM_MTO-71

The add-in Merge Template Tools - Convert Mail Merge Fields provides a basic utility for converting mail merge letter type main documents and associated merge fields into a merge template compatible with the add-in.

Note - It does not convert "Address Block" merge fields or conditional merge fields.

Employing Conditional If Fields (or Other Legacy Fields) in Merge Templates

The advantages of using conditional IF fields in merge templates is fully discussed by Word MVP Dave Rado in his article Making Your Mail Merge Intelligent by Using Fields.  These techniques can easily be adapted to use content controls in merge templates. The add-in download package at the conclusion of this tips page provides a demonstration merge template and data base providing a full description and examples.

Data Formatting

In most cases, it is more efficient to merge formatted data directly from the data source.  For example, if you want the dates from the data source to appear as MMMM d, yyyy (e.g., December 31, 2018) in the output merge document then use that format in the data source.


Data formatting can also be facilitated using the merge template content control tag property.  The following illustrations are provided as examples.

EM_MTO-71A EM_MTO-71B

In a similar manner, a data source date value can be advanced or regressed.

EM_MTO-71C

The following are just a few of the many possible formatting options available using these methods:

Premium Features

It would be very difficult if not impossible to create a "stock" add-in capable of meeting every user's requirements.  It wouldn't make much sense to it all for free, served up on a platter either.  After all, and to paraphrase Josey Wales; developer got to eat same as the worm.

Macro Enabled

Initially while working through the fledgling processes and examples I realized that in some cases a post merge macro process could resolve nagging anomalies in the output.  For example, in the merge document used in this example the resulting paragraph containing the "Independent List" isn't exactly right and if you are a stickler for grammar it can be flat out wrong. 

EM_MTO-72

Fortunately by modifying the template text slightly and employing a post merge macro, we can have the best of both worlds and correct text regardless of the number of associates defined in the data source.

EM_MTO-73

The specific VBA procedure (macro) to achieve this result is stored in a specific module of the VB Project of the merge template. The procedure is then called by name during the merge process.  The following illustrates the macro used (with module name and procedure named masked).

EM_MTO-74

If you are familiar with code you should readily see that the procedure simply evaluates the content of the Independent List contend control to determine if it contains two or more names and replaces text as appropriate.

VBA procedures like the one shown above can be used in any case where the merge output is conditional.

Contact the developer via the site feedback form to purchase a licensed version of this add-in with macro capability enabled.

Advanced Merges

Of course my old friend Graham Mayor was asked to preview the features of this add-in previously described and to offer his  feedback. As is often the case and always in fun, Graham's seems to enjoy offering challenging suggestions.  Two of which are implemented below.  

Merging Graphics\Boolean Values as Checkboxes

Photos can be defined and in the data source and be displayed in merge outputs.  Boolean values (e.g., True\False, Yes\No or 1\0) can be defined in the data source and displayed as checkboxes in the merge outputs.

EM_MTO-75

Merge to Dropdown List or Barcode Content Controls

With the right content controls and defined source data, you can merge to and fill dropdown lists or display a bar code (Word 2016 and higher only) in the merge output. 

EM_MTO-76

EM_MTO-77

EM_MTO-78

REMOVE CONTENT CONTROLS AFTER MERGE

Dropdown list and combo box content controls used in the merge template are not removed when the configuration option remove content controls after merge is applied.

To preserve other type controls such as the Special Instructions text control and Express Shipping checkbox shown above you must tag the content control "Merge_As_Fillin."

EM_MTO-79

Assistance with these advanced merges as well as assistance with other advance merge requirements or macros can be obtained by contacting the developer via the site feedback from.  Current consulting rates will apply.

Conclusion

You can download Enhanced Merge (Merge Many to One) and some of the templates and data sources used in these examples here: Enhanced Merge (Merge Many to One) Download Package.

Site Note Icon For more on template add-ins and how to load them, see: Organizing Your Macros/Template Add-ins at: Installing Macros

That's it! I hope you find the add-in and this tips page useful and informative.

Share

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo