Mail Merge from a Query (1 Viewer)

Right. I have setup a sucessful mail merge based in data in a table. It works great.

Now, I want to adapt this so that the mail merge only takes data for specific people. i ahve created a query that picks the specific people out. I have adapted the code of the mail merge to be based on the query, not the table as in the first merge. However, it isn't working. I can't for the life of me see why. Please take a look and open my eyes to the stupid thing that I have overlooked! Here it is:

Option Compare Database
Option Explicit

Private Function CreateMergeDoc(UseDDE As Boolean, PrintDoc As Boolean)
'------------------------------------------------------------------------------------------------
' This code is used to open MS Word and display an empty document
'------------------------------------------------------------------------------------------------
On Error GoTo Err_CreateMergeDoc

Dim WordApp As Word.Application
Dim WordDoc As Word.Document
Dim strLetter As String
Dim strConnect As String
Dim db As DAO.Database
Set db = CurrentDb()

' Create an instance of Microsoft Word 97.
Set WordApp = CreateObject("Word.Application")

' Create a new, empty document.
Set WordDoc = WordApp.Documents.Add

' Show the instance of Microsoft Word.
WordApp.Visible = True

Exit_CreateMergeDoc:
Exit Function

Err_CreateMergeDoc:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_CreateMergeDoc

End Function
Private Function CreateMerge()
'------------------------------------------------------------------------------------------------
' This code is used to open MS Word and then open a specific Word document used to mail merge
' with the table tblMailMerge. The path of the Word document is stored in the table ztblReports
' and is accessed through the list box lstMailMerge
'------------------------------------------------------------------------------------------------

Dim objWord As Word.Document
Dim db As DAO.Database
Set db = CurrentDb()
Dim strPath As String
strPath = Me.lstMailMerge.Column(2)
Set objWord = GetObject(strPath, "Word.Document")

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

' Create a DDE connection and merge the data in the MM query
objWord.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
linktosource:=True, _
Connection:="QUERY empunit-mm-head", _
SQLStatement:="Select * FROM [empunit-mm-head]"
objWord.MailMerge.Execute

End Function

Private Sub cmdCreateLetter_Click()
On Error GoTo Err_cmdCreateLetter_Click

CreateMergeDoc UseDDE:=False, PrintDoc:=False

Exit_cmdCreateLetter_Click:
Exit Sub

Err_cmdCreateLetter_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_cmdCreateLetter_Click

End Sub

Private Sub lstMailMerge_Click()
'------------------------------------------------------------------------------------------------
' This code is used tocreate the data set used in the mail merge. It first checks to see if an
' event is required to be selected followed by the cleaning of the tblMailMerge table anf then
' the append query is launch to gather the data and append it into the tblMailMerge table. The
' last step is to call the CreateMerge function.
'------------------------------------------------------------------------------------------------
On Error GoTo Err_lstMailMerge_Click


Call CreateMerge



Exit_lstMailMerge_Click:
Exit Sub

Err_lstMailMerge_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_lstMailMerge_Click

End Sub

I am getting the message 'File name or class name not found during the automation process'.

Many thanks,
Scott.

[This message has been edited by expublish (edited 03-07-2002).]

I knew it was simple. I had the row source as:

ztblReports

when it should have been:

SELECT [ztblReports].[ReportName], [ztblReports].[ReportTitle], [ztblReports].[DocPath], [ztblReports].[Menu] FROM ztblReports WHERE ((([ztblReports].[Menu])="Merge")) ORDER BY [ztblReports].[ReportTitle];

Thank you Scott for solving your own problem. You really are the greatest! ( )