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.
.gif)
Bon Mot
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.
.gif)
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.)
.gif)
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.)
|
Original field
|
Original result
|
Field and formatting switch
|
Formatted result
|
|
{ MERGEFIELD SaleDate}
|
3/1/1999 0:00:00
|
{ MERGEFIELD SaleDate \@ "dd-MMM-yyyy" }
|
03-Jan-1999
|
|
{ MERGEFIELD SalesAmt}
|
1245.9
|
{ MERGEFIELD SalesAmt \# "#,##0.00" }
|
1,245.90
|
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).
.jpg)
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.
.gif)
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.
.gif)
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