Need to run mailmerge from Access VBA

[pledbetter]

I am using Office 2010. I created a mailmerge document in Word that uses my Access database as its data source. That data source is a local table in the database. When I open the Word document, I get the prompt to connect to my data source and run the mailmerge. When I do so, the mailmerge runs perfectly.

If I start the mailmerge from within Access by clicking on External Data - Word Merge, that runs fine.

I want to be able to do the mailmerge with VBA, either by loading the Word document and running the mailmerge without being prompted for the data source, or wholly from Access, as if I clicked on External Data - Word Merge.

The desired result is a Word document with the merged data, ready for printing, emailing, or saving as a PDF.

I've been working on this all day and I'm stumped.

Thanks in advance.

 

[RuralGuy]

Cross posted here: http://www.accessforums.net/programming/automatically-running-word-mailmerge-access-vba-40467.html

 

[pledbetter]

And here: http://www.utteraccess.com/forum/Mailmerge-Access-VBA-t2013300.html

 

[pledbetter]

It's interesting that multiple people can work on a problem on one forum without issue. But as soon as the issue is shared to another site we have Dudley Do-Right "telling" on you. For a community that works in a field that requires a high level of logic, this practice defies comprehension. The only response I got from my post is an answer I can't use. I think I have a right to find solution elsewhere if I can't find it here.

 

[RuralGuy]

The reason to let others know about your other postings: http://www.excelguru.ca/content.php?184

 

[pledbetter]

I was able to get a code snippet from http://support.microsoft.com/kb/209976/en-us to "sort of" get me where I need go. But when I run the code, the following happens:


When I try this approach, Word does not open up. But when I open my mailmerge document manually,
1. I get a prompt "The command cannot be performed because a dialog box is open". "Click OK, and then close open dialog boxes to continue"
2. When I click OK, I get the prompt to run the query at the data source" Opening this document will run the following SQL command: SELECT * FROM [My Table]
3. I get 2 instances of the completed mailmerge document: my original and another titled "Form Letters1". Both copies are correct, but they do not come up automatically, and require the manual running of the SELECT statement.

 

[pledbetter]

I am able to come up with this code:

Private Sub RunMailMerge()
Dim objWord As Word.Document
Set objWord = GetObject("C:\MCLAP\Buyer Agency - Mailmerge.docx", "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source
objWord.MailMerge.OpenDataSource _
Name:="C:\MCLAP\Current.accdb", _
LinkToSource:=True, _
Connection:="TABLE tblBAData", _
SQLStatement:="SELECT * FROM [tblBAData]"

' Execute the mail merge
objWord.MailMerge.Execute
End Sub


When I run this code, the following happens:

1. I get the prompt to run the query. I can live with that.
2. I get two copies of a correctly mail-merged document: my original document and another titled "Form Letters1"

How can I get one or the other NOT to display? Not just not be "not visible", but not to come up at all? I found out by trial and error that the document can be "there" but simply not visible, which can bite you if you are not aware of that "feature"

 

[pledbetter]

OK so here's the solution I came up with:

Private Sub RunMailMerge()
Dim txtMailMergeFile As String
Dim objWord As Word.Document
Dim i As Integer
txtMailMergeFile = DLookup("txtFilePath", "tblConstants") & DLookup("txtBAMailMergeFile", "tblConstants")

Set objWord = GetObject(txtMailMergeFile, "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source
objWord.MailMerge.OpenDataSource _
Name:="C:\MCLAP\Current.accdb", _
LinkToSource:=True, _
Connection:="TABLE tblBAData", _
SQLStatement:="SELECT * FROM [tblBAData]"

' Execute the mail merge
objWord.MailMerge.Execute

' Close any "extra" mail merge documents that may open
For i = 1 To objWord.Application.Documents.Count - 1
objWord.Application.Documents(i).Close wdSaveNo
Next i
End Sub



I have a single-row table called "tblConstants" that I use to store, obviously, constants like file paths, file names, and other things not likely to change much and are used throughout the database. Obviously I need to put in some error trapping, but this works pretty well for me. I never found a way for the datasource SELECT query to run automatically without the prompt and without having to do a registry hack but I can live with the prompt. I also realized that knowing the name of the "extra" mail merge document was irrelevant. I just wanted one to be visible. So I simply closed all but one of the existing Word documents. I suppose if you actually HAD a Word document you were using at the time it might close that too, but for me, it is not likely.




Thanks to all who helped me with this.