Unwanted dialog when performing a mail merge

[Cooz]

Hi everyone,

This VBA function in Access accomplishes a mail merge in Word (Office 2003):


Function MMWord(DotName As String, QueryName As String)
Dim blnWordAlreadyRunning As Boolean
Dim strSQL As String

On Error GoTo ErrorTrap

blnWordAlreadyRunning = True
Set objWord = GetObject(, "Word.Application") ' Error 429 if Word was
not open

With objWord
.Documents.Open path & DotName
strSQL = "SELECT * FROM [" & QueryName & "]"
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:=path &
"Sollicitaties.mdb", SQLStatement:=strSQL

....

ErrorTrap:
Select Case Err.Number
Case 429 ' ActiveX cannot make object
Set objWord = CreateObject("Word.Application") ' start Word
blnWordAlreadyRunning = False
Resume Next ' continue

Case Else
Error Err.Number
End Select

End Function


"..." stands for code that is working fine and that is not particularly
relevant here. "path" holds the correct path to Sollicitaties.mdb.
DotName is the name of a Word-document that contains mergefields and no VBA
Code. QueryName is name of the data source, and yes, that's a query.

All lines of code function flawlessly up until the one directly above "..."
– the one with ".OpenDataSource". This line coughs up a dialog called
"Confirm data source" (or rather "Gegevensbestand bevestigen" since I use the
Dutch version of Access) where I am forced to select a mail merge method:
"OLE-DB Database files", "MS-Access databases via DDE (*.mdb, *.mde)" and
others.

How can I prevent this dialog from appearing? In addition, error 4198
"Command failed"/"Opdracht mislukt" occurs when I click OK. Until this
morning it seemed that the macro worked fine if the query did not contain a
WHERE-clause – however today I came across a situation in which the dialog
appeared even though the query was WHERE-clauseless. Bummer.

The following provides no solution:
- Use DDE (.OpenDataSource SubType:=wdMergeSubTypeWord2000)
- strSQL = CurrentDB.QueryDefs(strQueryIntern).SQL (and tweak strSQL in such
a way – remove the ";" at the end and such – that .OpenDataSource can work
with it)
- letting Word perform the mail merge – this yields the same result.
- In Word: (Un)check Tools | Options... | tab General | Confirm conversions
on open.

Any help in this matter will be greatly appreciated.

Thank you,
Cooz

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?Q29veg==?=,

Looking at the list of things you've tried...

When you say "letting Word perform the mail merge" what do you mean, more
exactly?

Fact: the OpenDataSource method's code is inadequate to connect with Access via
the default OLE DB. You're missing the necessary connection information, which is
why you're getting the prompt.

Normally, I'd tell you: record a Word macro while making the connection in the
Word UI, then check the result to get the missing information.

But your remark about Word giving the same result makes me hesitate. Try
recording the macro, anyway and show me the result?

If the above hasn't helped, please show me the SQL of the Access query to which
you're connecting.

This VBA function in Access accomplishes a mail merge in Word (Office 2003):


Function MMWord(DotName As String, QueryName As String)
Dim blnWordAlreadyRunning As Boolean
Dim strSQL As String

On Error GoTo ErrorTrap

blnWordAlreadyRunning = True
Set objWord = GetObject(, "Word.Application") ' Error 429 if Word was
not open

With objWord
.Documents.Open path & DotName
strSQL = "SELECT * FROM [" & QueryName & "]"
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:=path &
"Sollicitaties.mdb", SQLStatement:=strSQL

....

ErrorTrap:
Select Case Err.Number
Case 429 ' ActiveX cannot make object
Set objWord = CreateObject("Word.Application") ' start Word
blnWordAlreadyRunning = False
Resume Next ' continue

Case Else
Error Err.Number
End Select

End Function


"..." stands for code that is working fine and that is not particularly
relevant here. "path" holds the correct path to Sollicitaties.mdb.
DotName is the name of a Word-document that contains mergefields and no VBA
Code. QueryName is name of the data source, and yes, that's a query.

All lines of code function flawlessly up until the one directly above "..."
– the one with ".OpenDataSource". This line coughs up a dialog called
"Confirm data source" (or rather "Gegevensbestand bevestigen" since I use the
Dutch version of Access) where I am forced to select a mail merge method:
"OLE-DB Database files", "MS-Access databases via DDE (*.mdb, *.mde)" and
others.

How can I prevent this dialog from appearing? In addition, error 4198
"Command failed"/"Opdracht mislukt" occurs when I click OK. Until this
morning it seemed that the macro worked fine if the query did not contain a
WHERE-clause – however today I came across a situation in which the dialog
appeared even though the query was WHERE-clauseless. Bummer.

The following provides no solution:
- Use DDE (.OpenDataSource SubType:=wdMergeSubTypeWord2000)
- strSQL = CurrentDB.QueryDefs(strQueryIntern).SQL (and tweak strSQL in such
a way – remove the ";" at the end and such – that .OpenDataSource can work
with it)
- letting Word perform the mail merge – this yields the same result.
- In Word: (Un)check Tools | Options... | tab General | Confirm conversions
on open.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Cooz]

Hi Cindy,

"letting Word perform the mail merge" - move all code that actually performs
the merge to the Word template. Access creates a new Word document based on
the template and stops executing code; code in an AutoNew macro in the Word
template takes over and perfoms the mail merge.

Recording the macro in Word yields a large connectionstring which I happily
deleted, glad to be able to simplify the code. This was not as foolish as it
seems, since Microsoft states in http://support.microsoft.com/kb/289830:
"ODSO expects an SQL query for the data source in the SQLStatement argument
of OpenDataSource and ignores the Connection argument." (I wanted to get rid
of the Select Table dialog in the first place, which led me to this article)
Indeed, further on a code example of OpenDataSource with only the Name and
the SQLStatement arguments is given.

Your suggestion to "record a Word macro" and "check the result to get the
missing information" made me insert the Connection argument again, in spite
of article 289830. And it works.

I believe it would be wise if Microsoft reviewed 289830. While leaving out
the Connection argument actually works in some situations (all of which
involved a query data source that did not have a WHERE clause), the article
should not imply that the Connection argument can be left out just like that.

Thank you very much for your help.

Kind regards,
Cooz

"Cindy M -WordMVP-" schreef:

Hi =?Utf-8?B?Q29veg==?=,

Looking at the list of things you've tried...

When you say "letting Word perform the mail merge" what do you mean, more
exactly?

Fact: the OpenDataSource method's code is inadequate to connect with Access via
the default OLE DB. You're missing the necessary connection information, which is
why you're getting the prompt.

Normally, I'd tell you: record a Word macro while making the connection in the
Word UI, then check the result to get the missing information.

But your remark about Word giving the same result makes me hesitate. Try
recording the macro, anyway and show me the result?

If the above hasn't helped, please show me the SQL of the Access query to which
you're connecting.

This VBA function in Access accomplishes a mail merge in Word (Office 2003):


Function MMWord(DotName As String, QueryName As String)
Dim blnWordAlreadyRunning As Boolean
Dim strSQL As String

On Error GoTo ErrorTrap

blnWordAlreadyRunning = True
Set objWord = GetObject(, "Word.Application") ' Error 429 if Word was
not open

With objWord
.Documents.Open path & DotName
strSQL = "SELECT * FROM [" & QueryName & "]"
.ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
.ActiveDocument.MailMerge.OpenDataSource Name:=path &
"Sollicitaties.mdb", SQLStatement:=strSQL

....

ErrorTrap:
Select Case Err.Number
Case 429 ' ActiveX cannot make object
Set objWord = CreateObject("Word.Application") ' start Word
blnWordAlreadyRunning = False
Resume Next ' continue

Case Else
Error Err.Number
End Select

End Function


"..." stands for code that is working fine and that is not particularly
relevant here. "path" holds the correct path to Sollicitaties.mdb.
DotName is the name of a Word-document that contains mergefields and no VBA
Code. QueryName is name of the data source, and yes, that's a query.

All lines of code function flawlessly up until the one directly above "..."
– the one with ".OpenDataSource". This line coughs up a dialog called
"Confirm data source" (or rather "Gegevensbestand bevestigen" since I use the
Dutch version of Access) where I am forced to select a mail merge method:
"OLE-DB Database files", "MS-Access databases via DDE (*.mdb, *.mde)" and
others.

How can I prevent this dialog from appearing? In addition, error 4198
"Command failed"/"Opdracht mislukt" occurs when I click OK. Until this
morning it seemed that the macro worked fine if the query did not contain a
WHERE-clause – however today I came across a situation in which the dialog
appeared even though the query was WHERE-clauseless. Bummer.

The following provides no solution:
- Use DDE (.OpenDataSource SubType:=wdMergeSubTypeWord2000)
- strSQL = CurrentDB.QueryDefs(strQueryIntern).SQL (and tweak strSQL in such
a way – remove the ";" at the end and such – that .OpenDataSource can work
with it)
- letting Word perform the mail merge – this yields the same result.
- In Word: (Un)check Tools | Options... | tab General | Confirm conversions
on open.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)


This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Cindy M -WordMVP-]

Hi Cooz,

Your suggestion to "record a Word macro" and "check the result to get the
missing information" made me insert the Connection argument again, in spite
of article 289830. And it works.

Glad to hear your project is moving along, again

If you're interested in all the nitty-gritty, nasty details of making
connections, I highly recommend the mailmerge.fields newsgroup. Look for Peter
Jamieson. He's put in a lot of time, dissecting how things work. Peter might
even be able to explain why it doesn't work with a WHERE clause...

I believe it would be wise if Microsoft reviewed 289830.

I've passed this along...

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail