Automated Mail Merge from Access (2000)

Topic

New Reply

I have questions regarding the automation of a Word MailMerge from Access.

I am working on a project to allow the Case Managers working in a database I built for our Welfare-to-work program to generate routine correspondence easily. These included correspondence with the client, or with HRA, or the client’s WEP site, or the clients employer.

(One point – the letters are always generated one at a time.)

I operate the merge (no saved datasource because instalations vary…)

strMergeType comes from the selected template, which then sets the Path, Connection, & SQL variables accordingly

Select Case strMergeType
Case “Test Merge Template”
strPath = “S:KarlIAWMergeTemplatesTestMergeTemplate2.doc”
strConnection = “QUERY qryMergeTestMerge”
strSQL = “SELECT * FROM qryMergeTestMerge WHERE PersonID = ” & numPersonID
End Select

Set objWord = CreateObject(“Word.Application”)
objWord.Application.Visible = True
Set doc = objWord.Documents.Open(strPath)
doc.MailMerge.OpenDataSource Name:=CurrentDb.Name, LinkToSource:=True, _
&Connection:=strConnection, SQLStatement:=strSQL
doc.MailMerge.Execute

And just getting this far takes a long time – well over a minute, but I have no clue why. How can I make this run faster.

My other question, which I haven’t tackled yet because I want to find out why it is slow first, is how do I then manipulate the new document that I just merged to save it/etc.

Any help/advice would be greatly appreciated.
-Karl

Reply | Quote

Replies

If you’re running over a network, I would recommend that you export the data for the merge to a tab delimited text file and then make that file the data source for the mail merge. That will reduce quite a bit of overhead, since you won’t have to reprocess the the query or the SQL against it. In fact, even if you aren’t running over a network, the text file will be much faster.

Reply | Quote

That sounds like a good idea – I am indeed running this over a network. I spent some time last night testing different approaches, and the linking of the merge source sometimes would take over two and a half minutes… I’ll give this a try.

Thank you Charlotte.

best,
Karl

Reply | Quote

That sounds like a good idea – I am indeed running this over a network. I spent some time last night testing different approaches, and the linking of the merge source sometimes would take over two and a half minutes… I’ll give this a try.

Thank you Charlotte.

best,
Karl

Reply | Quote

If you’re running over a network, I would recommend that you export the data for the merge to a tab delimited text file and then make that file the data source for the mail merge. That will reduce quite a bit of overhead, since you won’t have to reprocess the the query or the SQL against it. In fact, even if you aren’t running over a network, the text file will be much faster.

Reply | Quote