OK, I'm I trying to automate a Word 97 MailMerge from an Access 97 database
form.
I been trying to automate the entire process.  I've talk to Microsoft
Word/Access/OffiveDev engineers and none could help me.  So, if you have a
solution to this, you will be my hero.

Here goes:

WHAT IM TRYING TO DO:

I have made a search form in Access that allows a user to search by whatever
parameters they want.  Once the user has narrowed down their search they can
click a "Merge" button.  This button runs a function that takes the current
forms' filter/recordset and sends it to Word 97 to be manipulated by the
user.  For those of you familiar with it, Symantec Q&A for DOS did this
perfectly.  A user could create (or use an existing) form letter on the
recordset they searched for.

#1) MY FIRST APPROACH:

I first used the acCmdWordMailMerge (the Publish It with MS Word menu
Command).  But in order for this to work you need a Query or a Table
selected.  So I took the filter from the my Search Form and created a
randomly named temp Query with it and then selected the object through code,
and ran acCmdWordMailMerge.

     Set qdef = .CreateQueryDef(queryName, filterString)
     DoCmd.SelectObject acQuery, queryName, True
     RunCommand (acCmdWordMailMerge)
     dbs.QueryDefs.Delete qdef.Name                'delete Query since we
dont need it

This works perfectly.  The MailMerge Wizard starts and one can select the
"Create a new Document and Link Data to It" option and Word starts and
configures the data sources perfectly.  A user can insert fields where ever
and then type the body of their form letter.  Then the user can click Merge
from the Tools/MailMerge menu and everything works great.

The problem occurs here.  Say the user wants to save that form letter they
just created so they dont have to re-type/re-setup the entire letter.  So,
they can use it on future search results they generate.  Then the user saves
the templated form as "ThankYouLetter.DOC" (NOTE: that the merged document
was closed so we are back to the merge template the user setup/typed out).
Then word is closed.  Say the next day the user comes back and wants to send
out the same letter but with a different recordset from the search form in
the Access database.  The user clicks the "Merge" button again and the above
code executes.  The wizard starts and this time the user selects the "Link
your data to an Existing Word Document".  A FileOpen dialog appears and the
uses selects "ThankYouLetter.DOC" and clicks Open.  Word starts and opens
the document and tries to open the DDE connection.

This is the problem  here.  The first time the user created the document a
randomly named query was sent to Word as the Datasource of the MailMerge
document.  The the query was deleted after the function was done excuting.
So the word document is looking for a Query that no longer exists and I get
an Error.   This would work perfectly if Word didnt store the datasource and
attempt to open it everytime you open the Word "ThankYouLetter.DOC"
document.

#2) MY SECOND APPROACH:

My seconed approach was to not use the acCmdWordMAilMerge wizard and write
my own MailMerge Wizard.  Here is the psuedo code:

    filter = Me.filter
    generate Random QueryName

    If filter <> "" Then
        sqlStr = SELECT * FROM  MyTable  WHERE  filter
    Else
        sqlStr = SELECT * FROM  MyTable
    End If

    Dim objWord As Word.Document
    Dim wApp As Word.Application
    Set wApp = CreateObject("Word.Application")
    Set objWord = wApp.Documents.Add

    objWord.MailMerge.OpenDataSource _
    Name:="Contacts.mdb", _
    LinkToSource:=1, _
    Connection:="Query" & QueryName _

The OpenDataSource is the meat of the function.  For those not familiar with
the OpenDataSource method here a quick description from MSDN.

expression.OpenDataSource(Name, Format, ConfirmConversions, ReadOnly,
LinkToSource, AddToRecentFiles, PasswordDocument, PasswordTemplate,
Revert, WritePasswordDocument, WritePasswordTemplate, Connection,
SQLStatement, SQLStatement1)

LinkToSource - Optional Variant. True to perform the query specified by
Connection and SQLStatement each time the main document is opened.

Connection - Optional Variant. A range within which the query specified by
SQLStatement is to be performed. How you specify the range depends on how
data is retrieved. For example:
 When retrieving data from Microsoft Access (Windows only), you specify the
word "Table" or "Query" followed by the name of a table or query.

SQLStatement - Optional Variant. Defines query options for retrieving data.

This works perfectly too like my first approach.  But only for creating the
document and merging it for the first time.  Once the document is saved and
the user tries to use it again the next time an error occurs just like
before, since the original datasource (the temp query was deleted).
The interesting thing here is that there is a parameter called
"LinkToSource"  which is seen above in the OpenDataSource method.  When set
to "1", Word will link to the data source everytime the document is opened.
But I tried setting it to "0" and Word still tries to link to the datasource
everytime the document is opened.  Does anyone know why Word does this.
Technically, from the definition of the parameter this would solve my whole
problem.  The problem being, Word trying to link to the original data source
everytime the MailMerge Document is opened.

Can anybody explain why the LinkToSource parameter isnt working the way it
seems it should?
And can anybody suggest a way to do this so the datasource is removed from
the MailMerge documents everytime the user uses a new query/datasource for
an previous setup MailMerge document?  Remember that the datasource name
changes everytime the user runs my Merge Wizard.  And my goal is total
automation.

HERE ARE THINGS I'VE ALREADY TRIED

1)  I've actually reset the document type and ultimately the maimerge info
with the following code.
objWord.MailMerge.MainDocumentType = -1
This works but I have to stick it in a AutoClose() macro.  Which I feel is
bad since i dont want to be using Document Templates with Macros in them.
Also, the AutoOpen() macro wont work since Word tries to open the old
datasource before it runs a documents AutoOpen() macro -- and thus that
error occurs.

2) I've tried generating randomly named tables from the filter statements
and using those instead of querys but the same errors occur.

3)I've tried given the OpenDataSource method the name of My main Table in
the database and then passing it the SQL filter statement.  This works for
simple statements like SELECT * FROM CONTACTS but word will bomb out as soon
as you start adding WHERE conditions in the SQL statement.  I need the WHERE
conditions from the search form.

 Any help from anybody will be greatly appreciated!!!

Thanks for yout time,
Gary Striano
UCI GSM
Administrative Computing