Many To One Mail Merge


Many people access the material from this web site daily. Most just leech what they want and run. That's OK, provided they are not selling on the material as their own; however if your productivity gains from the material you have used, a donation from the money you have saved, however small, would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.

Many to one mail merge
	The mail merge function in Microsoft Word works only with a flat data file as a one to one merge. There have been various approaches proposed to enable the merging of lists associated with each record - for example invoices where multiple items appear associated with each buyer. 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 regular contributor to this site, Doug Robbins has come up with a pair of add-in to attempt to simplify the process, to which I have added to the Word 2007 version a ribbon command. This page documents the use of the add-ins. The add-in is supplied in two versions, for Word 2003 and 2007, however the add-ins both require that the data source used for the merge is in Excel format. The Word 2003 version will not work with Excel 2007 XLSX format data sources. The Word 2007 version includes a modification to the Mailings Tab of the ribbon. The Word 2003 version has a custom toolbar. Both versions require Outlook to be the default e-mail application.
Notes:	As already indicated, the data source must be in Excel 97-2003 format. Excel 2007 is capable of saving in this format, so it should not present a problem for Office 2007 users to save a copy of your data file in that format. If there are likely to be some rows with no data in some of the 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. 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 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 browser. When Word is run the add-in will load and the calling command will be added to a custom toolbar or the mailings tab of the ribbon according to version.
Word 2003

Word 2007

Using the add-ins
	For the purpose of explanation of the use of the add-in, Doug has provided a sample merge document and an Excel data source. 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. While testing the e-mail option(s) with the sample data source, configure Outlook not to send messages immediately! The sample messages will then go to the outbox from where they can be deleted. In the Name & Address section, the key field is OWNER and the child fields are PROP_ID and LEGAL_DESC. The fields are inserted as shown in the upper illustration and 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 and LEGAL_DESC) is required in each location where that data may be reproduced.

	The next step is to run the macro. If the data source is detached or is of the wrong type of file, or if the merge document type is wrong the user will be presented with a warning message that explains the error i.e.

	If all is well the macro presents a userform from which the key field and the repeating fields are selected:

	The messaging function is designed to use Outlook. Later versions may include error trapping to establish that Outlook is present and the default e-mail application. If you have Outlook configured to spell check messages before sending, then ordinarily you may get a system message between each e-mail created by the add-in as follows:

	It is not possible to switch off the Outlook spell checking option using vba while Outlook is running, and I feel it would be inappropriate to close Outlook and re-open it in order to re-read the registry entry that determines the setting. To overcome this, and until we can find a more appropriate solution, I have added code to interrogate the registry to determine whether the Outlook spell check is set or not when the add-in is used. If the spell check option is selected, you will see the following message and the option to merge as the body of an e-mail document is disabled:

	If you see that message click OK, then if you wish to merge the documents as the body of the e-mail message, cancel the macro from the main userform and set the following option in Outlook > Tools > Options. If you wish to use any of the other merge options, including sending the documents as e-mail attachments, you may continue normally.

	With Word 2003, you may get a security warning message between each mail merged message, which can be a bit of a pain with documents you have produced yourself and know to be free from problems:

	If you experience this, you can avoid user interaction by installing the Click Yes utility that you can download from this site, which intercepts the dialog and, as its name implies, clicks 'yes'. You can turn the utility on or off as required.
	If the option to merge to separate files is selected, then the files are stored in the folder C:\MergeFiles. (If this folder does not exist it will be created). It may become necessary in future versions to allow the user to select the location, but for now if you require a different location for the merged documents, you will have to edit the macro.
Note:	The macro names the separate files using the key field to provide the names. There must be no illegal filename characters in the key field or the macro will crash. Again if there is sufficient demand we may add further error correction to overcome this.