Individual merge letters

Many people access the material from this web site daily. Most just take 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 would help to ensure the continued availability of this resource. Click the appropriate button above to access PayPal.

Word add-in to merge letters to separate files

The popular add-in has undergone many revisions, with a major re-write for version 2.0. Please keep an eye on the What's New page for new releases.

The add-in is provided in two versions: a dot format template intended to work with Word from version 2002 and 2003, and a dotm format template for Word versions 2007 and Word 2010 (32 and 64 bit versions). There are download links before the macros listings and at the end of the page, but please read the instructions first.

For earlier Word versions (97 & 2000) the macros included later on this page should provide an alternative means of splitting merge documents.

Extract the relevant template from the zip file to the Word startup folder, which by default can be found (in English language versions of Windows) by typing %appdata%\Microsoft\Word\STARTUP in the address bar of your Windows Explorer application or browser. If you don't have write access to this folder, ask your IT support to install it for you.

Extract ONLY the required template.

If updating from an earlier version of the add-in, remove the older version before starting Word!

It has been reported that while Word can handle a variety of merge data sources, CSV format files can be problematic. If you are using such a data source, and experience problems, converting it to Excel could be benefical.

If you experience a problem with the add-in, do ensure that you have the latest version before reporting the problem. The add-in is under regular development.

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

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 included is 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.

If the split documents do not appear to match the original merge document layout, run this macro and then re-run the merge.

It has been reported that with the previous version there may be a conflict if you have installed MathType by Design Science. If that still proves to be the case, temporarily disabling MathType should enable the add-in to be used.

During a long merge Word may display 'Not responding' in the title bar. This does not necessarily mean that there is a problem. If you can hear disc activity and/or the disc access light is flashing on the PC, give the merge time to complete. Word should recover normally when the merge is finished.

Command buttons are added to the Mailings tab on the ribbon of Word 2007/2010 and to a custom toolbar in Word 2003. In Word 2003 the main command is also added to the Mail Merge toolbar and to the Tools menu.

Word 2003 version

When run, the macro checks for fatal errors and will produce one of the self explanatory messages shown below, should such an error be apparent.

If the document's attached template is already SplitMerge.dotm then you will get the following error message and the application will quit:

If all is well you will see the following dialog. Click the '?' icon to return to this page.:

Running the Split Merge function

The first time the add-in is run, and each time until the check box on the dialog is checked, the following dialog is presented:

The next dialog (the first if the disclaimer is cancelled) is the option to select which records you wish to merge.

Clicking 'Select Records' displays the Word built-in Edit Recipient List dialog

The add-in version offers the user the choice to save the split files in one from a variety of formats.

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 Word 2007/2010 version now provides an additional option to save the merged documents to a Sharepoint location. If that option is checked, the browse button is not displayed and the label wording above the check box changes. Users may now type or paste a Sharepoint path into the dialog.

From version 2.3 the main form has a tabbed section which provides the additional option to add fixed text to the filenames, either as a prefix or as a suffix (before the filename extension obviously). This text and the location are remembered by the application, so remember to clear them if merging a different document that does not require them. Where such text is used it must obey file naming conventions. Illegal filename characters will be replaced with the underscore character.

From version 2.5 I have responded to popular request by allowing a second field to be included in the filename. This second field can be placed before or after the initially selected filename field and can be used in conjunction with the text string from the previous illustration, which can also be placed before or after the combined name fields.

As the field information is retained between uses of the add-in, I have added an empty 'field name' to allow the second field to be eliminated from the filename. To omit text strings from the filename, simply delete the content of the test string field. Where no second field/text string is selected, the option buttons associated with it perform no function.

No doubt someone will now come up with a suggestion to allow more than two fields or more than one text string. If you have so complex a requirement, use the ability to name the file from a derived field in the data source. Thus just about any legitimate filename structure can be achieved.

If the folder text box is left empty, the add-in will save the document(s) into the default document folder, however as the first time the macro is run this text box will be empty, I have added a message box to warn the user and provide the opportunity to reconsider. If you click 'Yes', then the second dialog alongside the illustration below appears:

If the Sharepoint folder option is selected, the Sharepoint path MUST be entered (preferably pasted to avoid mistakes) into the dialog.

The user will be prompted for the Sharepoint User Name and Password. If these are entered incorrectly, or unknown, the application will crash with an error message no 4198.

The 'Donate £€$' button presents the following dialog. The '?' button returns to this web page

You may also select the folder to store the merged documents by clicking the 'Browse' button, which offers the following dialog. The browse button is not available for Sharepoint folder selection.

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.

Illegal, Missing and duplicated name filename field values

Earlier versions of the add-in would prompt for missing filenames. This has proved a nightmare to code with the changes I wanted to effect, and was also rather irritating in its previous guise, where several records may have had illegal or missing filenames, so I have simplified the function. Now when the filename field is empty or there is an illegal character in the field (see list below), a dummy filename name is substituted. This name is in the format - "NoName Record Number", where Number is the number of the merged record.

Where fieldnames have duplicated content, the fieldname of the first matching record is named in the format "Filename" whereas the subsequent matching records have an incremented number appended e,g, "Filename(1)", "Filename(2)" etc.

The illegal characters that the macro will not accept are, :- / \ : * ?" < > |

The macro will also reject the name if the first character is a period (full stop).

Merge to Individual PDF files

The Word add-in described above also provides the option to create PDF files.

With Word 2003, PDF file creation requires the availability of the Adobe PDF driver from Adobe Acrobat

In early versions of Word 2007 the PDF plug-in was an optional extra. The add-in will only offer the Office 2007 PDF plug-in or Adobe PDF driver if they are available. The optional PDF plug-in will be available if Word 2007 is updated to SP2 as this removed the plug-in option and made it part of the application.

In Word 2010 The PDF plug-in should always be available, and the function to use Adobe Acrobat as an alternative, if present, is retained.

The macro detects whether the plug-in is installed by looking for EXP_PDF.DLL file in the folder %programfiles%\Common Files\Microsoft Shared\OFFICE14 (OFFICE12 for Word 2007), which is the active part of the plug in. If it is not installed, the add-in then checks whether the Acrobat printer driver Adobe PDF is installed, and if available will print to that. If neither is available a warning message is presented and the merge is made only to the choice of document format.

Adobe Acrobat

The Acrobat versions compatible with Word 2007/2010 provide their own merge function which has greater functionality than is available from the Adobe PDF 'printer' driver or the Microsoft Office Plug-in, though not the ability to name the PDF files from the data source. If you have Acrobat installed, investigate this if you need to merge to PDF.

The Acrobat add-in also offers the option to merge PDF to e-mail attachments which the individual merge letters add-in does not include. However see also Email Merge With Attachments.

If you wish to employ the Adobe driver with this add-in, before executing the merge to a new document, select the Adobe PDF printer (from Control panel > Devices and Printers). 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.

The illustration above is from Acrobat 10 (earlier versions of Acrobat have similar options).

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.

It has been observed that the processing demands of very complex merge documents, can result in problems, even to the extent of the add-in crashing Word completely. In such cases, and provided the data source is an Excel worksheet that meets the standards required by the add-in, you could try the Many to One add-in. This add-in does not use mail merge, but processes each record as a separate task. The final result can however be much the same.

There is a note on the Many to One page that explains how to use it for this task.

- Now download the add-in

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 using the add-in 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. The files are named by date and sequence number, with the date format from the mask. 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()
' Based on a Macro created 16-08-98 by Doug Robbins
' to save each letter created by a
' mailmerge as a separate file.
Dim Mask As String
Dim Letters As Long
Dim Counter As Long
Dim DocName As String
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
oDoc.Save
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"
oDoc.Sections.First.Range.Cut
Set oNewDoc = Documents.add
'Documents are based on the Normal template
'To use an alternative template follow the link.
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
oNewDoc.SaveAs FileName:=DocName, _
FileFormat:=wdFormatDocument, _
AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Wend
oDoc.Close wdDoNotSaveChanges
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()
' 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
Dim Letters As String
Dim Counter As Long
Dim docName As String
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
oDoc.Save
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
Default = "D:\My Documents\Test\"
Title = "Path"
MyText = "Enter path"
MyPath = InputBox(MyText, Title, Default)
If MyPath = "" Then End
While Counter < Letters
Application.ScreenUpdating = False
docName = MyPath & MyName & " " & _
LTrim$(Str$(Counter)) & ".doc"
oDoc.Sections.First.Range.Cut
Set oNewDoc = Documents.Add
'Documents are based on the Normal template
'To use an alternative template follow the link.
With Selection
.Paste
.EndKey Unit:=wdStory
.MoveLeft Unit:=wdCharacter, Count:=1
.Delete Unit:=wdCharacter, Count:=1
End With
oNewDoc.SaveAs FileName:=docName, _
FileFormat:=wdFormatDocument, _
AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
oDoc.Close wdDoNotSaveChanges
End Sub

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 its 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.
Dim sName As String
Dim docName As String
Dim Letters As String
Dim Counter As Long
Dim oDoc As Document
Dim oNewDoc As Document
Set oDoc = ActiveDocument
oDoc.Save
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"
oDoc.Sections.First.Range.Cut
Set oNewDoc = Documents.Add
'Documents are based on the Normal template
'To use an alternative template follow the link.
With Selection
.Paste
.HomeKey Unit:=wdStory
.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
.Delete
End With
oNewDoc.SaveAs FileName:=docName, _
FileFormat:=wdFormatDocument, _
AddToRecentFiles:=False
ActiveWindow.Close
Counter = Counter + 1
Application.ScreenUpdating = True
Wend
oDoc.Close wdDoNotSaveChanges
End Sub

Split a merged document to the printer with each 'letter' treated as a separate print task

Where a merged document consists of several elements e.g. the first page of each merged letter should be printed on letter headed paper from a different printer tray, or the printer provides a stapling function for each 'letter', a frequently encountered problem is that the printer loses track of the tray assignments or in the case of the stapled document, the whole merge is treated as a single document. To overcome this problem, fellow MVP Doug Robbins, came up with a macro that treats each letter as a separate print task.

To use this macro, merge to a new document then use the macro to print that document.

Sub SplitMergeLetterToPrinter()
' Macro created 16-08-98 by Doug Robbins to print each letter created by a
' mailmerge as a separate file.
Dim Letters as Long
Dim Counter as Long
Letters = ActiveDocument.Sections.Count
counter = 1
While counter < Letters
ActiveDocument.PrintOut Background:=False, _
Range:=wdPrintFromTo, _
From:="s" & format(counter), To:="s" & format(counter)
counter = counter + 1
Wend
End Sub

Create a template for the merge splitting macros

IMPORTANT!! Several of the macros featured on this page create their new split documents by using the default Normal 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, you could create an empty template, by deleting the content of the merge source document and save it as a template, then base the split documents on that template.

The add-in includes a function to create a template from the document upon which the following macro is based.

The following macro will create a template from the merged document called splitmerge.dot(x) which it will store in the default user template location.

To use the splitmerge.dot(x) template in the examples above, locate the line

Documents.Add

and replace it with

If Val(Application.Version) < 12 Then

Documents.Add Options.DefaultFilePath(Path:=wdUserTemplatesPath) _

& "\splitmerge.dot"

Else

Documents.Add Options.DefaultFilePath(Path:=wdUserTemplatesPath) _

& "\splitmerge.dotx"

End If

Sub CreateSplitMergeTemplate()
Dim sTempPath As String
Dim sQuery As String
Dim sRestore As String
Dim sATemp As String
Dim oSection As Section
Dim oStory As Range
Dim oMergeDoc As Document
If Documents.Count = 0 Then
MsgBox "No document present!" & vbCr & _
"Open the merged document and run this macro again", _
vbCritical, "Merge Template Creator"
Exit Sub
End If
Set oMergeDoc = ActiveDocument
If InStr(1, oMergeDoc, ".dot") Then
MsgBox "Active document is a template!" & vbCr & _
"Open the merged document and run this macro again", _
vbCritical, "Merge Template Creator"
Exit Sub
End If
sTempPath = Options.DefaultFilePath(Path:=wdUserTemplatesPath) & Chr(92)
With oMergeDoc
If Val(Application.Version) = 12 Then
.SaveAs FileName:="SplitMerge.docx", _
FileFormat:=wdFormatXMLDocument
Else
.SaveAs FileName:="SplitMerge.doc", _
FileFormat:=wdFormatDocument
End If
sRestore = .FullName
For Each oSection In .Sections
oSection.Range.Delete
Next oSection
For Each oStory In .StoryRanges
oStory.Delete
If oStory.StoryType <> wdMainTextStory Then
While Not (oStory.NextStoryRange Is Nothing)
Set oStory = oStory.NextStoryRange
oStory.Delete
Wend
End If
Next oStory
Set oStory = Nothing
With .PageSetup
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
End With
If Val(Application.Version) = 12 Then
.SaveAs FileName:=sTempPath & "SplitMerge.dotx", _
FileFormat:=wdFormatXMLTemplate
Else
.SaveAs FileName:=sTempPath & "SplitMerge.dot", _
FileFormat:=wdFormatTemplate
End If
sATemp = .FullName
.Close SaveChanges:=wdDoNotSaveChanges
End With
Documents.Open sRestore
With ActiveDocument
.AttachedTemplate = sATemp
.Save
End With
End Sub

- Download the add-in