Hints on managing MailMerge using VBA

[EHPorter]

I have a number of VBA routines which I use to assemble mailmerge documents
(they allow the user to select from a number of possible paragraphs and put
those paragraphs together into a single merge document). The data source
for the document is an Access database. Once the mailmerge document is
completely assembled, it is merged to some, but not all, of the records in
Access.

Right now I do the merge manually. I would like to take this one step
further, and automate the process using VBA. Problem: right now, I can see
how to set this up to merge with all of the records in the database, but
cannot see a good way to allow the user to select some (but not all) of the
database records to merge with.

I am still in the conceptualizing stage of this project. I am hoping for
some suggestions as to how this could best be done and perhaps some sources
for sample code or the like.

One good way to do this would be to have VBA open the mailmerge dialog box
in Word, pause to allow the user to check those mailmerge recipients he or
she desires to include, and then resume to complete the merge when the user
clicks the "OK" button. Is this feasible? Is there a better way to
accomplish this goal?

Thank you.

 

[John Renkar]

I have a few applications that do this. I have set up a query as the
datasource for the Word Mailmerge document. I then use code to Generate the
query applying any filters needed. Then I just generate the Mailmerge and
it only contains the data I need.

Here is a sample of the code I use for generating the query:
*************************************
'Set query for Header
strSQl = "SELECT tblAccount.ID, tblAccount.AccountName"
strSQl = strSQl & " FROM tblAccount"
'Set Criteria here
strSQl = strSQl & " WHERE tblAccount.ID = " & Forms!frmAccount!ID
' If query exists, delete it.
For Each qdf In db.QueryDefs
If qdf.name = "qryAccount" Then
db.QueryDefs.Delete qdf.name
Exit For
End If
Next qdf
Set qdf = db.CreateQueryDef("qryAccount", strSQl)
qdf.Close
*******************************************
When you do the mailmerge it only merges the data selected in the query.

Do not try to have the query just filter on Forms!frmAccount!ID it will not
work. You have to hard code the filter into the query.