|
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-ins to attempt to simplify the
process, to which I have added to the Word 2007/2010 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/2010, 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 XLSX format data sources.
The Word 2007/2010
version includes a modification to the Mailings Tab of the ribbon and
includes an additional function to merge charts.
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/2010 are capable of saving in this format, so it should not present
a problem for Office 2007/2010 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/2010 |
 |
| Using the add-ins - MERGE MANY TO ONE (Word
2003 & 2007/2010) |
|
|
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. The macro is designed to work in conjunction
with Outlook as the default email application. If the merge is going to
be as the body of a mail merge message, and you have Outlook configured
to spell check messages before sending, Then you will see the following
warning as each message is sent: |
|
 |
|
|
With a data
file of more than a few records, this will soon become extremely
tedious, so before progressing further, the macro checks the registry to
establish whether the spell check option is selected. As it is not
possible to disable the spell check using the macro, If it is selected
you are presented with the following dialog, which provides the option
to cancel and change the setting manually, or to continue without the
option to merge to the body of an e-mail message. |
|
Note: |
When
merging to the body of an e-mail message, be aware that the html format
used bears little relationship to your Word merge document, as the
formatting requirements for Word document and html are entirely
different. Ensure that the layout of the messages is correct (see the
Word web view) before sending. |
|
 |
|
|
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.
|
|
|
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 for Word 2003, 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.
The Word
2007/2010 version gives the user the opportunity to select where to save the
files from version 10. |
|
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. |
|
Using the add-ins
- CHART MERGE (Word 2007/2010 only) |
|
Setting up the data source |
| |
The data source must be in the
form of an Excel Workbook setup as described below. For convenience of
reference, name the first Worksheet in the Workbook as "Merge Data" and
name the second sheet as "Chart" |
| Merge Data |
| |
The field
names must be set up in the first row of the Merge Data sheet with the
data in the rows starting with row two. |
| Note: |
The row following the last row
of data should be coloured Red as shown below.: |
|
|
| |
If it becomes necessary to add
new records to the data source, the new records must be added by
inserting the necessary number of rows above the red row and then
entering the data into those rows. |
| Naming the Merge Data and the
Chart Data |
| |
All of the
data on the "Merge Data" Sheet must be selected, including both the row
with the field names and the row that is coloured red and then by going
to the Formula tab of the ribbon and then by clicking on the Define Name
button in the Defined Names section of the Ribbon the name "MergeData"
must be assigned to the selected range.
Similarly,
the cells (including the field names and the red coloured cells) in the
columns that contain the data that is to be used for the chart for each
record must be selected and the name "XLChartData" must be assigned to
the selected range. |
| Setting up the Chart |
| |
Starting in
the second cell of the first row of the "Chart" worksheet (B1), enter
the titles that are to be used in the legend of the chart.
In cell in
the second row of the first column (A2), enter the word "Chart Data" and
in the cell in the third row of the first column (A3), enter the word "XLDataRow"
Select the
cell in the third row of the second column (B3) and via the Formula
tab>Define Name dialog, assign the name "XLDataRow" to that cell. Enter
the number 1 in that cell.
In the cell
in the second row of the second column (B2) , enter the following
formula:
=INDEX(XLChartData,XLDataRow+1,1)
In the next
cell in the second row (C2), enter the following formula:
=INDEX(XLChartData,XLDataRow+1, 2)
In each of
the other cells in the second row under the cells containing the legend
titles, enter a similar formula, incrementing the final number formula
by 1 each time.
The cells
into which the above formulae have been entered should now show the data
for the chart for the first record in your data source. |
| Creating the Chart |
| |
Select the
cells containing the formulae and then insert the required chart into
the "Chart" worksheet and format it in the manner in which you want it
to appear in your merged documents.
The Chart
worksheet should now look something like the following, depending upon
the type of chart that you have used. |
|
|
| |
The data
source workbook can now be saved and closed.
SETTING UP THE MAIL
MERGE MAIN DOCUMENT
The mail
merge main document must be of the Letters type and must have the data
from the "Merge Data" sheet of the workbook attached to it. A sample
document and data source (shown in the example below) is supplied with
the add-in.
Enter the
required text and merge fields into the document in the required and
layout.
In the
location where the chart is required to appear, a suitably dimensioned
cell of a table must be inserted. (The cell can be one that is contained
in a table that is included in the document for other purposes if
required)
Using the
Select>Cell facility of the Table Tools Layout tab of the ribbon, select
the cell in which the chart is to be displayed and then from the Insert
tab of the ribbon, select Bookmarks and assign the bookmark name "Chart"
to the selected cell
With the
display of bookmarks turned on, the cell in which the charts will be
displayed should appear as shown below: |
|
|
| |
When the
mail merge main document setup has been completed, save the document so
that it can be recalled if necessary.
Executing the "MERGE"
With the
mail merge main document as the active document, click on the "Chart
Merge" button in the "Finish" section of the "Mailings" tab of the
ribbon.
When that
is done, a dialog will open in which initially there is a combo box from
which you can choose the destination of the merge by selecting one of
the options
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.
|
|
|
| |
When the
necessary additional information has been selected/entered into the
dialog, the Continue button will become enabled. On clicking on that
button, if relevant, a dialog will appear via which the user can select
or create the folder into which the files created by the utility will be
saved. If the user does not select or create a folder, the files will be
saved in the default document folder. Once that has been done, the
process of "merging" the documents will proceed. |
|
|
| |
|
| |
|
