Mail Merge: Part I

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

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

Introduction and Data Integration

By Cindy Meister

A word processor becomes a truly powerful office tool once information and data from other sources can be integrated into its documents. The result can range from simple form letters to complex business reports. In this and subsequent articles, I'll look at various ways data can be brought into Word, starting with the built-in mail merge facility.

Mail Merge from the Developer Viewpoint

As a developer, you'll be interested in mail merge primarily from the viewpoint of automating the process of passing data into Word. If you aren't a Word user, you may not be familiar with the concept, so I'll mention a few basics as I go along.

Word's mail merge can produce four basic kinds of documents: form letters, envelopes, labels, and catalogs. The first two are basically the same. Merge fields that link in data fields from the data source are placed in the document, and a section with the text and merged data is produced for each record in the data source. Envelope and form-letter merges differ primarily in that a setup dialog box will be presented for the envelope to allow the user to select envelope size, orientation, etc.

A label mail merge also displays a set of dialog boxes before generating a page with a table on it, the cells of which correspond to the number and size of the selected label type. With the exception of the first cell, a Next field precedes the merge fields in each cell, so the data from more than one record is entered on the same page.

The main difference between a catalog merge type and a form letter is that no section break is inserted between the data records - each follows directly after the other on the same page until the page breaks automatically once it's full. This is the merge type to use when the required result is a list, such as an address book or telephone directory.

Mostly, you'll set up the Main merge document as a template so your program can re-use it (via Documents.Add) every time a particular document must be produced. For the occasion where you need to automate the creation of the Main merge document, you can set the MainDocumentType property of the document's MailMerge object. Besides the constants wdCatalog, wdEnvelope, wdFormLetter, and wdMailingLabels, there is wdNotAMergeDocument (corresponds to "Restore to normal Word document" in the Mail Merge Helper). This property is useful for unlinking a data source and determining the state of a document, before executing further mail merge commands:

Set docMerge = ActiveDocument.MailMerge With docMerge
  If .MainDocumentType = wdNotAMergeDocument Then
    .MainDocumentType = wdFormLetters
  End If
End With

Getting the Data

Besides setting up a document as one of the four types of Main merge documents, the data source must be linked in. What ought to be a fairly straightforward process is unfortunately fraught with pitfalls  - for the user, and for the developer.

Except for some changes in the dialog boxes and the ability to access "Views" for ODBC data sources (Word 95), MAPI Address Books (Word 95), and HTML format as data sources, the mail merge feature in Word hasn't changed substantially in the last 10 years. As a matter of fact, in many respects, the capabilities have degenerated since Microsoft put most of the development resources into "Web" options in the last two versions, giving little attention to data integration. The default data source Connect method for other Office applications (Excel and Access) is DDE; otherwise it's ODBC if the drivers are installed. Word can also link to spreadsheet and text data sources using its internal converters (see FIGURE 1). As far as Word mail merge is concerned, DAO, RDO, and ADO don't exist.

FIGURE 1: Activating the Select method in the Open Data Source dialog box enables the user to select from all available connection methods.

The most basic Word data source is a Word table in a Word document. The mail merge will pretty much transfer the fields' content as-is, including graphical objects (but not formatting). No problems with things like mismatched quotes or special characters and symbols occur, unlike with many other data source types.

This is fine, but the purpose of most automation is to use data from sources outside Word. Besides Word tables, mail merge recognizes data sources of these file types: .htm, .rtf, .csv/.txt, .mdb/.mde (Access), .xls (Excel), .qry/.dqy (MSQuery), and .db/.dbf (Paradox, FoxPro, dBASE). To save you some frustration and time, here are some tips to get around the more frequently encountered problems with various non-Word data sources.

Text Files

Delimited text and comma-separated value files are standard data formats. Almost any program that imports and exports data supports one or both, be it a Windows, DOS, or mainframe application. Up through Word 95, using text files as a data source was a fairly straightforward proposition.

But if you're merging in Office 97 (and on Office 2000 machines upgraded from an earlier version), you're likely to run into a number of problems: The individual fields aren't recognized, the names all run together as a single field, and error messages like "Word was unable to open the data source" appear. The culprit is the text ODBC driver with which Word does not communicate correctly. To link up to your data source, you have three options:

1)        uninstall the ODBC driver;

2)        activate the Select method checkbox so you can specify Word's internal text file converter as the link method (see FIGURE 1);

3)        rename the file with an extension the ODBC driver doesn't recognize (such as .dat), so that Word automatically uses its internal text file converter.

"Virgin" Office 2000 installations shouldn't have this problem, as Microsoft didn't include the Text ODBC converter as one of the recognized link methods.

If you use a Header file to define the field names for the data source (as is often the case with data coming from a mainframe), an annoying automation obstacle you'll encounter in all versions of Word is a dialog box requesting you to confirm the field and record delimiters. For this, too, there are a couple of work-arounds:

1)        Create the Header file as a Word table, or

2)        duplicate the first line of the Header file, so that it contains two lines with the same number of fields using the same field delimiter (Note: This only works for field delimiters, not for record delimiters.):

FirstName~LastName~Address~City~PostalCode
FirstName~LastName~Address~City~PostalCode

Text files created with non-ANSI character sets present a problem. Word will open them for mail merge automatically, without recognizing their origin or giving you the opportunity to convert them. For a DOS file, with ASCII Code Page encoding, you could get something like this:

Vous ^tes all,s ... Bfle. 

instead of:

Vous êtes allés á Bâle. 

Looking at the OpenDataSource method of the MailMerge object, you may notice the Format argument and think this provides a way out. Unfortunately, it's generally ignored by Word, even if you use one of the built-in constants. What does work is opening the document manually in Word. With Tools | Options | General | Confirm conversion at open activated, select the correct conversion filter, and save it as a Word document for use as the data source. However, there is no ideal way to automate this. Again, in the Open method, Word provides no way to specify the correct converter. You have to rely on SendKeys to make the proper selection, and confirm it in the Confirm Conversions dialog box:

szFileName = "E:\My Documents\Mergdata.txt" Options.ConfirmConversions = True
SendKeys "^{HOME}MS-DOS Text{ENTER}" 
Set doc = Documents.Open(FileName:=szFileName) 
doc.SaveAs Left(szFileName, Len(szFileName) - 3) & "doc" 

Excel Workbooks

All things considered, Excel is very reliable as a data source. You can import and link to data from many other sources - even ones Word doesn't support - using MSQuery. Excel has powerful data-management features, making it easy to sort and filter lists, analyze the data, and create charts and pivot tables. On a typical installation, three types of link methods are available for the merge: DDE, ODBC, and Word's internal spreadsheet converter.

The best one to use depends very much on what aspect of the data is important. For example, if the version of Word is pre-2000, and you want to merge to data not on the first spreadsheet in the workbook, you need to use Word's internal spreadsheet converter, which allows you to specify any range.

To merge to a filtered subset of records, only a DDE connection will do. When you first set up the link, you'll be a bit nonplussed to see that all the records are included, but that those filtered out in Excel show no data. Suppress them by setting a Query Option in Word to show all records where a particular field that you know always has an entry (an ID field, for instance) to "is not blank," as illustrated in FIGURE 2. (Record the action in a Word macro if you need to get the VBA code for automation purposes.)

FIGURE 2: Recognize and suppress filtered record from an Excel data source.

Of all the link methods, ODBC is certainly the fastest. But besides older ODBC drivers not allowing you to select a range in the workbook, it will also not pass through date and number formatting. This is because ODBC only passes across the underlying data. You can remedy this problem in your Main merge document by adding number and data format switches to the merge fields affected, as shown in FIGURE 3. (Remember, use [Alt][F9] to toggle between field result and field code display.)

FIGURE 3: Use formatting switches to change how dates and numbers are presented in the mail merge result. Note the uppercase "M" for month and the use of double quotes.

As an alternative, you can create an additional column in the Excel spreadsheet that converts the numerical data to text using the Text function. For example, to pass across the date in FIGURE 3 as literal text, assuming it's in cell C2, type the formula =Text(C2, "dd-mmm-yyy"), then copy it for all the records. Use this technique, rather than trying to reformat an existing column of data, because another problem with ODBC occurs if the Excel column mixes data types. The ODBC driver scans the first eight rows of the Excel data source in order to determine a field's data type. If the data in a field doesn't match (there's text in a numeric field, for example), a NULL value (empty result) is passed to Word for that record's field. This behavior is documented in Knowledge Base article Q141284 (http://support.microsoft.com/support/kb/articles/Q141/2/84.asp).

Occasionally, a user may contact the Help Desk in panic because the mail merge data coming from Excel is mixed up, records are repeating, or the merge stops after a certain number of records. The usual reason for this is that the Main merge document has become damaged, which is an issue for the developer, because the company will turn to the person who provided them with the application! If this happens to you, either go back to your original, backed up copy of the Main merge document, or copy everything from the damaged Main merge document to a new document except for the last paragraph mark. If your code doesn't set up the mail merge, go through Tools | Mail Merge to set the main document type and link in the data source.

Access

As mentioned earlier, DDE is the default link method for Access. The same observations concerning speed, number, and date formats apply here to DDE vs. ODBC as for Excel. In Access, the corresponding approach for passing Word the desired number of formats as literal strings via ODBC is to use the Format function in expressions in the query providing the data (see FIGURE 4).

FIGURE 4: Convert numerical data to literal strings (done here using the Format function), so an ODBC merge displays the information the way you want to see it.

If the data source is a parameter query, you must use DDE. However, note that you can't guarantee that the parameter dialog box will be displayed in the Word environment; it might come up in the Access window instead. More reliable is to request the user information in the Word environment, then pass it as part of a SQLStatement argument in the OpenDataSource method.

There is an issue with DDE in Office 97 and 2000: The built-in procedure that sets up the DDE link requires the default title bar caption. If it can't find it, another instance of Access is run; and every time the mail merge needs to re-contact the data source (when querying, for example), another instance is loaded. If you're familiar with Access, you can imagine that most typical installations will be dangerously low on resources by the time three or more copies of Access are open. The problem can usually be solved by removing any customized Application Title on the Startup dialog box, which is accessible from Tools | Startup.

Until Office 2000, ODBC was definitely the way to avoid this problem, as well as to speed things up. However, whatever changes were made in the ODBC drivers that were not accounted for by the Word 2000 developers make this an uncertain option. The more complex the query, the longer the SQL behind it, and the greater the chance that Word will reject it as a data source with the laconic, uninformative message "Word cannot open the data source."

There are some query types to which Word can't set up a link at all, e.g. aggregate ("group-by") queries. In this case, your only recourse is to export the query result to an Access table, or other data file type (such as delimited text or an Excel spreadsheet) for use as the merge data source.

Others

Linking to other data sources is becoming more and more difficult with each succeeding version of MDAC. What worked in Word 6 or Word 97 may well not function in a later version. If you've installed MDAC 2.1, to link to dBASE .dbf files, for example, you need the Borland Database Engine (BDE), or you have to set up a DSN through the FoxPro VFP driver. Here you may well encounter another hurdle: To use the FoxPro driver with Word's mail merge, you also need to download Wdvfpdsn.exe from the Microsoft Web site and run it (http://support.microsoft.com/support/kb/articles/Q237/2/84.asp).

Mail merge only recognizes the data sources listed in the Open Data Source dialog box, so there's no way for the user to directly access Oracle or Microsoft SQL Server information, even if User, System, or File DSNs for installed ODBC drivers are available. To work around this, Microsoft has provided a button to run MSQuery (an applet associated with Excel). Unfortunately, when moving to the Office 97 Unicode interface, Word lost some capabilities, including being able to accept query strings longer than 255 characters, thus seriously limiting the usefulness of MSQuery. For a user, the only work-around is to first link the information into Excel (via MSQuery - here it works fine) or Access (as a linked table), then set up the mail merge to that data source.

As a developer, you have an alternative. You can get around this limitation using the Connect, SQLStatement, and SQLStatement1 arguments of the OpenDataSource method. The SQLStatement and SQLStatement1 arguments each allow a maximum of 255 characters in the string, which, while limited, at least doubles the capacity MSQuery is allowed. This code snippet links to the Customers table in NorthwindCS.sql, created as a DSN in the 32-bit ODBC manager:

ActiveDocument.MailMerge.OpenDataSource Name:="", _
  Connection:="DSN=SQL_UserDSN;UID=<your_user_name>;PWD=<your_password>'', _
  SQLStatement:="SELECT * FROM Customers" 

When using SQL Server as a data source, the field content may be lost during the merge process, even though you're able to view it in the Main merge document. This can happen if the fields are of data type NCHAR/NVARCHAR (Unicode strings, etc.). Possible work-arounds are to change the field types, or to create a view that returns non-Unicode data. If the upsizing wizard has been used to convert an Access database to SQL Server, you'll see this problem.

VBA: To Link or Not to Link

Now that you've decided which Main merge document type you need, and what data source and link method are appropriate, it's time to turn your attention back to the Main merge document. The next step is to decide whether the file should be saved as a regular Word document, or as a Main merge document, and whether the link to the data source should be saved with it.

Word hard-codes the complete path to the data source in the document's binary file format. (If you open a Main merge document in an editor, such as the old Windows 3.x Write, you can see the non-encoded parts of the file, including the path to the data source.) As long as the data source doesn't move, this isn't a problem.

If it will change, however, you should save the Main merge document without the data source, and link it to VBA after opening the file. The link must be broken before you save the file, because Word and VBA don't provide a way for you to check and change the link before Word tries to resolve it when the document is opened. Converting a Main merge document back to a normal Word file doesn't affect any merge fields that have been inserted, but it will remove the data source and any query options:

Activedocument.MailMerge.MainDocumentType = _
  wdNotAMergeDocument

The most reliable way to get the proper syntax for the OpenDataSource method is to record the linking process as a macro. Be aware, however, that you can't specify certain types of link methods via VBA. For instance, you can't differentiate between DDE and Word's internal spreadsheet converter for an Excel worksheet. Internally, the mail merge facility will automatically select the default DDE method if it's available. Compare the procedures in FIGURE 5.

Sub LinkXLODBC()
  ' Connect to an Excel data source using ODBC. 
  ' Recorded macro. 
  ActiveDocument.MailMerge.OpenDataSource Name:= _
    "E:\My Documents\XLTestData.xls",  _
    ConfirmConversions:= False, LinkToSource:= True,_
    Format:=wdOpenFormatAuto, Connection:= _
    "DSN=Excel Files;DBQ=E:\My Documents\XLTestData.xls;" _
    & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;", _
    SQLStatement:="SELECT * FROM 'Data'", SQLStatement1:="" 
End Sub
 Sub LinkXLDDE()
  ' Connect to an Excel data source using DDE. 
  ' Recorded macro. 
  ActiveDocument.MailMerge.OpenDataSource Name:= _
    "E:\My Documents\XLTestData.xls", _
    ConfirmConversions:= False, LinkToSource:= True, _
    Format:=wdOpenFormatAuto, Connection:="Data", _
    SQLStatement:="", SQLStatement1:="" 
End Sub
 Sub LinkXLConverter()
  ' Connect to an Excel data source using
  ' Word's spreadsheet converter. 
  ' Recorded macro; When run, this links using DDE! 
  ActiveDocument.MailMerge.OpenDataSource Name:= _
    "E:\My Documents\MergData.xls", _
    ConfirmConversions:= False, LinkToSource:= True, _
    Format:=Application.FileConverters("MSBiff"). _
    OpenFormat, Connection:="", SQLStatement:="", _
    SQLStatement1:="" 
End Sub

FIGURE 5: Three recorded macros. They don't always act as you'd expect.

One type of data source you'll always want to attach using VBA when the Main merge document is opened is an Address Book. Word can work with data from any MAPI-registered address book. For Microsoft products, this means Schedule+, an Exchange PAB, or an Outlook Contacts list. For mail merge, Word doesn't link to any Address Book directly, but converts its contents to a Word table in memory. Once Word is closed, this virtual file is destroyed. So when a Main merge document with an Address Book data source is opened, the user will see "Word Cannot Find Its Data Source C:\...\..._virtual_file_....olk" and will have to work through a couple of dialog boxes.

Make sure the document or template is saved without the data source. Then, the following procedure, saved in the document or its template, solves the problem neatly:

Sub AutoOpen
  ' AutoOpen executes when a document is opened. 
  ' Link up to the Outlook Contact List. 
  ActiveDocument.MailMerge.UseAddressBook Type:="olk" 
End Sub

Interacting with the User

After opening the Main merge document and data source, you may be all set to produce the mail merge result. But there are occasions where you'll want to interact with the users, either because they have no direct access to the data source, or the information to be included is a one-time thing.

There are two fields that can be inserted into a document to get user input (and they don't require VBA): Fillin and Ask. Both display a prompt when updated, but only the Fillin field displays the user input directly. The Ask field stores it in a bookmark, which can be displayed or otherwise referenced during the merge using a Ref field. The prompts can be displayed once for the entire mail merge, or for every record in the mail merge by including the \a switch (see FIGURE 6). Because the prompts aren't shown in context with the text, it's useful to nest merge fields displaying relevant data in the prompt text.

FIGURE 6: Prompt for user input using a Fillin or Ask field: result and field codes.

What's slightly annoying, however, is that Word tends to display Fillin field prompts at times other than when a merge is executed, e.g. when a new document is created from a template. You can prevent this behavior by locking the fields until the mail merge is executed (see FIGURE 7). You also don't want to confuse the user by showing irrelevant prompt information from a former session with the Main merge document; set the field's result to an empty string, or some standard text (e.g. "Salutation appears here").

Sub AutoClose()
  ' Set Fillin field to "empty" and keep it
  ' from updating when not merging. 
  With ActiveDocument.Bookmarks( _
         "txtSalutation").Range.Fields(1) 
    .Result.Text = "" 
    .Locked = True
  End With
End Sub
 Sub RunMerge()
  With ActiveDocument
    ' Unlock Fillin field so it is updated
    ' during the merge process. 
    .Bookmarks("txtSalutation") _
      .Range.Fields(1).Locked = False
    .MailMerge.Destination = wdSendToNewDocument
    .MailMerge.Execute
  End With
End Sub

FIGURE 7: Managing Fillin and Ask fields in a user-friendly manner.

One feature Word doesn't have is a way to freely select records to merge that don't meet a common criterion. You can provide this for your users within the Word environment with a UserForm and a bit of code; see Listing One. This solution is based solely on Word VBA, but it would also be possible (and faster, if the data source contains a lot of records) to link to the data source directly via DAO/ODBC or ADO. (This topic will be covered in a future article.)

The SelectContacts procedure is the starting point, calling the UserForm in FIGURE 8. The UserForm_Initialize event handler calls the FillList function, which fills the ListBox with data from a specified field in the data source. Before looping through the data records, the QueryString is adjusted to sort them by the same field being accessed for the list. Filling the list is slow due to the way Word scrolls through the records when VBA is used: It always restarts at record one for each next record call, and counts forward.

FIGURE 8: Enabling the user to select records from a list.

The ListBox is set to MultiSelect so every entry the user clicks on will be selected. Once OK is clicked, GetUserSelection runs through the items in the list and picks out those that were selected, putting them into an array. The array is then passed to the CreateQueryString function that puts together the "Word SQL" to specify the individual records. Note: the syntax may be different from what you're accustomed to:

WHERE (("ContactName" = 'John Doe' )) OR
      (("ContactName" = 'Mary Brown')). 

Finally, the entire QueryString is concatenated and applied to the data source in the QueryTheData procedure.

With a bit of modification, you can use the same basic approach to allow users to search for a specific record. The mail merge feature provides the Find Record dialog box (the "Binoculars" toolbar button), but it's necessary to know what value should be typed into the Find What text box. Instead, you can present the user with a list from which to choose. Listing Two extends the basic code.

The same UserForm is modified slightly to accommodate the additional functionality. Because filling the list is time consuming, and finding records can be required fairly often (in contrast to just selecting records for the merge), the UserForm is no longer being unloaded when the dialog box is dismissed. Instead, it's simply hidden so all settings remain intact. The form caption and the MultiSelect property of the ListBox need to change according to which command bar button is activated (top, FIGURE 8). These settings are made in the UserForm_Activate event handler, which is executed every time the form is displayed.

When the user confirms the selection by clicking OK, the appropriate mode is determined from the UserForm's caption. The FindUserSelection procedure makes sure the first record is the active one, so all records in the current selection are available to the search. It then uses the FindRecord method to locate the user's choice. Afterwards, the same method is used to search for a record that isn't in the recordset. If this isn't done (due to a bug in Word 97 and 2000), the user can't flip through the records, nor can the merge be executed.

The functionality could be further extended by allowing the user to specify from which field the Select and Find lists should be filled. I leave this as an exercise to the reader. You can build on the code here, which is available for download (see end of article for details). Hint: Word VBA has a MailMergeDataFields collection.

Executing the Merge

Many people think that once they can view the individual records in the Main merge document that the merge is complete. Although you can print single envelopes or letters this way, there's more to a mail merge. The feature is actually meant to generate one letter, label, envelope, or list entry per record in the data source.

The mail merge can be sent directly to the printer, for example. However, if you're automating the entire process, you won't want to select this option. Word displays the Print dialog box, and there's no way to suppress it (unless you take a chance on SendKeys {Enter}).

Instead, set the MailMerge.Destination to wdSendToNewDocument, then print the result. By the way, a catalog mail merge must be merged to a new document before it can be printed.

Form letters, envelopes, and labels can also be sent to a MAPI-compliant fax printer or the default e-mail program, although normally this only makes sense for letters. An e-mail letter can only be sent as plain text (no .rtf or HTML format), unless you send the resulting document as an attachment, rather than in the e-mail body.

For faxing and e-mail, the fax number or e-mail address must be in one of the data source's merge fields. Fax numbers must be formatted so that mail merge recognizes them correctly. For details, consult the Knowledge Base article Q155314: "How to Format the Fax Number Field When You Merge to Fax." It's available at http://support.microsoft.com/support/kb/articles/Q155/3/14.asp.

Besides filtering records using query options (i.e. with the QueryString property of the DataSource object), Word also lets you merge a specified range of records, e.g. from record number 5 to 10. This works fine in Word 2000, but may not work reliably - or at all - in earlier versions. From VBA, set the FirstRecord and LastRecord properties of the MailMergeDataSource object.

Conclusion

That covers the basics for automating Word's mail merge. You're now acquainted with the types of mail merge documents available and the aspects of using various data sources, as well as with many of the VBA commands for the Word mail merge object model. Clearly, this feature has lots of potential for improvement; we can only hope that Microsoft brings it up to the current standards of data integration technology. It will be interesting to see what they do in the next version of Office.

In Part II, I'll look at how to supplement the built-in mail merge feature, making it more versatile and powerful. The article will cover topics such as formatting, one-to-many relationships, and managing conditional, formatted text inclusions.

Cindy Meister owns a consulting business, INTER-Solutions, based in Switzerland. Before 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 Word support, and as Sysop in the CompuServe Word 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 - Select contacts for mail merge

'Code behind UserForm. 
Option Explicit
 Private Sub cmdCancel_Click()
  Unload Me
End Sub
 Private Sub cmdOK_Click()
  Dim aSelectList()As Variant
  Dim szQueryString As String
 
  aSelectList() = GetUserSelection(lstRecords) 
  szQueryString = CreateQueryString(aSelectList())
  QueryTheData szQueryString
 
  Unload Me
End Sub
 Private Sub UserForm_Initialize()
  Dim NrRecs As Long
 
  NrRecs = FillList(lstRecords) 
  lblNrRecs.Caption = Trim(CStr(NrRecs)) & " Records" 
End Sub
  ' Code in project module. 
Option Explicit
 ' Constant values for list of data, so user can select
' records. 
''' The basic SQL statement. 
Private Const SQLSelect As String _
  = "Select * from Customers" 
''' The field from which the list is built. 
Private Const FldSelect As String = "ContactName" 
''' The field on which the merge records are sorted. 
Private Const RecOrder As String = "Country" 
 Sub SelectContacts()
  ' Display Userform with list from data source. 
  frmMailMerge.Show
End Sub
 Function FillList(lst As MSForms.ListBox) As Long
  ' Fill list with records from a field in the data source. 
  Dim MMSource As Word.MailMergeDataSource
  Dim NrRecs As Long
 
  Set MMSource = ActiveDocument.MailMerge.DataSource
  With MMSource
    ' Sort according to the field. 
    .QueryString = SQLSelect & " ORDER BY " & """" & _
                   FldSelect & """" 
    ' Get the record count. 
    .ActiveRecord = wdLastRecord
    NrRecs = .ActiveRecord
    ' Go to the first record and get field entry. 
    .ActiveRecord = wdFirstRecord
    lst.AddItem .DataFields(FldSelect).Value
    ' Repeat until all records have been read. 
    Do While .ActiveRecord < NrRecs
      .ActiveRecord = wdNextRecord
      lst.AddItem .DataFields(FldSelect).Value
    Loop
  End With
  FillList = NrRecs
End Function
 Function GetUserSelection(lst As MSForms.ListBox) _
  As Variant
 
  ' Get all user selections from multiselect list. 
  Dim aList()As Variant
  Dim NrItems As Long
  Dim ItemCounter As Long
  Dim Listcounter As Long
 
  NrItems = lst.ListCount
  Listcounter = 0
   If NrItems = 0 Then
    ReDim aList(0) 
    aList(0) = "None" 
    GetUserSelection = aList()
    Exit Function
  Else
    ' Loop through all items in ListBox. 
    For ItemCounter = 0 To NrItems - 1
      ' If item has been selected. 
      If lst.Selected(ItemCounter) = True Then
        ' Put value from list in array. 
        ReDim Preserve aList(Listcounter) 
        aList(Listcounter) = lst.List(ItemCounter) 
        Listcounter = Listcounter + 1
      End If
    Next ItemCounter
  End If
   If Listcounter = 0 Then
    ' If user has made no selection. 
    ReDim aList(0) 
    aList(0) = "None" 
  End If
   GetUserSelection = aList()
End Function
 Function CreateQueryString(aList()As Variant) As String
  ' Build query string to include all item
  ' in user selection array. 
  Dim TheString As String
  Dim Listcounter As Long
   ' Required syntax for MSWord: 
  ' (("ContactName" = 'John Doe')) OR
  ' (("ContactName" = 'Mary Brown')) 
  For Listcounter = LBound(aList())To UBound(aList())
    If Len(TheString) <> 0 Then
      ' Include ORafter first item. 
      TheString = TheString & " OR " 
    End If
    TheString = TheString & "((" & """" & FldSelect & _
                """" & " = '"& aList(Listcounter) & "'))" 
  Next Listcounter
   CreateQueryString = TheString
End Function
 Sub QueryTheData(szQueryString) 
  ActiveDocument.MailMerge.DataSource.QueryString = _
    SQLSelect & " WHERE " & szQueryString & _
    " ORDER BY " & RecOrder
End Sub

End Listing One

Begin Listing Two - Find

' Code behind UserForm. 
Option Explicit
 
Private Sub cmdCancel_Click()
  Me.Hide
End Sub
 
Private Sub cmdOK_Click()
 
  Select Case Mid(Me.Caption, 1) 
    Case Is >= "Select" 
      Dim aSelectList()As Variant
      Dim szQueryString As String
 
      aSelectList() = GetUserSelection(lstRecords) 
     
      ' No user selection was made, so nothing happens. 
      If aSelectList(0) = "None" Then Exit Sub
      szQueryString = CreateQueryString(aSelectList())
      QueryTheData szQueryString
      Me.Hide
    Case Is >= "Find" 
      Me.Hide
      FindUserSelection
    Case Else
      Debug.Print Mid(Me.Caption, 1) 
  End Select
End Sub
 
Private Sub UserForm_Activate()
  ' Reset control properties when form is redisplayed. 
  Me.Caption = CommandBars.ActionControl.Caption
 
  Select Case Mid(Me.Caption, 1) 
    Case Is >= "Select" 
      ' Get rid of previous selection. 
      lstRecords.MultiSelect = fmMultiSelectSingle
      ' Activate multiselect. 
      lstRecords.MultiSelect = fmMultiSelectMulti
    Case Is >= "Find" 
      ' Single selection only, for find. 
      lstRecords.MultiSelect = fmMultiSelectSingle
    Case Else
      Debug.Print Mid(Me.Caption, 1) 
  End Select
End Sub
 
Private Sub UserForm_Initialize()
  Dim NrRecs As Long
 
  NrRecs = FillList(lstRecords) 
  lblNrRecs.Caption = Trim(CStr(NrRecs))  & " Records" 
End Sub
 
' Code in project module that differs from Listing 2. 
 
Sub FindUserSelection()
  ' Find record user selected
  With ActiveDocument.MailMerge.DataSource
    ' Go to first record so that all
    ' records are available in the search. 
    .ActiveRecord = wdFirstRecord
    ' Find the selected record. 
    .FindRecord FindText:=frmMailMerge.lstRecords.Text, _
      Field:="ContactName" 
    ' MS BUG!!! 
    ' Search a record not in the data
    ' so that merge operates properly. 
    .FindRecord FindText:="x", Field:="unknown" 
  End With
End Sub

End Listing Two