The mail merge capability of Microsoft Word is a powerful tool that can be used
to automate much of the drudgery of creating form letters, mailing labels and catalog-style
documents. It’s also a great tool for
creating reports with complex formatting, and sending personalized e-mail to individuals
without resorting to some of the more sophisticated (and expensive) email tools that
are designed for that explicit task. However
when the number of merge records gets large, data sources using Word, Outlook or Excel
are often quite slow and cumbersome to use. On
the other hand, Access can handle the task with relative ease – IF it all works correctly. There-in
lies the rub, especially if you want the merge task to be handled by a non-technical
person.
In this tutorial we want to share some useful tips from our experience in using merges
with production databases.
Data
Source Challenges
When you rename an object that is used as the merge data source (a common occurrence
as a production database evolves), Word promptly announces it can’t find it’s data
source and gives you the choice of finding
the data source or dropping the data source information. It
is often difficult to retrieve the name of the data source Word thinks it is supposed
to use. Word displays it in the Mail
Merge Helper dialog box, but if the path to the database is of any substantial length,
you can’t read the data source table or query name.
Furthermore, if you move or rename the database, when Word tries to start the database,
another common occurrence, it finds it can’t, and then gives you two options: remove
the data source information, or remove all merge information. At
that point, the solution is to move and/or rename the database back to it’s original
state, write a small macro to display the data source name, and then repeat the move/rename
process and specify the new data source. Along
these lines, an annoyance related to Word is that if you try to change the data source,
it does not remember that you were using an Access database previously, nor does it
remember the path.
In some cases, especially if Access security is being used, a merge initiated from
Word always starts a new instance of Access, even if Access was already open with
the correct data source database. We
discovered that a common cause was that the application name had been changed to something
other than Microsoft Access. We
do this frequently so that users can identify the application correctly. The
solution to this problem is to rename the application to Microsoft Access –
XXXXXX.
Other Merge Issues
Another issue that users commonly face is the need to use a parameter query as the
data source for a merge. In that situation,
you must use DDE as the method of connection to the Access database. In
Word 2002, that can be a substantial challenge under some conditions, as the option
isn’t always presented, depending on configuration issues. In
addition, DDE presents some performance problems, so you may want to explore other
options in creating a document.
Hans Vogelaar (of Woody's Lounge fame) suggests an alternative where you use a form
to collect the parameter information. When the user has completed the information,
VBA statements are used to construct the SQL string with the actual criteria embedded
in in the string. The merge is then completed using an ODBC connection, which
provides significant advantages in both speed and reliability. This does imply
that you need to be reasonably proficient with VBA, and with creating queries dynamically
by manipulating the QueryDef collection. One detail should be noted: you
must specify the SubType argument of MailMerge.OpenDataSource as wdMergeSubTypeWord2000
or an error will occur.
Another situation that occurs is corrupt main merge documents. We
have seen documents, which have been used successfully for some time, suddenly either
loose all of the data source information, or actually crash Word when you try to open
it. OLE Automation, now commonly referred
to as Automation, is one way to tame the problems with merges, and also makes user
instructions for production databases simpler. There
are still some complications that need to be noted.
In many cases, especially if you are using Access security, Word will still attempt
to open a second instance of Access to attach to it’s data source, even if you have
set the Word data source in your automation code. Also,
Word invariably prompts you about saving the main document after you have completed
the merge and close down Word. The natural
inclination of most users is to say yes, which generally doesn’t cause any harm, but
will on rare occasions cause document corruption. The
solution to this problem us to actually close the main merge document in code after
executing the merge, so that the user is not prompted, and to deliberately remove
the data source information from the main document and then save it as a read-only
document. In our tutorial on automation,
you will find a sample set of code for executing merges to Word from Access.
Merging to email
The experience with Word-based email messages has generally positive. In
this case, the user selects the data source when the document is converted to a mail
merge document. Then they specify the
destination of the merge to be Electronic Mail using the mail merge dialog box or
with the Mail Merge Wizard in Word 2002. One
requirement is that the main document must contain at least one merge field before
Word will let you execute the merge – this caused some problems at some clients. The
solution was to use a merge field that we knew would always be empty. Another
issue that confronts email merges, is that Word/Outlook insists on saving a copy of
every email sent in the Sent Items folder, even if Outlook is configured to not do
so.
Finally, the problem of frequently changing email addresses can cause serious logistical
problems. One option is to develop a
tool to identify rejected email addresses and mark them as bad when emails are returned
as undeliverable. If you are considering
this approach, be sure to design your system so that you can mark rejected addresses
as bad and exclude them from future merges.
Alternatives to Merges
Other methods for producing some types of merges should be considered, especially
if there are a large number of data records, say 1000 or more. The
first thing to be considered is whether an Access report would be acceptable. Reports
are much quicker to generate and to print, and can have many of the same characteristics
as a Word document. On the other hand,
you are limited in what you can do with formatting of characters, paragraphs and so
forth with reports, even if you choose to use a rich text control. Also,
customized reports can’t really be emailed in the same fashion as a Word merge to
email.
Another method of creating a complex report that is effective, especially for creating
a single copy of a document, is to use Automation. In
that situation, you program Word to create a document, or create it from a template,
and do the normal formatting you would do if you were building the document manually. In
one situation, we encountered the need to do complex formatting in the header and
footer of the document, and to change at various points in the document. It
had originally been created as a catalog merge in Word; rewriting it as a Word automation
document built the same 600 page document in about one fifth the time and nicely supported
the header and footer customizations.
Summary
In summary, if you are contemplating Word mail merges driven by Access on a recurring
or production basis, consider these points:
-
Use OLE Automation code to drive the Word merge from Access
-
Save the main document with no data source and make it read-only
-
If you change the application name, make sure it starts with Microsoft Access
-
If several merge documents exist, store the document names and data sources in Access
tables so the information is preserved and readily available
-
If you are merging to email, be sure your Outlook (or Exchange Server) folders have
room to store the twice the number of emails you will send.
-
If you are using Office XP, be sure to read how to invoke the DDE method.
Further
Resources