Automate Mail Merge From Access

[primagic]

I have the following code which opens word successfully and merges the data but only after I get prompted to select a table. Is there a way around this. I am merging into Word 2003 from Access 2003.

Function MergeIt()

On Error GoTo ErrHandling

Dim objDoc As Word.Document
Dim objWord As Word.Application
Dim blnCreated As Boolean
Dim strFilename As String
Dim strQueryName As String
Dim strDBpath As String

strFilename = "D:\test\Master Due Diligence.docx"
strQueryName = "qryDDMergeReport"
strDBpath = "D:\test\DD.mdb"

On Error Resume Next
Set objWord = GetObject("Word.Application")
If Err Then
Set objWord = CreateObject("Word.Application")
blnCreated = True
End If

On Error GoTo ErrHandling

Set objDoc = objWord.Documents.Open(strFilename)

' Make Word visible.
objWord.Visible = True
'Execute the Mail Merge
With objDoc.MailMerge

' Set the mail merge data source.
objDoc.MailMerge.OpenDataSource Name:=strDBpath, LinkToSource:=True, Connection:="QUERY" & strQueryName, SQLStatement:="SELECT * FROM" & strQueryName
.Destination = wdSendToNewDocument
.Execute
End With


Exit Function

ErrHandling:
MsgBox Err.Description
End Function

 

[RoyVidar]

I'd suggest having a look at Albert Kallal's "Super Easy Word Merge" here

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

It circumvents the usual problems by creating a textfile which Word will use for the merge, so no message box, and no attaching to the running database file.

Roy-Vidar

 

[cree2me]

Hey,

Just to save some time from having to code this. It can be accomplished from a Word template.

This is from MS Office Word 2007:

1) Open your form letter to be sent out, Save as template.
2) Click on Mailings tab
3) Select Recipients -> Use Existing List
4) Click on NewDataSource
5) Browse to find your database, a connection will made automatically
6) Select the table or query
7) Select the fields from Insert Merge Fields. The database fields will be displayed.

I dont know much about mail merge, so you'll have to figure out that.

Glen