VBA Code for Mail Merge via Dynamic Data Exchange

[Ganymede]

I'm trying to write a code that opens a word document template (from my database) and enters criteria from a query (within my database) into the template. Here's my current code:

   Dim objWord As Word.Document
   Set objWord = GetObject("S:\(My Name)\Affidavit.doc", "Word.Document")
   ' Make Word visible.
   objWord.Application.Visible = True
   ' Set the mail merge data source as the Northwind database.
   objWord.MailMerge.OpenDataSource _
      Name:="S:\(My Name)\Database19.accdb", _
      LinkToSource:=True, _
      Connection:="QUERY ClaimantList", _
      SQLStatement:="SELECT * FROM [ClaimantList]"
   ' Execute the mail merge.
   objWord.MailMerge.Execute

I cannot get this code to properly mail-merge my claimant list. Every time I run the macro, it opens word and prompts me with this:


I tested the code with other inquiries and tables and it worked perfectly. However, I think I have discovered why.

1.) The queries that worked with this code did not have anything in the "criteria field". The query that I'd like to use does have a criteria field. It is triggered by the value of a certain text box within a form.

2.) I believe that having criteria in a query means it cannot be read as an OLE (Object linking and embedding) data source.

When I attempt to manually mail merge from the word document and I select OLE Database files (I have confirm data source on), I get a list of every table AND every inquiry that does not contain anything in the criteria field.


However, when I select "Show All" at the "Confirm Data Source" prompt and select "MS Access Databases via DEE (*.mdb; *.mde), I get a list that includes all queries, including the one that contains criteria.



This leads me to believe that I may be able to write a working code to mail merge the contents of my query IF I can find a way to express it as a DEE source rather than a OLE source. I'm looking for how I might change the code for this effect.

[SteveF]

This is going back years for me, but as I recall I had my best success using the following formula:

Export the results of your query to an external data file, e.g. CSV.
Then use your Access code to point the Word doc at the external file for its merge data and execute the merge.

These steps can all be done in code, of course.

[Ganymede]

This is going back years for me, but as I recall I had my best success using the following formula:

Export the results of your query to an external data file, e.g. CSV.
Then use your Access code to point the Word doc at the external file for its merge data and execute the merge.

These steps can all be done in code, of course.

Thank you. I'll have to try that out.