|
| Add-in to merge letters to separate files. |
| |
Fellow Word
MVP Doug Robbins, who produced most of the macro code on this page has
been working on a method of intercepting Word's built-in 'Merge to a New
Document' option, to merge a form letter merge document directly to individual Word documents. The
latest version of this code is available for download as a
Word add-in template. Extract the MMtoDocsRevnn.DOT template
(where nn is the latest revision number) to the Word startup
folder - its location defined in Word at Tools > Options > File
Locations > Startup. |
| Note 1: |
This add-in is intended to work with Word from version 2002 to 2007. For
earlier Word versions (97 & 2000) the macros included later on this page
should provide an alternative means of splitting merge documents.
++ If updating an earlier version of
the add-in, remove the older version before starting Word! ++ |
|
Note 2: |
If you use
Word as an editor for Outlook, and with Outlook 2007 where you don't
have the choice, you must start Word before Outlook,
otherwise the autoexec macro in the add-in will not run. Similarly Word
must be started from the Word icon in the Windows start menu, and not by
clicking a document or template in Windows Explorer, for the same
reason. |
|
|
The add-in
is designed to save a letter merge document to separate document files
for each record. It does this by creating a new document for each
record. This works best when the new documents are created from the same
template as the merge document, and includes an option to select the
template from which the document was created. If for some reason, that
template is not available, then the function creates documents using the
normal template.
In some
circumstances, this will result in documents that bear only a passing
resemblance to the merge document. To minimise that possibility, the
add-in also includes a macro that will create a template from the merge
document and a copy of that merge document with the new template
attached, which should be used to run the merge in place of the original
merge document. The original merge document is unchanged.
This
function is activated from a toolbar button. The toolbar should be
displayed the first time you run Word with the add-in present.
In Word
2007 the toolbar will be displayed from the Add-Ins tab on the ribbon.

In Word
2002/3 it will be displayed in the document space. If you don't require
the function you can close the toolbar, by clicking the X on the title
bar of the toolbar

When run,
the macro displays the following dialog:

If there is
no document loaded or the document loaded is a template, you will get an
error message and the application will quit.
  |
| Running the Split Merge function |
| |
The add-in
will intercept the command to merge to a new document and provides
an additional option to merge to separate document files as follows: |
| Word 2002/3 |
| |
Prepare your
merge source document in the usual manner and then click the 'Merge to
New Document' button on the mail merge toolbar (or select 'Edit
individual letters' from the Mail Merge task pane) |
|

or

|
| Word 2007 |
|

or if following the Step by Step Mail
Merge Wizard


|
| |
Whichever
Word version is employed, you are then
presented with the usual means of choosing which records to merge.
Complete that and click 'OK'

The macro
code then takes over to offer the choice of merging to separate
documents or continuing with a merge to a single document.

If you choose
separate documents, you are offered the list of available fields from
your data file (here shown from Outlook). If the field contains the path
to the folder in which you wish the files to be saved, check the
checkbox. If not, enter the path in the dialog box where indicated. The
macro retains this information for next time in a file called
Settings.txt which is saved in the root of the C: drive.

If a record
does not have an entry in the field chosen above, a warning message is
displayed which shows the content of the record in question and provides
the option to name the document associated with this record or use the
default name NoNameNumber#
The add-in
creates a series of new documents. It works best if these new documents
are created using the template from which the merge document was
created. If that template is not available, the new documents are
created using the normal template. You can, if you prefer, use the
normal template to create the documents, by un-checking the check box
shown checked in the illustration above, or run the separate macro from
the toolbar, as detailed earlier on this page, to create a version of
the document c/w attached matching template.

If you click
'Yes' in the above dialog, the following screen will take your filename
input.
There is no
need to add .DOC to your choice of filename.

For the
purpose of demonstration I used the following data file, which has a
single field 'Name' with one missing and one duplicated filename.

This produces
the following files. Note the results for the duplicated and missing
files.

|
| Merge to Individual PDF
files |
| |
The Word add-in described
above also provides the option to create PDF files in conjunction with
Acrobat 6, 7 and 8 (and possibly other PDF creating tools). In order to do
so, the Adobe 'printer' driver called Adobe PDF must be present in the
list of available printers. |
| Note: |
Although
not tested, it may be possible to use other types of PDF creating
'printer' driver with this add-in, provided the 'printer' is renamed
Adobe PDF and it is possible to set the same parameters as listed below.
If anyone tests this with alternative products and finds that it works,
please let me know via the feedback link on
the home page. |
|
|

Before executing
the merge to a new document, select the Adobe PDF printer in the
above list (from Control panel > printers and faxes). Right click and
select Properties and from the dialog box shown below click 'Printing
Preferences'

Uncheck the "View Adobe PDF results" and "Prompt for Adobe PDF filename"
items.
Acrobat 6

Acrobat 7

Acrobat 8

Execute the mailmerge to a
new document and click on "Yes" when you are asked if you want to create
separate files for each document (as in the previous section). Select the field in the datasource
that will supply the filenames and check the box 'Do you want to create
PDF files' in the dialog box shown below

When you click on Continue, as well as having the separate document files saved in the
directory that you had nominated, a .pdf will be created in the My
Documents folder with the name of the .pdf being supplied from the
datasource. If you prefer
to save the PDFs in a different folder you need to create a new 'Port'
for the printer driver and set that as the active port. From Windows
Control panel > Printers and Faxes. Right click the Adobe PDF driver and
select properties from the sub menu.

From the properties menu, select 'Ports'

Then 'Add Port'. The required port type is Adobe PDF Port.

Click New Port and you can choose an output folder for the PDF files.
 |
| Print individual merge letters from a
merged document - doing it the old way. |
|
If you create a merge letter and merge to a
new document without accepting the option to split the merge, you get one long document with each of the individual
'letters' separated by a section break.
It is simple enough to print an individual 'letter' or
range of letters by addressing the sections in the print dialog as in the
illustration below |
|
 |
| Split the single merged document into separate
letters. |
|
Having merged
to a single document, it is still possible to split to
separate documents, with the use of a macro. To this end Doug
Robbins also came up with the following, to which I have taken the
liberty of making a couple of small changes.
The
macro splits the document and files each sub document into the indicated
path - shown here in blue. The files are named by date and sequence
number, with the date format from the mask - also shown in blue. Both
these variables can easily be changed to reflect personal preferences -
nor should it be too difficult for those with vba programming skills and
inclination to modify the code to prompt for a name and/or path.
Sub
Splitter()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by a
' mailmerge as a separate file.
' With minor modifications by Graham Mayor 10-02-03
Dim mask As String
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
mask = "ddMMyy"
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
DocName = "D:\My Documents\Temp\Workgroup\"
& Format(Date, mask) _
& " " & LTrim$(Str$(Counter))
& ".doc"
ActiveDocument.Sections.First.Range.Cut
Documents.add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=DocName, FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Wend
End Sub
As an alternative, the following macro
provides the opportunity to provide the fixed portion of the filename
and to change the path of the saved files:
Sub SplitMerge()
' splitter Macro
' Macro created 16-08-98 by Doug Robbins to save each letter created by
a
' mailmerge as a separate file.
' with modifications by Graham Mayor 16-06-03 & 08-10-04
Dim Title As String
Dim Default As String
Dim MyText As String
Dim MyName As Variant
Dim MyPath As String
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
Default = "Merged"
MyText = "Enter a filename. Long filenames may be used."
Title = "File Name"
MyName = InputBox(MyText, Title, Default)
If MyName = ""
Then
End
End If
Default = "D:\My Documents\Test\"
Title = "Path"
MyText = "Enter path"
MyPath = InputBox(MyText, Title, Default)
If MyPath = ""
Then
End
End If
While Counter < Letters
Application.ScreenUpdating = False
Docname = MyPath & LTrim$(Str$(Counter)) & " " & MyName
& ".doc"
ActiveDocument.Sections.First.Range.Cut
Documents.Add
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
ActiveDocument.SaveAs FileName:=Docname, FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub
|
| Note: |
If you
are unsure how to use the above code, see
how to install macros |
| Patience! |
|
Note that as a merge document
may contain hundreds of letters, this macro can take a while to run, as each
document has to be opened and saved, but you should end up with a folder
full of files as shown below (derived from the first code example): |
|
 |
| Naming the file from the data source |
| |
This is
easier said than done, as once the document is merged the data file no
longer forms part of the merge letters. There are a couple of approaches
that may be considered, but the one I favour requires the field to be
used as the filenames to be added to the top of the merge letter on a
line of their own. When merged, the fieldnames will be those first lines
of the merged letters. The macro then removes the lines to restore each
merge letter to its required format during the splitting process.
The only
proviso when choosing filename fields is to choose a combination that
will produce a unique result. If duplicate filenames occur, the macro as
written will simply overwrite the saved file with the new file of the
same name. |
|
 |
| |
The filename
fields above are inserted in red for clarity - the colour applied does
not affect the result. Following the merge (below), the fields are
translated into a 'filename'. |
|
 |
|
|
When the
macro is run, the filename fields are stripped out leaving the merge
letters. |
|
 |
| |
Sub SplitMergeLetter()
' splitter Macro modified to save individual
letters with
' information from
data source. The filename data must be added to
' the top of the merge
letter - see web article.
Selection.EndKey Unit:=wdStory
Letters = Selection.Information(wdActiveEndSectionNumber)
Selection.HomeKey Unit:=wdStory
Counter = 1
While Counter < Letters
Application.ScreenUpdating = False
With Selection
.HomeKey Unit:=wdStory
.EndKey Unit:=wdLine, Extend:=wdExtend
.MoveLeft Unit:=wdCharacter, Count:=1, Extend:=wdExtend
End With
sName = Selection
Docname = "D:\My Documents\Test\Merge\" & sName & ".doc"
ActiveDocument.Sections.First.Range.Cut
Documents.Add
With Selection
.Paste
.HomeKey Unit:=wdStory
.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
.Delete
End With
ActiveDocument.SaveAs FileName:=Docname, _
FileFormat:=wdFormatDocument
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
End Sub |
| Note: |
IMPORTANT!! All the macros featured on this page (and the add-in) create
their new split documents by using the default normal.dot template as a
basis for the documents. If the merge source document has been
created from a different template, with different margins or page size,
the resulting split documents are not going to match the layout of the
merge document. To overcome this issue, create an empty template, by
deleting the content of the merge source document and save it as a
template. Locate the line:
Documents.Add
and address
the template as follows:
Documents.Add Template:="TemplateName.dot"
If the template in
question is not stored in the default template location you must add the
path also
Documents.Add Template:="c:\template path\TemplateName.dot"
|