Access 2003 to Word 2003 Mail Merge

[Ron B]

Help!!!

What am I doing wrong? I am working with Office 2003 and am trying to create a command button on an Access form that will create a mail merge in Word from an Access table.

I want to make the mail merge seamless to the user so all they have to do is click on the command button and the letters are generated.

For the button I created an event procedure with the following code:

Private Sub RunLetters_Click()

Dim objWord As Word.Document
Set objWord = GetObject("C:\Temp\Download\Access to Word\MERGE LETTER.doc", "Word.Document")

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

'Set Mail Merge Data Source
objWord.MailMerge.OpenDataSource NAME:="C:\Access2003\RonWorkingOn\Copy of NewVersion.mdb", _
LinkToSource:=True, _
Connection:="TABLE LETTER SENT PULL TABLE", _
SQLStatement:="SELECT * FROM LETTER SENT PULL TABLE"

'Execute the mail merge.
objWord.MailMerg.Execute

End Sub

When I click on the button I always get a Microsoft Office Word message box that says:
Opening this document will run the following SQL command:
SELECT * FROM [LENDER SENT PULL TABLE]
Data from your database will be place in the document. Do you want to continue?

I click on Yes then after a short wait Word opens and the master merge letter opens and I get another message box that says:
Confirm Data Source
OLE DB Database Files
MSAccess database via DDE(*mdb.*mde)
MSAccess database via ODB(*.mdb)

When I pick the 1st choice - nothing happens and I close Word and am back in Access and have a Microsoft Visual Basic message box that says:
Run-time error 4198
Command failed

When I pick the 2nd choice - again nothing happens and I close Word and am back in Access and have another Microsoft Visual Basic message box that says:
Run-time error 5922
Word was unable to open the data source

When I pick the 3rd choice - a message box comes up asking me to pick the Table and I click on the desired table.

Again the same thing happens as when I pick the 2nd choice.

Can anyone please help me? I have tried everything I could find and nothing is working.

Thanks,

Ron

[southoz]

check your PM's for an answer


southoz

[PEB]

It's interesting to have the answer also in the forum :)

check your PM's for an answer


southoz

[MMcCarthy]

Try changing this line to:

SQLStatement:="SELECT * FROM [LETTER SENT PULL TABLE];"

[Ron B]

To southoz,

Thanks for your reply. Unfortunately when I try to go to members.dodo.com/au/gjevers/gregw.zip I get Page does not exist.

Have I got the url wrong?

Ron

[Ron B]

To mmccarthy,

Thanks for your recommendation. I tried it and it did not work either.

I am desperate for any ideas so if you have any more please send them.

Thank again,

Ron

[MitchR]

Does Word 2003 have to house the form letter .... i have script that will generate a text email and send with you respective data from the form ....

[southoz]

Good ay Ron B

I have posted the code to this forum follow this link http://www.thescripts.com/forum/thread559954.html

I also have the fall demo database on my site http://members.dodo.com.au/gjevers/gregw.zip

hope this helps
southoz

[NeoPa]

Good 'ay SouthOz.

That's a good way to provide the answer - a reusable thread - I like it.