Access 2007 Query data to Mailmerge

[jketcher]

My assignment is to create a button on a form that will activate a query that creates data for a mail merge document. This has to be done in mail merge. Would someone please give some guidance. My ultimate assignment is to try to get this to work with one button press from start to finish but right now I would be happy if I could get the selected data to display on the word document. Do any of you have expertise with this?Thanks!

 

[HiTechCoach]

See:

How to use automation to run Word 2000 mail merge from Access 2000

 

[jketcher]

Thanks -- I assume this same process/model will work for Access and Word 2007?

 

[HiTechCoach]

Since you said "My assignment ..." does this mean that this is for a class/course you are taking?

 

[jketcher]

No -- the assignment from my manager. I work for a small company that has no tech support for VBA.

 

[HiTechCoach]

You might want to also check out:

Super Easy Mail Merge
*** You will have to scroll down to find it.

 

[jketcher]

I set this up and need more assistance. I have never worked with Functions, which the example you gave uses. I have to use a button to run a query and then merge the output to a word document. These are not mailing addresses. I can't use the wizard. I have another example in a book that does not use a Function to do this. Not sure which to use. I tried combining the two examples I have without success.

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

Would you please review this code and help me make the changes to make it work?

Thanks, jketcher


Private Sub Create_Report_Click()
( tried to add Function MergeIt() here) and after the compile error it disappeared.)
On Error GoTo Err_Create_Report_Click
'Dim stDocName As String
'stDocName = "qry_op_review_rpt_merge"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Dim objWord As Word.Document
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:="qry_op_review_rpt_merge"
objWord.MailMerge.Execute

Exit_Create_Report_Click:
Exit Sub
Err_Create_Report_Click:
MsgBox Err.description
Resume Exit_Create_Report_Click

End Function
End Sub

 

[jketcher]

Hi,

I did not get a response from my last question (posed in the VBA forum also). Wondering why? The examples given to me were partially helpful but not fully applicable to this design. I have progressed a little. I hope you are still willing to assist me. Would like to get this project done so the users can begin use. I have successfully completed the merge using the wizard but my company wants users to be able to press one button to create the table and do the merge.

I am now getting an error on the objWord.MailMerge.OpenDataSource. I am trying to get the path stated correctly. It is a long one. Do you think that is the problem? Is there a reason I can't just name the make table query that has already been executed without the long path? I tried it and it didn't work but am hopeful. The data is all ready to merge at the point where the objWord.MailMerge.OpenDataSource code is executed...

Do you have ideas for why I am getting the compile error on that 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:\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

Thank you so much for your assistance,

jketcher