MailMerge under automation

[TedMi]

I create a mail-merge doc and attach a data source (query in Access db). When
opened manually, everything works fine -
ActiveDocument.MailMerge.State is 2 (MainandDataSource),
ActiveDocument.Mailmerge.DataSource.Name returns the proper string.

However, when I open with automation:
Set appWord = CreateObject("Word.Application")
appWord.Documents.Open "MyMerge.doc"

I get appWord.ActiveDocument.MailMerge.State is 0 (Normal doc),
ActiveDocument.Mailmerge.DataSource.Name is empty.

I can set the datasource in code, but why doesn't the one applied externally
stick? Is there some magic incantation of the Open method that I'm missing?
Thanks.

 

[Peter Jamieson]

You probably need to look at the following article:

http://support.microsoft.com/kb/825765/en-us

Peter Jamieson

 

[TedMi]

Thanks Peter, that article explains MS's intent and describes a workaround,
but the actual behavior is more complex. Turns out that if VBA creates an
instance of Word.Apllication and opens a mail-merge doc, the data source is
stripped off. But if VBA opens a mail-merge in an existing instance of Word
that was started by some other means than this instance of Access, then the
data source remains attached, and you get the dialog about SQL. Weird, or
what? Encountered in both O-XP and O-2003.

 

[Peter Jamieson]

I must say I don't think MS's article actually covers all the ground, but...

in Office 2003 I tried
a. make the registry patch as the article describes (actually, I normally
have the patch applied here so only see the other scenario when I
deliberately change it)
b. open a Word Mailmerge document that's set up with a.mdb as data source
c. open a.mdb
d. in a.mdb, I have a simple macro that does a Runcode with a function that
does the following (with the usual declarations etc. around it)

set objWord = GetObject(,"Word.Application")
set objDoc = objWord.Documents(1)
Msgbox objDoc.Mailmerge.DataSource.Name

Everything works fine. createobject followed by documents.open works OK.
Things tend to go wrong if the data source is in the database you're working
from and you run the function from the VBA Editor in Access, at which point
Access VBA (I think) wants an Exclusive open and has either got it (in which
case the Word OpenDataSource will fail) or can't get it because the Word
document already has access.

I can't say I've investigated any more thoroughly than that, though.

If your scenario is different again, I suppose I might be able to spot
something if you post the code.

Peter Jamieson

 

[TedMi]

Thanks for your efforts. I have it working to my satisfaction, applying
work-arounds in the VBA code to cover all contingencies. Users can run
multiple or repetitive merges from buttons on an Access form, and they will
all coexist in the same Word instance as separate docs. In this particular
app, the data sources are hard-wired in the VBA code, and the code opens the
datasource after opening the doc, so it doesn't matter if the doc loads with
source attached or not. In other apps, if the user would need to select the
source, I plan to present a drop down (in Access) for the user to pick from,
then have Access open the doc & attach the source. Here's my code:
Public Sub WordMerge(MergeDoc As String, Query As String, OutDoc As String)
' Use late binding to accomodate various versions of Word lib
Dim Doc As Object

' Varnames beginning "g" are global
On Error Resume Next
'Get instance of Word if already running
Set gWord = GetObject(, "Word.Application")
'If not running, create Word app
If Err Then Set gWord = CreateObject("Word.Application")
On Error GoTo 0
gWord.Visible = True
'Close this doc if open, to allow repeat of merge
For Each Doc In gWord.Documents
If Doc.Name = OutDoc Then
Doc.Close
Exit For
End If
Next
gWord.Documents.Open FileName:=gsPath & MergeDoc
gWord.ActiveDocument.MailMerge.OpenDataSource _
Name:=gsDB, _
LinkToSource:=True, _
SQLStatement:="SELECT * FROM `" & Query & "`"
gWord.ActiveDocument.MailMerge.Execute

' Execute has switched the active doc to the merge results
gWord.ActiveDocument.SaveAs gsPath & OutDoc
gWord.Documents(MergeDoc).Close SaveChanges:=True
gWord.Documents(OutDoc).Activate
End Sub

 

[Peter Jamieson]

I'm assuming you don't actually need any more assistance as everything is
working. Just one comment on your code - do you need to do this

gWord.Documents(MergeDoc).Close SaveChanges:=True

? Wouldn't discarding the changes be more reliable, so that you're always
starting from the same point?

gWord.Documents(MergeDoc).Close SaveChanges:=False

(NB, one consequence of saving data source info. in a mail merge main
document is that you will not be able to open the document without user
intervention if the data source is ever moved: it's far better to start with
documents that have no data source attached).

Peter Jamieson

 

[TedMi]

I started out discarding the changes, but upon user feedback reverted to
saving them. The reason is so users can rerun the last merge by opening the
doc in Word, without going through Access.
Thanks for the help.