Mailmerge help

[jketcher]

Getting a compile error 'user defined type not defined' on the 'Dim objWord as Word Document'

I researched the internet and found that I might have a problem with the Object Library from the References tab when the Module sheet is open. My Access installation has 'Microsoft DAO 3.6 Object Library' checked. I tried the test again and, of course, it failed again since I had not changed anything.

The merge is using data from a Make Table query and a merge document located on my C: drive. The code is below... Help is greatly appreciated!

Thanks, jketcher


Private Sub Create_Report_Click()
On Error GoTo Err_Create_Report_Click
Dim objWord As Word.Application
Set objWord = GetObject("C:\Documents and Settings\jketcher\My Documents\Janis Folder November 6\ECG\MS Access Projects\ECGOpReview", _
"Word.Document")
'Make Word visible.
objWord.Application.Visible = True
'Set the mail merge data source as
objWord.MailMerge.OpenDataSource _
Name:="tbl_merge_findings"

'Execute the mail merge
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

' Save and close the new document

objWord.Application.Options.SaveInterval = False
objWord.Application.ActiveDocument.SaveAs "OpReview_MailMerge"
objWord.Application.ActiveDocument.Close
objWord.Application.Quit wdDoNotSaveChanges

MsgBox "Mail Merge Complete!", vbOKOnly, "File Done"
DoCmd.Quit


Exit_Create_Report_Click:
Exit Sub

 

[Kiwiman]

Howzit

You may want to check if the "Microsoft Word 11.0 Object Library" is also selected.

 

[jketcher]

Thanks -- but I got that piece working. I added the Object 11.0 library and that line of code worked. I now have a new problem and I sent a message to the Forms Forum. I am now getting an error on objWord.MailMerge.OpenDataSource in the following code. Is there something wrong with my syntax? I thought I followed the model I have. I am using a Make Table query and was hoping I could just name the query without the path since the database is open when I am executing this code. Your help is greatly appreciated.

jketcher

Private Sub Create_Report_Click()
On Error GoTo Err_Create_Report_Click

Dim objWord As Word.Application
Set objWord = GetObject("C:\Documents and Settings\jketcher\My Documents\Janis Folder November 6\ECG\MS Access Projects\ECGOpReview", _
"Word.Document")
'Make Word visible.
objWord.Application.Visible = True
'Set the mail merge data source as
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\jketcher\My Documents\Janis Folder November 6\ECG\MS Access Projects\OpReviewDBV8TestReport.mdb", _
LinkToSource:=True, Connection:="TABLE tblMergeFindings", _
SQLStatement:="SELECT * FROM[tblMergeFindings]"

'Execute the mail merge
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

' Save and close the new document

objWord.Application.Options.SaveInterval = False
objWord.Application.ActiveDocument.SaveAs "OpReview_MailMerge"
objWord.Application.ActiveDocument.Close
objWord.Application.Quit wdDoNotSaveChanges

MsgBox "Mail Merge Complete!", vbOKOnly, "File Done"
DoCmd.Quit


Exit_Create_Report_Click:
Exit Sub

 

[Kiwiman]

Howzit

It looks fine compared to the microsoft example - see below. The only problem I can see is the missing space in the sql stmt

Private Sub Create_Report_Click()
On Error GoTo Err_Create_Report_Click

Dim objWord As Word.Application
Set objWord = GetObject("C:\Documents and Settings\jketcher\My Documents\Janis Folder November 6\ECG\MS Access Projects\ECGOpReview", _
"Word.Document")
'Make Word visible.
objWord.Application.Visible = True
'Set the mail merge data source as
objWord.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\jketcher\My Documents\Janis Folder November 6\ECG\MS Access Projects\OpReviewDBV8TestReport.mdb", _
LinkToSource:=True, Connection:="TABLE tblMergeFindings", _
SQLStatement:="SELECT * FROM [tblMergeFindings]"

'Execute the mail merge
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

' Save and close the new document

objWord.Application.Options.SaveInterval = False
objWord.Application.ActiveDocument.SaveAs "OpReview_MailMerge"
objWord.Application.ActiveDocument.Close
objWord.Application.Quit wdDoNotSaveChanges

MsgBox "Mail Merge Complete!", vbOKOnly, "File Done"
DoCmd.Quit


Exit_Create_Report_Click:
Exit Sub

MS Example

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:="C:\Program Files\Microsoft " & _
"Office\Office\Samples\Northwind.mdb", _
LinkToSource:=True, _
Connection:="TABLE Customers", _
SQLStatement:="SELECT * FROM [Customers]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

found at the following site:

http://support.microsoft.com/kb/209976

 

[jketcher]

I changed the code a little - moved the database to the Program Files folder and it still hits the same error message. There really is a space in the code, the copy just makes it look like there isn't one. Does it make any difference that I am running in Access 2007?

Recently tested code:


Dim objWord As Word.Application
Set objWord = GetObject("C:\Documents and Settings\jketcher\My Documents\Janis Folder November 6\ECG\MS Access Projects\ECGOpReview", _
"Word.Document")
'Make Word visible.
objWord.Application.Visible = True
'Set the mail merge data source as
objWord.MailMerge.OpenDataSource _
Name:="C:\Program Files\Microsoft Office\Office12\OpReviewDBV8TestReport.mdb", _
LinkToSource:=True, _
Connection:="TABLE tblMergeFindings", _
SQLStatement:="SELECT * FROM [tblMergeFindings]"



'Execute the mail merge
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

 

[Kiwiman]

Howzit

Look at this sample database posted by ajerumpet. If you put the db and the word doc in C:\, it will work.

This will point you in the right direction.

You shouldn't need to put the file in the Program Files folder, as long as you specify the correct path etc.

http://www.access-programmers.co.uk/forums/showthread.php?t=158126