Where the open source PDF creation tool PDF Creator is installed, additional options to use PDF Creator are available for both Many to One and One to One merges. PDF Creator additionally offers the option to add security measures to the merged PDF files, which are not available to Word's built-in PDF Creation function. The PDF Creator functionality is described in the last section at the end of this page, followed by the download button.
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.
When the add-in is installed and run for the first time, the process starts by displaying a userform to set some preferences. You can redisplay the dialog as required from the ribbon button.
Earlier versions would check whether Outlook was installed, however as the latest versions of Office have Outlook installed, I have included a manual confirmation. For the e-mail options to work, Outlook must be the default e-mail application and the check box must be checked.
Show Filter Dialog at Start is self evident. With the check box unchecked. The filter is not shown and all the records in the set are processed.
E-mail validation may be disabled. It is strongly advised that you don't check this option unless you use an Exchange Server with recipient nicknames that are not recognised by the add-in as e-mail addresses. This option is only enabled if the Outlook check box is checked.
The add-in has included an option to enable the use of a macro to be run before saving the individual documents for some time, though in some versions it had not been enabled for most users. This version has that option enabled if checked in the configuration. Support for creating macros is not provided.
Where macros are enabled in the configuration, an additional tabbed page is available on the main program dialog with options to include a macro and to 'Fix Hyperlinks' This latter option should not be required by most users, but if hyperlinks are not resolved correctly, it can be checked to address that issue.
It has been found that where the '#' symbol is used in the data, it can cause the process to misread the following data. The add-in therefore detects the use of the '#' symbol and stops the process. This hash check may be disabled, but should you do so, check the results thoroughly, especially if you are sending the merges to e-mail, where incorrect data may be sent out and cause embarrassment.
The add-in allows the use of PDF Creator version 2.5 to be used to create more secure PDF files, when that application is installed. Do not check the box if PDF Creator 2.5 is not installed.
Mute sounds. The add-in uses sounds as alerts throughout for messaging etc. Some users prefer to work without such sounds so the option is included to mute them
he temporary folder is set by default as a sub folder of the user's TEMP folder. For this reason when the process detects a new user, the dialog is shown and the new user's TEMP folder is displayed.. You can change the folder to a location of your choice, but there is no need to do so.
Previous versions prompted each time for the location to save the document. The dialog now requires the user to set a folder to save the documents to. The folder selected will become the default for future merges using the add-in. You can change it by re-running the configuration from the ribbon button.
Each of the options in the configuration dialog is accompanied by context sensitive help displayed by clicking the '?' buttons alongside the check boxes. Similar help buttons are available throughout the process.
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 my original Many to One add-in, which is now replaced by the completely re-designed add-in described on this page.
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.
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 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.
You may use switches with fields that are not 'Child fields', and include such fields in conditional constructions, but when doing so bear in mind that the merge fields are converted to docvariable fields during the process and as docvariables cannot contain a null value, any field in the data source that has a null value is replaced in the docvariable field with a zero length space ChrW(8203)
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.
As an aid to formatting numbers and dates formatted and displayed in Excel as text, you could use the following Excel macro. Select the column(s) to be converted and run the macro:
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.
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, if the Filter option in the configuration has been set, a Filter dialog will be displayed. This is a simple filter that allows users to merge only selected field values. You may only choose one value from each of four fields.
The dialog is populated with the fields from the data set, and text entered in the text boxes is not case sensitive. If a field is selected, and text entered associated with that field, the check box alongside is automatically checked. Only checked filters will be applied to the data set.
If a field is entered then subsequently unchecked, the entry is removed and the list moves up to fill the empty space.
The filter uses a 'Like' comparison so you may use wildcards in the values to be searched. E.g to merge only records beginning with (say) "A & " (there are two such records in the example files) enter A &* in the search field.
With large data sets, the filtering (which works by creating a temporary copy of the worksheet and document and removing records from the temporary worksheet, thus the original worksheet remains unchanged) can take a while to complete. I have therefore added a progress indicator to the filter.
Cancelling at any stage of the process restores the original document and data.
The main multi-page userform is then displayed. From this page you can select from a variety of merge options.
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.
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.
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 insert a rich text content control, with the title 'List Field' at the position(s) where the list is to be displayed. Clearly this content control must be present before the merge process if run.
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.
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.
A Many to One merge to printer provides the additional functions relating to the use of Child fields outlined in the previous section.
As the Many to One add-in is not a mail merge application, merging graphics from child fields is not simply a matter of inserting the correct syntax as discussed at Mail Merge Graphics. I have therefore incorporated a process to convert a merged graphics path that points to an existing file, inserted into the child field table.
Note that this facility is only available for Many to One merges where the child fields are in a table as in the following example:
The data file for this would be something like the following, with the paths available on a local disc
The add-in features an additional tab
Checking the check box populates a combo box with popular graphics file extensions. The extension helps the process locate the file path in the table, so it is essential that all the graphics merged are of the same format and the extension selected matches that format.
If either of the above conditions is not true, or the file path indicated is no longer valid, the file path only is merged.
As the Many to One add-in is not a mail merge application, merging barcodes (as with graphics above) is not simply a matter of inserting the correct syntax as discussed at Insert Bar Code. I have therefore incorporated a process to convert a merged field, inserted into the child field table. Note that this facility is only available for Many to One merges where the child fields are in a table as in the following examples.
Because the process is not a true mail merge application and the merge fields are used merely as place markers using MERGEBARCODE fields in the body of the merge document will not work. To get around this issue, the process includes code which will create a bar code from a merge field, provided that the merge field is bounded by tags && and %% (see the User Information panel on the dialog below).
The process can convert a selected column (column 1 in the following illustration) to barcode type 39, 128 and QR in the following three fields respectively.
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.
When Word is used as e-mail editor, as is the case when the documents are merged to the body of an e-mail message, and small attachment called 'header.htm' is attached to the message. This is a harmless addition which has been present since at least Word 2000, and there is no simple way around it.
Up to version 8.8 of the add-in, I allowed the add-in to produce these attachments, as this produced a cleaner running add-in, but it had not occurred to me that the attachments were also added when the merge was to attachments, with a customized message body. I have therefore modified the process to create the message bodies in a different way, but this has the disadvantage that each message must be opened and closed.
Such opening and closing produces flashing on screen as the messages are displayed briefly, and to date I have not found a way to avoid this, so when the Merge to Email Message option is selected (or the HTML check box is selected) the user will see a warning message. In addition I have removed the progress indicator for Merges to Message body (but not when the HTML check box is selected, as that is a much slower process and user indication is desirable).
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.
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.
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.
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.
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.
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.
When the Send Messages button is clicked, the user is presented with a dialog in which to choose whether to defer sending the messages until a later time. By default the current time and date is displayed.
The dialog also allows for the sending of a subset of the messages in the temporary draft folder. This is only necessary when the e-mail service provider blocks large numbers of e-mail messages as part of a spam deterrent policy. If this is not required simply leave the setting at the default '0' value.
The dialog is error trapped against inappropriate entry and from version 10.1 features a date picker control that does not use the MSCOMCT2.OCX object library, that has proved problematical for uses with 64 bit operating systems. to provide a date picker function.
If you have Outlook configured to send messages immediately when connected (see below), and subject to the values configured in the Delay function, 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.
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.
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 in the subject text box.
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.
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.
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.
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.
All e-mail options allow the user to select an alternative reply-to address. This address may be retained for future use while the associated check box is checked.
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.
Checking the HTML Format check box provides an option to select a Word document to use as message body.
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:
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).:
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.
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
You may encounter the following error message when you select Start Merge from the ribbon.
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.
When merging to PDF format with PDFCreator the add-in additionally provides the option to engage PDF security measures. When the 'Continue' button on the main dialog is clicked, a second userform will open to offer that facility. Clicking 'Cancel' on this userform will cancel the whole process.
The items shown disabled in the following illustration become available when a Master Password is entered. If you don't want additional security measures then don't set a Master Password!
The additional options become available when there is text in the Master password text box. This includes the provision to add a common password to open the PDFs or to use a password for each document from a field in the merge data source, dependent on the setting of the 'User Password from Field' check box
If you continue without setting options, you will see the following dialog. Click 'No' to set options or 'Yes' to continue without.
Currently the additional security options are only supported when the option PDF is checked. Should you check one of the other PDF format types, the dialog changes as shown below and any security settings entered are returned to their default values.
The PDF files are created in the same folder as the documents.
This YouTube video is recorded at 1080P HD, so if it looks blurred on your screen, change your YouTube view settings to HD.
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.