Mail Merge from Access

[justme0010]

I have set up Mail Merge to execute from within Microsoft Access using
the Word 9 object library like http://support.microsoft.com/kb/209976
shows.

My issue is that this opens up another instance of the database, which
I don't want and it makes the mail merging much slower. Is there a way
to execute mail merge from Access without opening another instance of
Access?

 

[Peter Jamieson]

Word 2000, right?

See also

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

If you want to use ODBC programmatically, assuming ODBC is installed on your
system, you would need code more like the following (starting from the code
in that KB article): However, be aware that ODBC will not "see" certain
types of query, e.g. parameter queries.

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\MyMerge.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the Northwind database.

objWord.MailMerge.OpenDataSource _
Name:="", _
Connection:="DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;", _
SQLStatement:="SELECT * FROM [Customers]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

You will need to get the correct DSN name from the ODBC Administrator on
your system.

Precisely what else needs to go in the Connection parameter depends on your
set-up. For example, if you are using Workgroup security, you will need
something more like

Connection:="DSN=MS Access Database;" & _
"DBQ=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;" & _
"SystemDB=c:\mywgdb\Secured.mdw;UID=myloginname;PWD=mypassword;", _

Peter Jamieson

 

[justme0010]

Thanks- that helped a lot. That problem has gone away.

Although now, I am getting random errors happening 30% of the time
(using the exact same queries) saying that the database is exclusively
locked or the recordset is empty when it shouldnt be. I am using a temp
table that I insert with data from a query using ADODB execution. Could
there be a delay in filling the temp table and trying to retrieve that
data for the mail merge??

 

[Peter Jamieson]

Can you post your code here? (Are you for example closing all the relevant
ADO recordsets and connections after you have created the temp table? Are
you opening the Word document with its ODBC data source attached?). Not sure
when I wil be able to reply to this as I may be out of circulation for a
while though...

 

[justme0010]

Well, here's the code if anyone sees a problem. Like I said before, this
works about 75-80% of the time.

Dim strSQL As String
Dim objWord As Word.Document
Dim ContainingFolder As String 'Full folder path that this database
resides in
Dim cnn As ADODB.Connection

ContainingFolder = CurrentProject.Path
Set cnn = CurrentProject.Connection

'Make sure there are no other records in temp table
cnn.Execute "DELETE * FROM EnvelopeTemp"

'SQLCriteria is a global variable
strSQL = "INSERT INTO EnvelopeTemp (BusinessName, Street, City, State,
Zip, Country) " & _
"SELECT BusinessName, Street, City, State, Zip, Country
" & _
"FROM [tblContactInfo] WHERE " & SQLCriteria

'Fill temp table with addresses
cnn.Execute strSQL

cnn.Close
Set cnn = Nothing

Set objWord = GetObject(ContainingFolder & "\HT System Blank
Template.doc", "Word.Document")

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

' Set the mail merge data source as this database
objWord.MailMerge.OpenDataSource _
Name:="", _
ReadOnly:=True, _
Connection:="DSN=MS Access Database;" & _
"DBQ=" & ContainingFolder & "\HT System.mdb", _
SQLStatement:="SELECT * FROM [EnvelopeTemp] ORDER BY [Country] DESC,
[BusinessName] ASC"

'Execute the mail merge.
objWord.MailMerge.Execute

'Close main document, don't save changes
objWord.Close False

 

[justme0010]

I also notice that after a merge is executed and the main document is
automatically closed, when I open up the main document it has the
connection information in it, even though I specified the document to
not save during the close method.

Can I make sure that the connection information is not saved in the
main (template) document or is erased somehow? Could that help me with
this problem?

 

[Peter Jamieson]

Try changing

cnn.Execute strSQL

to

cnn.Execute strSQL,,adCmdText+adExecuteNoRecords

I have a suspicion that what happens otherwise is that ADO DB creates a
recordset that has locking implications and is not necessarily cleaned up by
the cnn.Close or perhaps Set cnn = Nothing. (FWIW the Access VBA help says
that a cnn.Close has no effect on the actual connection in this case
anyway - at least I think that's what it says.

 

[Peter Jamieson]

I get the same here, but am not completely sure of the cause. What seems to
clear it up here is to create a new Word application object and do
everything inside that. But it may also have something to do with the
Activate calls that I've put in the following version of your macro which
seems (so far) to work OK here.

Sub mymacro()

Dim strSQL As String
Dim objWordApp As Word.Application
Dim objWord As Word.Document
Dim ContainingFolder As String 'Full folder path that this database
resides in
Dim cnn As ADODB.Connection

ContainingFolder = CurrentProject.Path
Set cnn = CurrentProject.Connection

'Make sure there are no other records in temp table
cnn.Execute "DELETE * FROM EnvelopeTemp"


'SQLCriteria is a global variable
SQLCriteria = "1 = 1"
strSQL = "INSERT INTO EnvelopeTemp (BusinessName, Street, City, State,Zip,
Country) " & _
"SELECT BusinessName, Street, City, State, Zip, Country " & _
"FROM [tblContactInfo] WHERE " & SQLCriteria

'Fill temp table with addresses
cnn.Execute strSQL, , adcmtxt + adExecuteNoRecords


cnn.Close
'Set cnn = Nothing
Set objWordApp = CreateObject("Word.Application")
Set objWord = objWordApp.Documents.Open(ContainingFolder & "\HT System Blank
Template.doc")

' Make Word visible.
objWordApp.Visible = True
objWordApp.Activate

' Set the mail merge data source as this database
objWord.MailMerge.OpenDataSource _
Name:="", _
ReadOnly:=True, _
Connection:="DSN=MS Access Database;" & _
"DBQ=" & ContainingFolder & "\HT System.mdb", _
SQLStatement:="SELECT * FROM [EnvelopeTemp] ORDER BY [Country]
DESC,[BusinessName] ASC"

'Execute the mail merge.
objWord.MailMerge.Execute
objWord.Activate
'Close main document, don't save changes (not sure you need all these
Activates but
' maybe you can see if this works OK there, and pare it down a bit).
objWord.Close wdDoNotSaveChanges
Set objWord = Nothing
objWordApp.Quit wdDoNotSaveChanges
Set objWordApp = Nothing
End Sub

 

[justme0010]

OK, I've tried out your code except I don't use the part where it exit
Word. I want Word to remain open because I want to still see th
envelope addresses but I just want the template part (main document
closed. It still tells me the database cannot be opened around the 8t
execution (after 7 smooth executions). I dont know if its because o
not exitting Word but I would like to keep Word open until the use
exits it.

Yes, if worst comes to worst I will have to find a way to create th
main document entirely in code. Thanks again for the immense help

 

[Peter Jamieson]

1. Did you just remove the line with the Quit? i.e. is the Set objWordApp =
Nothing still in there?
2. Maybe you also need to set cnn = Nothing. I can't remember what happens
in that case!
3. If you are still stuck, I can have another look, but I am probably near
the end of my ability to debug this one.