Windows 7 
Access 2007
Automated Mail Merge
Hello all,
I have been following a tutorial from tek-tips about automating the access-word mail merge process.
The query I am using for the merge is a parameter query in which the user can select between two dates to choose patients' records who have had an appointment in this time. This pulls their name and address for the merge, which is to send out labels (for appointment reminders).
I am kind of a newbie at VB but I am getting there! I have the following code which runs when you press a button on a form. So far, all that happens is that Word opens. I wonder if any of you can take a look and see if I am doing anything wrong? TIA!
Option Compare Database
Option Explicit
Private Sub SetQuery(strqryMM As String, strSQL As String)
On Error GoTo ErrorHandler
'set the query from which the merge
' document will pull its info
Dim qdfNewQueryDef As QueryDef
Set qdfNewQueryDef = CurrentDb.QueryDefs(strqryMM)
qdfNewQueryDef.SQL = strSQL
qdfNewQueryDef.Close
RefreshDatabaseWindow
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
Private Sub cmdMergeIt_Click()
'creates an SQL statement to be used in the query def
On Error GoTo ErrorHandler
' user enters a zip code in a text box on the form;
' the query's criteria is set to pull records for
'that zip code
Dim strSQL As String
'replace the SQL statement below with the SQL statement
'from your query. This sample shows how to use single quotes
'to incorporate string values from the form's fields
'into the SQL statement. For dates, use # instead of the
'single quotes
strSQL = "SELECT tblAppointments.ApptDate, tblCustomers.CustomerAddress, tblAppointments.ApptCustomerName FROM tblCustomers INNER JOIN tblAppointments ON tblCustomers.CustomerName = tblAppointments.ApptCustomerName WHERE (((tblAppointments.ApptDate) Between [Type the beginning date] And [Type the ending date]));"
Dim strMailMerge As String 'name of the Word template document
strMailMerge = "C:\Users\Celinae\Desktop\MailMerge.dot"
'use your template document name above
Call SetQuery("qryMM", strSQL)
'use your query name above
Dim strLabels As String 'name to use when saving
'the merged document
'this next line of code makes the document name pattern
'like this: Custom Labels January 11, 2005.doc
strLabels = "Custom Labels " & Format(CStr(Date), "MMM dd yyyy")
'use your file name pattern above
Call OpenMergedDoc(strMailMerge, strSQL, strLabels)
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred. " & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strMergedDocName As String)
On Error GoTo WordError
'opens an instance of Word, opens a merge template which has its data source
'already linked to a query in the database,
'optional code merges the template to a new document,
'saves the merged file with a descriptive name,
'then closes the merge template
'Set the directory for any labels generated
Const strDir As String = "C:\Users\Celinae\Desktop\"
'use your directory and folder name above
Dim objWord As New Word.Application
Dim objDoc As Word.Document
objWord.Application.Visible = True
Set objDoc = objWord.Documents.Open(strDir & strDocName)
' Make Word visible so that if any errors occur,
' you can close the instance of Word manually
objWord.Application.Visible = True
'*optional code to merge to a new document, save the merged document, and close the template goes here*
'release the variables
Set objWord = Nothing
Set objDoc = Nothing
Exit Sub
WordError:
MsgBox "Err #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
objWord.Quit
End Sub
Reply With Quote
