Use an XML file as a data source

Open the XML file in Excel 2002 or Excel 2003. Edit it as necessary. Save the result as a regular Excel spreadsheet and use this as the mail merge data source for any version of Word.

Background Information

Create executable hyperlinks using mail merge

Word's mail merge doesn't really support creating hyperlinks. If you need hyperlinks in a merge result, especially an email result, then it is probably better to look into other products than Word. You should find a list at slipstick.com.

The only way Word can create a hyperlink is using a Hyperlink field. That means that you have to pass the TEXT (URL) through the mail merge, and nest the merge field into a Hyperlink field. Further, this has to be done as the last step, and the mail merge executed immediately, to a NEW document, before hiding the merge field codes, then updating the fields.

Here are the steps in detail:

Suppress "Cannot find datasource" message opening merge document

A common obstacle to automating mail merge is Word's prompt when opening a main merge document if the data source cannot be found because it has been moved or is not present. For all versions of Word up through Word97, your only recourse is to make sure the main merge document is saved without a linked data source. Manually, this is done in the first step of the Mail Merge Helper, by selecting "Restore to Normal Word document" as the type of merge (this only unlinks the data source and will not affect the merge fields).

In Word2000 and later, setting DisplayAlerts to show no alerts will suppress this dialog box:

Application.DisplayAlerts = wdAlertsNone
Documents.Open FileName:="c:\my documents\testmerg.doc"
Application.DisplayAlerts = wdAlertsAll

Save each merged letter as a separate file

Normally, the mail merge result to a new document is one long file which can be edited and printed. Using the Master Document feature, it's possible to save each as a separate file. In the mail merge result document, each record's letter is created in its own section, so one can select each section, turn it into a sub-document, open the sub-document and save it as a separate file.

The only preparation you need to make in the main merge document is to select the first paragraph, go to Format/Paragraph and set the Outline Level to 1 (one). The Master Document feature needs this in order to create the sub-document.

The following sample VBA code can help automate this process.

Sub SaveRecsAsFiles
' Convert all sections to Subdocs
AllSectionsToSubDoc ActiveDocument
'Save each Subdoc as a separate file
SaveAllSubDocs ActiveDocument
End Sub

Sub AllSectionsToSubDoc(ByRef doc As Word.Document)
Dim secCounter As Long
Dim NrSecs As Long
NrSecs = doc.Sections.Count
'Start from the end because creating
'Subdocs inserts additional sections
For secCounter = NrSecs - 1 To 1 Step -1
doc.Subdocuments.AddFromRange _
  doc.Sections(secCounter).Range
Next secCounter
End Sub

Sub SaveAllSubDocs(ByRef doc As Word.Document)
Dim subdoc As Word.Subdocument
Dim newdoc As Word.Document
Dim docCounter As Long
docCounter = 1
'Must be in MasterView to work with
'Subdocs as separate files
doc.ActiveWindow.View = wdMasterView
For Each subdoc In doc.Subdocuments
Set newdoc = subdoc.Open
'Remove NextPage section breaks
'originating from mailmerge
RemoveAllSectionBreaks newdoc
With newdoc
.SaveAs FileName:="MergeResult" & CStr(docCounter)
.Close
End With
docCounter = docCounter + 1
Next subdoc
End Sub

Sub RemoveAllSectionBreaks(doc As Word.Document)
With doc.Range.Find
.ClearFormatting
.Text = "^b"
With .Replacement
.ClearFormatting
.Text = ""
End With
.Execute Replace:=wdReplaceAll
End With
End Sub

Field names from Excel have an underscore appended

In more recent versions of Excel, you may see underscores directly following field names; for example, instead of TotalAmount you get TotalAmount_. This will happen when:

  1. You link to the data source using DDE
  2. The field heading (first cell in the Excel column, containing the column name) is formatted as "Accounting". (Probably, the entire column was selected and this number format applied.)

The solution is to select this cell in Excel and apply the format "General" or "Text" (Format/Cell/Number).

Put barcodes in a mail merge form letter

While Word's mail merge feature up through Word 2000 makes it easy for you to include a barcode on a label or envelope, no facility is provided for doing the same in the address of a form letter. The easiest solution is to create a label or envelope mail merge main document with the same data source, then COPY the barcode and paste it into the form letter main merge document.

If you for some reason must create the Barcode field manually, be forewarned that the syntax must be entered exactly:

{ BARCODE "{Mergefield StreetAddress}¶
{Mergefield PostalCode}¶
"\u }

Where the symbol ¶ indicates a hard-return (press Enter at these positions). In the mail merge result the barcode field's code will display the literal text from the merge fields; for example:

{ BARCODE "1000 Main Street¶
10000-3022¶
"\u  }

The situation is a bit less clear in Word 2002, since the Setup dialog boxes for label and envelope merges have been done away with for the more "intuitive" interface of the Word document window. If you don't want to resort to building the Barcode field by hand, you can use the Mail merge Helper of previous versions, instead.


Format Word labels in the label setup window of the Mail Merge Helper (through Word 2000)

If you've been formatting every label on a label page one at a time, or wished you could format a single label to print and haven't known how, this is the tip for you:

Select the text you want to format, then right-click in the selection. Choose the "Font" or "Paragraph" short-cut menus and apply the formatting.

If you're using Word97 and want to change the paragraph indent setting watch out for this gotcha: in order for the setting of a single paragraph in the label to be recognized and applied, the indent must be larger than the default Word applies for the label. If you want a setting less than this, you must apply paragraph indent formatting to all the paragraphs in the label.


Show a complete list of merge fields (through Word 2000)

Sometimes, when you have a lot of fields in your datasource, you can't display all the fields in the list you can open from the mail merge toolbar. By pressing Alt+Shift+F you can display a dialog box that will allow you to scroll through all the fields available to the merge.

It's also possible, with a macro, to show a list of fields in a non-modal UserForm. This means you can view the list and insert fields and work in the document at the same time. This is only possible in Word 2000 and later.


Merge unavailable after using FIND in Word 97 and Word 2000

After you use the Find Record feature (binoculars on the mail merge toolbar) in Word97 or 2000 you can no longer view individual records or complete the merge. You may get an error message when you try to merge.

This is a bug in these two versions. You have to search for something using Find that returns an empty result (doesn't find any records). You can then view and merge normally.

The problem has been fixed in the user interface of Word 2002. But for some reason, the new implementation is broken when run from VBA.
 


Watch this spot
Return to top of this article

Return to Home Page