Graham Mayor

Mail Merge 'Many-to-One', 'One to One' (both including to E-mail with Attachments) and use Merge fields in Protected Forms.

The featured add-in provides the following functions:

1. Many to One Merge to e-mail or separate Word documents or PDF format documents or to the printer.
2. Many to One Mail Merge with attachments to e-mail.
3. One to One Mail Merge to e-mail or separate Word documents or PDF format documents or to the printer.
4. One to One Mail Merge to e-mail with attachments.
5. Combine Mail Merge with protected form fields.

The add-in requires that the data source is Sheet1 of an Excel workbook, with a header row starting at cell A1 and that Outlook is available to send the messages.

As a separate project, I have also produced a tool to make it relatively easy to extract Outlook Contacts data to an Excel worksheet should it be required to use Outlook data with this add-in.

Note that the functions of this add-in do not constitute a true mail merge. Word's mail merge function is only used as a convenient way of placing the variable data for the finished documents.

The function processes the complete record set serially. If you require a filtered record set, then you should filter in Excel and process the filtered worksheet as the data source.

Do not use the address block field in your document and do not include blank columns or rows in the worksheet used as a data source.

Error trapping in the add-in will detect when the worksheet is incompatible and will attempt to point out where the problems lie.

The add-in is provided in the format of a macro enabled template which should be installed in the Word Startup folder (with Word closed) so that it loads with Word and makes its commands available to the Mailings Tab of the Word ribbon.

The add-in is also provided as a self executing zip file, which will install the add-in and remove older versions.

The two ribbon buttons and the sub menu provide access to the various functions of the add-in:

• Start Merge - runs the add-in.

and in the Misc Functions sub menu

• Send Messages - Sends the e-mail messages created by the application to the intended recipients.

Be aware that clicking the Send button will send ALL the messages in the application's temporary folder - INCLUDING ANY TEST MESSAGES YOU MAY HAVE LEFT THERE!!!

No messages are sent from the application other than by clicking this option.

• Remove Temp Folder - The application creates a temporary folder in Outlook to hold the messages before transmission. This button provides a means of removing that folder and its unwanted content. If the folder is not present when the application is next run, it will be recreated.
• Reset Defaults - The application stores various settings in the registry. This button removes the registry values created by the application.
• Force Send - All messages - This is aimed at messages which appear to be stuck in the Outbox. Instead of having to open and send each such message again, the function aims to automate that task. It does not affect messages in the temporary folder created by the add-in. For that you should use 'Send Messages', the first item in the menu.

Microsoft's Windows security produces the following warning message when the merge document is opened and its data attached.

The security measure that produces this warning can be disabled by a simple registry change, but I realise that company IT departments may be reluctant to permit such a change, so you may see this message twice when using the add-in - once when you open the message and again if you use the HTML option (see later) with a personalised covering message.

This warning, apart from being annoying, should not affect the working of the add-in.

Many to One

The mail merge function in Microsoft Word works only with a flat data file as a 'One-to-One' merge i.e. a separate set of values for each record in a single document.  There have been various approaches proposed to enable the merging of lists associated with records that share a common 'key' field - for example a list of purchased items from a particular customer where multiple separate product items are associated with each customer. Microsoft's own suggested approach is quite complicated and recorded at 'How to use mail merge to create a list sorted by category'.

Fellow Word MVP Paul Edstein has posted a tutorial on this, with working field codes and a sample Excel data source which you can download from this web site but it can be a tad bewildering and even Paul would concede that creating this type of merge can be intimidating for those unskilled in mail merge.

To overcome this difficulty another Antipodean MVP and occasional contributor to this site, Doug Robbins, produced an add-in to attempt to simplify the process. I developed the principles Doug had come up with to form the basis of the original Many to One add-in, which is now replaced by the completely re-designed add-in described on this page.

The function uses the Shell32.DLL object library. With some operating systems, notably Windows 7 - 64 bit, Shell32.DLL may not be available by default and thus produces an error when the function is started. Where that is an issue, download the explanatory document, which should help you resolve it.

In addition, the add-in uses the Microsoft Common Controls object library and this is frequently not available, so I have included that file in the ZIP, complete with instructions for its use.

If there are likely to be some rows with no data, in some of the Excel data source fields, all of the cells that represent the data source must be formatted as text. Otherwise, errors can occur, when the routine encounters a type of data that it is not expecting.

For Many to One to function correctly, the data must be sorted by the field that is going to be used as the key field. Not sorting it that way will not cause an error, but the desired result will not be achieved.

The merge starts a new document each time the key field changes, which feature is used to different effect in the One to One options, but more of that later.

When merging to e-mail, ensure that the e-mail addresses are in a valid format and that all the records have a valid e-mail address. The add-in will detect invalid e-mail addresses. Where records do not have a valid e-mail address you must correct the address, replace it with a valid address (such as your own e-mail address) or use a dummy address as in the example data supplied with the add-in.

When merging to e-mail, the application should start Outlook, if it is not already running, but such is the relationship between the Office products that this may not happen. It is therefore advisable to start Outlook before running the application.

The appropriate add-in should be should be extracted to the Word start-up folder. By default the start-up folder location is a hidden location, but it can easily be located by typing %appdata%\Microsoft\Word\Startup in the address bar of Windows Explorer or your Internet Explorer browser.

For the purpose of explanation of the use of the add-in, a sample document and an Excel data source are included in the zip file. An extract of the relevant part of the merge document is included below.

The merge document must be set up as a conventional letter merge with the Excel data file attached as its data source. The example document is supplied as an ordinary Word document.

The messages created by the application will go to a sub folder of the Outlook account Drafts folder called 'Merge Many To One'. Messages are only sent when you choose to send them. If you have the option set to send messages immediately when connected, then that is exactly what will happen, so do not do that unless you really do want to send the messages to the recipients indicated in the data file.

The example data supplied with add-in contains dummy e-mail addresses and so it will not matter too much if you send the messages. They will only be bounced back with a delivery failure error.

With the example document, the 'Key field' is OWNER and the 'Child fields' are PROP_ID, LEGAL_DESC. for the totaled fields ACRES and VALUE. and for the List field NAME.

The fields are inserted in the document as shown in the next illustration. They do not have to be placed in a table, but a table format is ideal for lists.

The intended result is shown in the lower illustration of the two illustrations that immediately follow. Note that only one set of each of the “repeating” fields (PROP_ID, LEGAL_DESC, ACRES and VALUE) is required in each location where that data may be reproduced as shown in the table of the first illustration. Fields not designated as child fields will be reproduced from the first record that matches the key field. In the example document, those are the fields of the address.

Totaling the values in the columns.

With lists of items, it is often desirable to provide a total of the values of one of more of the listed fields. This can be done quite simply, subject to two provisos.

1. That the values returned from the data source are numbers. It may be stating the obvious but you cannot total values that are not numeric. Numbers formatted as text in Excel with currency symbols should be OK.

2. The list of values is in a table column. In the example shown below the Child Field 'Value' is inserted in a table cell. The function will build sufficient rows for the child fields, as shown in the third of the following illustrations.

When both conditions are met, you can add an Expression (=) field to a final row of the table. This field can be formatted using switches as required. The fields to be added - here 'VALUE' may not be formatted with switches. If you do so the range of values in the column will simply be added together.

IMPORTANT!!! Do not use nested tables (i.e. tables inserted within other tables) when creating your merge document and do not use merged cells in the table used for the child fields.

 

Running the Merge

Having setup the merge document and data source, the next step is to run the macro. The function begins by making a range of checks to ensure the viability of the merge document and the worksheet attached as its data source. A range of messages informs of any anomalies that may be found. Should all the tests be passed, the first time the add-in is run, it displays the disclaimer text similar to that used in all my add-ins. Please take a moment to read it.

When the disclaimer is dismissed, the start page of the multi-page userform is displayed. From this page you can select from a variety of merge options, and also select to display the disclaimer text.

Your choice of Merge Many to One or Merge One to One is retained between merges, as most users will tend to repeat the same type of merge. The change is stored when you click the 'Continue ' button.

 

The add-in will determine whether the merge document contains legacy form fields (see illustrations below) and whether the document is protected for forms.

If the form is protected with a password, you will need to know that password in order to proceed with a merge containing form fields.

If the merge document contains form fields and is not protected, you will be prompted both to choose whether to protect the merged documents, and if you choose to protect you will be given an option to provide a password.

If the form is already protected and a password has been used to protect the form, you will be prompted for that password. If you don't know the password - see http://www.gmayor.com/Remove_Password.htm. 

The form documents created will all be protected, with the chosen password where such a password is entered.

 

When a password has been entered, you will be asked to confirm it, as the password entered is masked by asterisks making review impossible. No confirmation is required for a blank password entry.

When the document is protected for forms, the 'Many to One' merge option is unavailable, and you may only create Word documents either as files or as files attached to e-mail messages.

When the macro is run, the form is unprotected and the merge fields are replaced with docvariable fields.

 You can get the very useful Classic Forms Controls group, shown to the right of the Mail Merge Forms group, from http://gregmaxey.mvps.org/word_tip_pages/add_classic_form_controls.html.

 

The docvariable fields are populated with the data from each record (below) in turn and each modified document is re-protected and saved in a folder chosen from the add-in.

Merge to Individual Files

A One to One merge to individual files provides an alternative approach to splitting merges into separate files from the Individual Merge Letters add-in. It provides a similar range of functions, except that it does not offer the option to merge using Adobe Acrobat where present, and unlike the other add-in it does not use mail merge to process the documents. This can produce better results than the Individual Merge Letters add-in - especially with documents that contain complex tables and/or several sections.

The dialog for the One to One merge option is relatively simple. it merely requires the selection of a field for use in naming the document. Apart from the title bar captions the dialogs are identical whether merging to Word documents or PDF.

The dialog includes a button marked 'Select additional filename options'. This provides the opportunity to add a second field value to the filename and/or accompanying text. That text can be placed before or after the two fields, with a choice of separator character between each element. The field order can be transposed if required. This may be important in the case where the filename field is also the key field.

The dialog indicates the format of the filename (see below). Not indicated is the filename extension, which will be PDF or DOCX according to the initial selection of the merge type.

IMPORTANT!
As this add-in also provides functionality for Many to One mail merges, it is imperative that the initial filename field chosen has unique data in consecutive records. The function will produce a new document ONLY when the value in the filename field in a One to One merge changes. If the value in the filename field of the following record is identical, then the following record will be skipped.

There are no additional options for a merge to individual documents and so click 'Continue' to run the process of creating the separate document.

You will have to select a location to store the documents in the next dialog, or they will be created in the current document folder.

Click OK and the merge creates the documents in the chosen folder. As nothing much appears to be happening I have added a progress indicator.

Merging with the Many to One option to individual documents provides a few more options and introduces the concept of a 'Key' field and associated 'Child' fields. The 'Key' field is a field that contains data related to all the records in the set. The 'Child' fields are those fields from the set that are to be listed.

The list can be in a table, which will be ideal if you wish to calculate totals for values, or it can be a plain list, one to a line, or a continuous list.

Where the list is not in a table, you can choose the separator character and whether to use 'and' or 'or' as the separator for the last item in the list. e.g.

• Value1
• Value2
• Value3
and/or
• Value 4

Or

• Value1, Value2, Value3, and/or Value4

 

Independent List Field

The Many to One merge provides for a single child field to be used to create a separately formatted list, regardless of whether the child field has already been used in a table or list elsewhere in the document. Access to this option is found on the List Field tab.

Such a field may have a variety of uses, but it was envisaged that it would be used for providing signature blocks for legal documents where several parties would sign the document.

In order to employ this field in the document, it will be necessary to enter a DocVariable field { DocVariable varSig } at the position where the list is to be displayed. Such a field can be seen in the example at the start of the page. Clearly this field must be present before the merge process if run.

You can manually enter the field by typing CTRL+F9 and entering DocVariable varSig between the brackets { } so produced.

The field selected as List field is a Child field so must have been selected as a Child field on the main page of the Userform. Only Child fields are available for selection on the List Field page. If the field you want to use is not available for selection, return to the Merge tabbed page and select it as a child field.

You can use any text in the Leading and Trailing text boxes to give the spacing you require. Note that if you type [TAB] in either text box, this will be substituted by a tab character when the merge is run. The Independent List Layout does not indicate the actual spacing provided by the tab setting applied to the paragraph containing the DocVariable field so you may need to establish what leading and trailing text is required to give the effect you seek, before running the merge. See the two illustrations below which indicate how the function is applied in practice.

Merge to Printer

A One to One merge to printer is include for completeness. In practice you would probably use the Word mail merge function to merge to printer. The main advantage of this function is that each document is treated as a separate print job, which can be of value when printers include stapling functions.

Merging to printer with this function requires a key field. This may be any field in the merge document which has a value for each record and which value is different in consecutive records. It does not matter if the same value is duplicated in a subsequent record, but the function only creates a new document when the value in the key field changes.

A Many to One merge to printer provides the additional functions relating to the use of Child fields outlined in the previous section.

Merge to E-mail Message

The function is able to merge the document to the body of an e-mail message. This function, more than any other, took up a lot of development time to get to the stage it is currently at. There are several issues involved and thus this part of the process uses an entirely different means of communicating with message creation part of Outlook from the merge to attachment options with covering message.

A particular problem is that Word document format and formatted e-mail messages are quite different in their presentation and capabilities. E-mail does not, for example, support pages nor headers and footers, so if you are going to send the document 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 document as you intend, merging as an attachment - preferably in PDF format is the best way forward.

The dialogs for One to One and Many to One are shown below.

Note that both options enable more of the tabbed pages of the userform. Common to both are the 'Include Attachments' and the 'Copy to Third Party'. The Many to One option additionally includes the 'List Field' which was described in a previous section.

Invalid e-mail addresses

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. When such an option is selected, the add-in will examine all the e-mail addresses in the record set and if any addresses do not meet the required standards for e-mail address formats, the process will be stopped and you will see a warning message and the erroneous record numbers and associated e-mail addresses are listed in a new document to help you locate and correct them.

Note that it is the record number shown and not the row number in the Excel worksheet.

When attaching the Excel data source to the merge document, use the default OLE DB connection option and select the appropriate worksheet from the dialog. Use of alternative connections is likely to produce missing e-mail address errors.

This function caters for the vast majority of possible e-mail address configurations, however should you discover a genuine e-mail address reported as erroneous, or an erroneous address that is not flagged as incorrect then please e-mail me with the details so that I may modify the error trapping.

 

Outlook Temporary Folder

When 'Continue' is clicked, the messages are sent to a temporary sub folder of the Outlook drafts folder (to ensure a unique location) named Merge Many To One. If this folder is not present, it will be created.

One user had reported during the testing cycle that the messages were created not in the temporary sub folder, but in the Drafts folder itself. The process to send messages will not handle the messages if they are in the Drafts folder. If that occurs, manually drag the messages to the correct folder before selecting the option to Send.

I would appreciate feedback from other any other user who experiences this problem as so far I have been unable to recreate it. In particular I would appreciate details of Outlook account names and types.

A button on the ribbon provides a function to remove the temporary folder should you wish to do so. When removed the temporary folder is removed to the Deleted items folder.

The temporary folder allows the safe examination of the messages and attached documents produced, before committing to sending them. They will only be sent when the 'Send Messages' button is clicked.

Word 2010 changed the way e-mail accounts are filed, so each e-mail account can have its own inbox/outbox and, more to the point, Drafts folder. The add-in should detect and create the temporary folder in the Drafts folder relating to the default email account.

If you have Outlook configured to send messages immediately when connected (see below), clicking the 'send messages' button, irretrievably sends the messages to their destination e-mail addresses.

If you have the option set not to send messages immediately when connected, then you have a further opportunity to change your mind, by clicking the 'Continue' button, which moves the messages to the Outbox.

If you choose the 'Continue' option, then you may use the Outlook Send and Receive function to send on the messages.

Messages that appear to be 'stuck' in the Outlook Outbox can often be sent by clicking the send button again and choosing the Send Now option.

 

At various times you may see one of a variety of error and information messages. I have not reproduced them here as they are self explanatory.

Message Subject

The message subject is entered into the userform, and there is an option to include the content of a field in the text. Note that the value used in the subject will be that from the key record. The field may be placed before or after the text

Include Attachments

Attachments may be included with the messages, and it was this provision that caused the most head scratching for documents merged to the message body. You may indicate the full path of the attachment in a field in the data file, which would allow separate attachments to each message, or you could browse to select a file to attach to all messages.

The Attachments dialog is similar for both One to One and Many to One selections.

Copy to Third Party

Users may wish to send the messages to a variety of recipients and the application allows for this. The e-mail field, selected on the Merge tabbed page, may contain multiple e-mail addresses separated by semi-colons.

The application will send the messages to each of the recipients entered into the e-mail field. If the BCC option is selected, all recipients will be directed to the 'BCC' box of the message header and thus be hidden from one another. If unselected, then the first address will be directed to the 'To'  box and the remainder to the 'CC' box.

Users may also select a second e-mail field from the data source or enter an e-mail address manually. This last option can be useful when it is a requirement to send copies of messages to a co-worker, supervisor or head office.

Because the requirement to send a copy to a supervisor etc. is likely to be a persistent requirement, there is a check box on the Copy to Third Party page that when checked will store the text address for re-use. Unchecking the box will clear the data. Note that if you check this box, copies of all messages created by this function will be sent to that address.

Again the additional recipients may be hidden from one another and the main recipient(s), but if the main recipients are hidden and you choose the option not to hide the additional recipients, you will be prompted to confirm that choice.

Merge to E-mail as Attachment

The final option combines all the other functions already described and merges the document as an attachment to an e-mail message in either Adobe PDF or Word DOCX format. The covering message may be plain text entered from the userform or a formatted Word document.

Formatted Word Document as Message Body

Checking the HTML Format check box  provides an option to select a Word document to use as message body.

The HTML Format option removes the option to select a message signature. If you need a signature block on your formatted covering message, then add it to the message document.

 

Because of differences in the presentation of userforms between Word 2010 and Word 2013, the positioning of elements on the userform will differ between Word versions. In the previous two illustrations, the former was captured from Word 2010, the latter from 2013. Note the difference in border widths.

Create your covering message in Word, formatted as you wish it to appear (checking the appearance in Word's Web view) and save as 'docx' format. Enter the path to that document, which will be used as a template to create the covering documents. Inevitably this will slow the merge as that document is accessed for each record.

The results is as follows:

Do not save the covering message as a macro enabled document or template, or in the case of DOT format, do not include macros in the template.

Personalised formatted covering messages

The document used as a covering message may also include form fields from the same datasource. This provides the opportunity to personalise the covering messages.

Create the covering message as shown above, and temporarily attach the same data source used for the attachments. Insert the fields that you wish to use e.g.:

Then set the document type to 'Normal Word Document (though the add-in will do this automatically if you forget).

When merged the data is read into the fields to produce personalised accompanying messages with the attachments as follows (this time with a PDF format attachment).:

A few important matters not previously mentioned.

The merge to e-mail message option is dependent upon the option to send the current document to an e-mail recipient being available in Word. If you find that this option does not produce any messages in the Outlook outbox, then the most probable reason is that this option is not available.

Its omission is caused by a MAPI error. Repairing Office from the Windows Control Panel add or remove programs applet should fix it. Ensuring Outlook is the default e-mail application in Windows should also fix it, but doesn't always. This is not the fault of the add-in, but of the host application. As a quick check, add the Send To Mail Recipient command from the All Commands section to the Quick Access Toolbar. If there is a problem, the command will be grayed out. This issue does not affect the alternative merge options provided.

Field Errors

When using the add-in it is possible that you will encounter the error message shown in the next illustration in your document attachments.

The error occurs because the merge fields in the document are replaced with docvariable fields. However, because of the way mail merge ignores certain characters that may be used in the data source field names, the values that end up in the document variables may not match the variable names in the docvariable fields.

There is no documentation, that I have been able to find, that discusses how Word mail merge handles field names, and, worse still, when there are several similar field names, the mail merge renames the fields to avoid conflicts. I have trapped all those unwanted characters that may reasonably found in a field name, but I cannot do anything about your field naming with similar field names.

If you see such an error in any of your merge documents, check out the field names, before reporting the issue to me. Toggle the display of fields to display the errant field construction, so that you may cross reference it with the corresponding field in the data source. The character that is different in the Excel data source from the Word field is the one that needs to be fixed to make it work correctly. Ensure especially that there are no leading spaces in your column headings

If you are unable to edit the data source for whatever reason, and this issue is thus insurmountable, let me know the character in question and I will add it to the list of corrections. I do not anticipate that this will occur very often.

You may encounter the following error message when you select Start Merge from the ribbon.

The add-in addresses the Excel data directly. It is not a conventional mail merge. Consequently if the macro cannot access the data it requires, the error message is created.

This error will occur in circumstances that a conventional merge would handle. In particular if you have hidden columns in your worksheet, or you have hard to spot spaces in columns that are not used for the process, you will get phantom field names, which the add-in will not be able to resolve. If you see this error message, the first thing to check is for such phantom fields.

If you experience problems with this add-in, then please read this page - especially the yellow panels - to see if your problem is addressed, before reporting it.

DO NOT use the add-in template as a document template for creating merge documents!

- Now download the add-in

 

 Merge Tools

Some time ago I developed a pair of add-ins, based on an idea by fellow Word MVP Doug Robbins, to enable mail merges to email with attachments and to perform 'many to one' merges.

Over the period these add-ins have been available I have had regular feedback from users, suggesting enhancements, reporting bugs and pointing out and limitations.

As the projects became more complex, I found that a lot of the material was being duplicated, so I decided to take the plunge and redeveloped the add-in from the ground up, to combine the two processes and to incorporate as many of the suggestions for improvement as possible. This page demonstrates the fruit of the many hours of work it took to complete and test the project.

Home | What's New | Word Pages | Links | Downloads | Photo Gallery | Search | Contact