This article may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist. To maintain the flow of the article, we've left these URLs in the text, but disabled the links.

Bon Mot

Employing Word's Field and Formatting Capabilities

By Cindy Meister

In Part I of this series, we looked at the basics of setting up and running a Word mail merge. We became acquainted with the types of mail merge documents available, as well as with many of the VBA commands for the Word mail merge object model.

In this installment, I want to show you how to get mail merge to jump through hoops. We'll look at how to supplement the built-in mail merge feature, making it more versatile and powerful. This article will cover such topics as formatting, one-to-many relationships, and managing conditional, formatted text inclusions.

Insert field brackets { }	[Ctrl][F9]
Toggle field codes	[Alt][F9]
Force a field update	[F9]
Lock a field	[Ctrl][F11]
Unlock a field	[Shift][Ctrl][F11]
Field to plain text	[Shift][Ctrl][F9]

FIGURE 1: Keyboard commands for editing fields manually.

A Question of Formatting

A question that crops up with every new version of Word is what to do when the merged data in the mail merge result is in a different font or font size than is specified in the Main merge document. Microsoft claims that "This behavior occurs when the default Normal style is different from the font applied to your mail merge main document [from various Knowledge Base articles, most recently Q212349]." However, people who've experienced this problem claim that this is not always the case. Be that as it may, there is one sure-fire method for forcing the merged data to be formatted correctly: Make sure the MERGEFIELD field contains the switch \* CharFormat, for example:

{ MERGEFIELD LastName \* CharFormat }

As a developer, your goal is to make life as easy as possible for the user. If your application allows users to create their own Main merge documents to link with your data source, you don't want to require them to edit the field codes manually. And Microsoft does not, unfortunately, provide an option to include the \* CharFormat switch when a user inserts fields. (Although, disconcertingly, it will often put in the \* MergeFormat switch, thus compounding the problem. You should remove it wherever you find it.)

A bit of VBA code placed in the procedure that runs the mail merge can solve this handily, as shown in Listing One. In this example, a \* CharFormat switch is to be added to each merge field in the Main merge document, if one doesn't already exist. First, any \* MergeFormat switch present is removed, because its functionality will conflict with that of the \* CharFormat switch. The RemoveText function is written to take advantage of the Replace function (new in Office 2000) if Word 2000 is loaded; otherwise, the older method of combining the Instr, Left, Mid, and Len string functions is implemented. Care should be taken to ensure that one space separates each element of the field code.

As mentioned in Part I of this series, using a Word document as the data source - whether as a table or in character-delimited format - allows maximum flexibility in terms of what can be brought across the mail-merge link. For one thing, the data can be merged with its formatting; the only formatting not supported is superscript/subscript. The trick to allowing the formatting to "shine through" is to remove the MERGEFIELD keyword from the field, turning it into an implicit reference field. An implicit reference field causes Word to search the environment for something to which the first word in the field might refer, such as a bookmark or, in this case, the field name from the data source. It then displays the related information as it appears in its original state (with formatting). To make it easier for the user, another button can be added to the toolbar that will remove the MERGEFIELD keyword and any \* MergeFormat and \* CharFormat switches in a selected merge field: Sub RemoveMergeFieldKeyword (see Listing One). Once again, the RemoveText function does much of the work to get rid of both elements.

Note: Use explicit field names whenever possible, and treat implicit reference fields with caution. What Word decides they refer to may not be what you expect. For instance, in recent versions of Word, the currency setting in Windows is treated like a keyword, causing incorrect results in mathematical calculations that implicitly reference a bookmark of the same name. For example, instead of using:

{ = {DM} * {DollarExch} }

to multiply values from bookmarks named DM and DollarExch, use:

{ = {REF DM} * {REF DollarExch } }

No matter what data source you use, you can conditionally format the merged result using IF fields, as shown in FIGURE 2, where a credit amount greater than 150 is displayed in blue, one between 0 and 150 in black, and one less than or equal to 0 in red. Notice that, unlike If statements in Access or Excel, Word IF fields do not use argument separators. Instead, the True and False elements are designated by pairs of double quotes.

FIGURE 2: Control formatting during mail merge by: a) removing the MERGEFIELD keyword so that formatting in a Word data source is reflected; and b) using IF fields to display conditional formatting.

Include Elements Not in the Data Source

What if the data source isn't a Word file, but you want to include formatted text in the merge result? What if the conditional text is too long to be easily managed in the data source or in an IF field? What if you want to use the same text in more than one Main merge document? The answer is to use an INCLUDETEXT field to display another document's content in the merge result. If there are a number of pieces of related text, as might be the case when conditionally specifying the inclusion, they can all be saved in a single document and indicated with bookmarks. For example:

{ INCLUDETEXT "C:\\My Documents\\MergeTexts.doc"
  { MERGEFIELD LongText }}

(Note: This statement - and others in this article - has been "wrapped" to fit in magazine columns. The actual statements should be on one line.) The merge should run correctly as long as you pass only the document or bookmark name from the data source to the INCLUDETEXT field. However, if you need to bring over the entire path, you'll run into problems in Word 97 and 2000 due to a change in how fields are updated and interpreted. Word uses the backslash in fields to identify switches; double backslashes are required when Word should use a backslash literally, as in a file name. However, when Word 97 or 2000 receives a file path as part of a nested field (be it a MERGEFIELD, SET, DOCPROPERTY, or DOCVARIABLE field), only single backslashes are needed. So, while setting up the Main merge document, a field set like the following will display an error message if the data contains double backslashes, but will look fine if the path has single backslashes:

{ INCLUDETEXT "{ MERGEFIELD Bio }" }

After the merge is run to a new document and the fields are updated, however, one sees the error message "Error! Cannot open file." Looking at the field code reveals that the merge process has converted the MERGEFIELD field to literal text, so double backslashes are required:

{ INCLUDETEXT "E:\My Documents\CindyMeister" }

This is a catch-22 situation and requires a small yet significant work-around to provide an error-free environment in both the Main merge document and the merge result. Place the INCLUDETEXT field within an IF field (that always evaluates to True):

{ IF 1 = 1 "{ INCLUDETEXT "{ MERGEFIELD Bio }" }" }

This works because the IF field and all fields nested within are always converted to plain text during the merge.

A variation on this technique can be used to automatically generate introductory text for a catalog-type mail merge. As you're aware, all text in the Main merge document is repeated for every record in the data source. To have text that precedes the first record only, a way to test for the first record is required. The MERGEREC field provides this information:

{ IF { MERGEREC } = 1
  "{ INCLUDETEXT "C:\\My Documents\\Intro.doc" }" }

Similarly, you can incorporate pictures in your mail merge result. Word's MAILMERGE fields can only pick up pictures from a Word data source; they can't work with the long binary format that databases use to store pictures. However, if the graphics are stored as files, and the file name information is available from the data source, an INCLUDEPICTURE field can be used to display the graphics in the merge result. When using this technique, you must merge to a new document and update all its fields to see the correct graphic images:

{ INCLUDEPICTURE
  "E:\\My Documents\\{MERGEFIELD PicFileName}.gif" }

Bring It All Together

Let's consider a practical application of these techniques in a catalog-type mail merge to generate a directory or address list. FIGURE 3 shows part of a sample page created entirely using Word's formatting features and mail merge; no VBA was used to manipulate the result. (This sample is available for download; see end of article for details.) Among other things, you'll notice the introductory text is in one column spanning the entire page width, and the merge records are formatted in two columns. In addition, the record information does not break across columns or pages except in the "Bio" text.

FIGURE 3: Merge result combining various field and formatting techniques.

To begin, an INCLUDETEXT field links in the introductory text. At the end of this document, a continuous section break was inserted to maintain the column formatting (single column). The last paragraph is formatted with Space After to set the distance following the introduction.

If you ran the mail merge at this point, you'd see a Next Page instead of a continuous section break in the merge result. This is due to the Section Start setting in File | Page Format | Page Layout of the Main merge document; you need to change this to Continuous, as well.

The address information and the picture are inserted in a two-column table with a border across the top. The option to allow the row to break across pages is deactivated. Below that, another INCLUDETEXT field picks up the Bio information for each merge record. The first paragraph of the Bio text is formatted Keep with Next so that it never stands alone at the bottom of a column. (To keep an entire paragraph in the same column, use Keep lines together.) Space After is applied to the last paragraph to give it some distance from the following record.

Controlling page and column breaks in Word is most effectively accomplished using the built-in formatting features. Manually-set breaks are unreliable because paragraph and page layout depend mainly on the printer driver installed on each machine. Looping through the lines of text in a document to determine where the breaks should be inserted is inefficient, at best. Once you understand the power of Word's paragraph-formatting features, you'll require very little VBA intervention to produce attractively laid-out reports.

Index-type Headings for Lists

It's easier to consult a long list if it has alphabetic headings, as in an index (see FIGURE 4). Implementing this in Word for a mail merge listing is a bit tricky and not very flexible, because it's not possible to interrupt the merge process - you must use fields. The required field set is a bit complex:

{ IF { Compare "{ MERGEFIELD LastName }" = "{ = 
  { REF Counter } \* Alphabetic }*" } =
  "0" "{ SET Counter "{ = { Counter } + 1 }" }{ =
  { REF Counter  \* CharFormat } \* Alphabetic }" }

FIGURE 4: Index-type headings for a directory or address list can be generated using fields in a mail merge.

Counter is a bookmark that is initiated by a SET field. As long as the alphabetic value of the counter is the same as the first letter of the record's LastName merge field, nothing happens. As soon as they differ, Counter is incremented by 1 and its alphabetic equivalent is displayed in the mail merge. Format the sequence that's displayed as you want the headings to appear in the merge result (bold, underlined, different font size, etc.):

{ = { REF Counter \* CharFormat } \* Alphabetic } 

The COMPARE field is virtually unknown, but quite useful. Using COMPARE is the closest you can come to the Left function, because Word doesn't provide fields that correspond to VBA string functions. COMPARE has the basic syntax:

{ COMPARE Expression1 Operator Expression2 }

If Expression2 is a string (contained in double quotes), it may contain the wildcards "?" or "*" to stand for a single character or multiple characters. This way, one can check whether the first character of a MERGEFIELD corresponds to the alphabetic representation of a numerical counter, plus any number of characters. For more information on the COMPARE field, consult Word's Help file.

A SET field must be inserted at the beginning of the Main merge document, setting the value of Counter to -1. The fields must then be updated, and the SET field deleted, before the merge is executed. (Otherwise, the value would never increment because SET would execute for each record.) You can include code for this in the procedure that executes the mail merge (refer to InitiateHeadingsCounter in Listing One). (This method works only if there is at least one entry for each letter of the alphabet, because there isn't a way to skip letters.)

One-to-many Relationships

Unlike WordPerfect, Word does not provide a way to include data from other related tables during a mail merge so that you can display lists of information related to each merge record. There are four basic approaches you can use to work around this problem:

1. Forget mail merge and create the result documents using Automation (the topic of the next article in this series).
2. Concatenate all the data into a string, and pass it as part of a mail merge field.
3. Use complex field sets, as described in Knowledge Base article Q181730.
4. If the data may be displayed in table format, use a DATABASE field.

A DATABASE field links to an external data source the same way mail merge does (see Part I of this series). The easiest way to build the field is to use the Insert Database command from the Database toolbar. The Query Options allow you to select the field on which you want to filter the records (the link for the one-to-many relationship). Make sure to enter a value you know is in the data table. If it's installed, Word will ask whether you want to use MSQuery. Unfortunately, recent versions of Word don't work reliably with MSQuery, so it may be better to turn the offer down and use Word's internal Query Options, instead.

Formatting a linked DATABASE is limited to the selections in the Table Autoformats. Judicious use of the options available provides quite a bit of scope, but if this isn't enough, you can format the tables in the merge result using VBA.

In the last step, be sure to insert the data with a link (as a field). The example at the top of FIGURE 5 links to the Orders table of the Northwind database for the CustomerID ALFKI. The DATABASE field can be filtered for the current mail merge record by selecting the "Where" value (ALFKI) and inserting the corresponding MERGEFIELD (CustomerID) in its place, as illustrated at the bottom of FIGURE 5.

FIGURE 5: The top example shows a DATABASE field as inserted using the tool on the Database toolbar. The bottom shows the field modified to display selected columns and only data matching the value of the current mail merge record's CustomerID value.

You may also wish to edit the list of fields you want included in the table. Note the special apostrophe character that Word uses to denote strings in the SQL section. The result for the sample document is shown in FIGURE 6.

FIGURE 6: Field result of mail merge with DATABASE field linked to the merge record.

A Separate File for Each Merge Record

Occasionally, there's a need to save each merge record's form letter as a separate file. There are two ways you can approach this. The first is to create a new document from a Main merge template, move to a record, unlink the fields, turn the Main merge document into a regular Word document (thus unlinking the data source), save, and close the file. Repeat for each record.

The second method, which I will discuss in more detail, is to perform the mail merge as usual, then extract the letters using the Master Document feature to create a separate Subdocument for each section (in a form letter mail merge, every record's result is in a separate section). You'll find sample code for this in Listing Two.

After the merge has been executed, the SaveRecsAsFiles procedure is called. The merge result document is passed as a parameter. The next step is to convert each section in the mail merge result to a Subdocument (AllSectionsToSubDoc). It's necessary to start with the last section and work toward the top, because creating Subdocuments inserts additional section breaks in a document. To create Subdocuments from existing text, the first paragraph in the section must be formatted as Level 1 in the Outline Level box (select Format | Paragraph, then the Indents and Spacing tab); otherwise, an error occurs. Be sure to take this into account when setting up the Main merge document.

SaveAllSubDocs loops through the merge result document's Subdocuments collection, opening each Subdocument and removing the NextPage section break resulting from the mail merge process. Each file is then saved to disk under a unique name (the sample procedure uses a simple counter) and is closed. It's important to note that the merge result document window must be in Master Document view in order to open the Subdocuments as individual files.

Conclusion

This article addresses many questions about Word's mail merge that crop up regularly in support areas. Most of the solutions use VBA solely to improve user-friendliness; the real key is understanding Word's field and formatting capabilities. However, there are things that the mail merge feature cannot do, such as handle data from multiple data sources (one-to-many or many-to-many relationships) flexibly. The next part of this series will look at how to access data from outside sources and bring it into a Word document.

Cindy Meister has her own consulting business, INTER-Solutions, based in Switzerland. Prior to becoming independent she spent three years as a consultant/trainer for Deloitte & Touche in Zurich. Five years experience as head of administration in an international organization and a Bachelor of Science provide her with a broad background for understanding customer requirements. Cindy's fully bilingual (English and German), with customers in four countries (and counting) and is a regular contributor to the German edition of "Inside Word." Four years as a Microsoft MVP for MSWord support and as Sysop in the CompuServe MSWord forum have given her an in-depth knowledge of Office and Word. For general questions on Word and links to other useful sites, visit her Web site at http://homepage.swissonline.ch/cindymeister. You can reach her at mailto:cindymeister@swissonline.ch.

Begin Listing One - Run mail merge

Option Explicit
  
' Controls preparation of the merge: Runs the merge; 
' manipulates the merge result. 
Sub RunMerge()
   Dim MainDoc As Word.Document
   Dim fld As Word.Field
  
   Set MainDoc = ActiveDocument
  
   ' Add CharFormat switch to all merge fields, 
  ' and removes MergeFormat. 
  For Each fld In MainDoc.Fields
     If fld.Type = wdFieldMergeField Then _
      AddCharFormatSwitch fld
   Next
  
   ' Set the counter for Directory Index. 
  InitiateHeadingsCounter MainDoc
  
   With MainDoc
    .MailMerge.Destination = wdSendToNewDocument
    .MailMerge.Execute
   End With
  
   ' Update fields in merge result to display pictures. 
  Dim ResultDoc As Word.Document
   Set ResultDoc = ActiveDocument
  ResultDoc.Fields.Update
End Sub
  
Sub RemoveMergeFieldKeyword()
   Dim fldCodeText As String
  
   On Error GoTo Error_handler
  
  fldCodeText = Selection.Range.Fields(1).Code.Text
  fldCodeText = RemoveText(fldCodeText, "MERGEFIELD") 
  fldCodeText = RemoveText(fldCodeText, "\* MergeFormat") 
  fldCodeText = RemoveText(fldCodeText, "\* CharFormat") 
  Selection.Range.Fields(1).Code.Text = fldCodeText
  
   Exit Sub
  
Error_handler: 
   Select Case Err.Number
     Case 5941
       ' No field is in current selection. 
      MsgBox "Please select a merge field, then try again." 
     Case Else
      MsgBox Err.Number & vbCr & Err.Description
   End Select
  
End Sub
  
Function AddCharFormatSwitch(fld As Word.Field) As Boolean
   Dim fldCode As String
   Dim fFound As Boolean
  
  fFound = False
  fldCode = fld.Code.Text
   ' \* Mergeformat conflicts; remove if present. 
  If InStr(1, fldCode, "MergeFormat", _
           vbTextCompare) <> 0 Then
    fldCode = RemoveText(fldCode, "\* MergeFormat") 
   End If
   ' Make sure a space is present at end of current
  ' field code text. 
  If Right(fldCode, 1) <> " " Then _
    fldCode = fldCode & " " 
   ' Add \* CharFormat to end of field code text. 
  If InStr(1, fldCode, "CharFormat", _
           vbTextCompare) = 0 Then
    fld.Code.Text = fldCode & "\* CharFormat" 
    fFound = True
   End If
  AddCharFormatSwitch = fFound
  
End Function
  
Function RemoveText(SearchText As String, _
                    RemoveStr As String) As String
   Dim CleanedString As String
  
   ' Replace function only available from VBA6 onwards. 
  #If VBA6 Then
    CleanedString = Replace(SearchText, RemoveStr, _
                            "", , , vbTextCompare) 
  # Else
     Dim CharPos As Long
    CharPos = _
      InStr(1, SearchText, RemoveStr, vbTextCompare) 
    CleanedString = Left(SearchText, CharPos - 1) & _
      Mid(SearchText, CharPos + Len(RemoveStr)) 
  # End If
  
  RemoveText = CleanedString
End Function
  
' Insert SET field for Index style headings. 
' Update all fields, then delete SET field. 
Sub InitiateHeadingsCounter(doc As Word.Document) 
   Dim rng As Word.Range
  
  Application.ScreenUpdating = False
   With doc
     ' Field codes on so that range = field. 
    .ActiveWindow.View.ShowFieldCodes = True
  
     Set rng = .Range
    rng.Collapse wdCollapseStart
  
     ' Insert SET field at beginning of doc. 
    .Fields.Add Range:=rng, _
      Type:=wdFieldSet, _
      Text:="Counter -1", _
      PreserveFormatting:= False
     ' Update all fields, then delete SET. 
    .Fields.Update
    rng.Delete
      
     ' Prepare display for merge. 
    With .ActiveWindow.View
      .ShowFieldCodes = False
      .ShowAll = False
     End With
   End With
End Sub

End Listing One

Begin Listing Two - Save merge results

Option Explicit
  
' Save each record's merge result as a separate file. 
Sub RunMerge()
   Dim MainDoc As Word.Document
   Dim fld As Word.Field
  
   Set MainDoc = ActiveDocument
  
   With MainDoc
    .MailMerge.Destination = wdSendToNewDocument
    .MailMerge.Execute
   End With
  
   Dim ResultDoc As Word.Document
   Set ResultDoc = ActiveDocument
  
   ' Save each record's form letter as a separate document. 
  SaveRecsAsFiles ResultDoc
End Sub
  
Sub SaveRecsAsFiles(doc As Word.Document) 
   ' Convert all sections to Subdocs. 
  AllSectionsToSubDoc doc
   ' Save each Subdoc as a separate file. 
  SaveAllSubDocs doc
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 mail merge. 
    RemoveAllSectionBreaks newdoc
     With newdoc
      .SaveAs FileName:="MergeResult" & CStr(docCounter) 
      .Close
     End With
    docCounter = docCounter + 1
  
     ' Word 97 may require: 
    ' Set newdoc = Nothing. 
  Next
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

End Listing Two