I am trying to action a mail merge from a button on a form.
I've entered code onto my form as shown below, but it is failing at almost the first hurdle.
I get the error message "Compile Error: Variable not defined" on the line ..
Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
The Code I have entered is triggered by the On Click code and is shown below ..
Don't know if this is relevant, but when I tried to Set a reference to Microsoft DAO (which is currently unchecked) I get a message stating that this conflicts with an existing module, project, or object library !!!
Any help would be appreciated.
Code:
Option Compare Database
Option Explicit
Private Sub SetQuery(strQueryName As String, strSQL As String)
On Error GoTo ErrorHandler
'set the query from which the merge document will pull its info
Dim qdyNewQueryDef As QueryDef
Set qdfNewQueryDef = CurrentDb.QueryDefs(strQueryName)
qdfNewQueryDef.SQL = strSQL
qdfNewQueryDef.Close
RefreshDatabaseWindow
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
Private Sub Sub_Reminder_1_Click()
On Error GoTo ErrorHandler
Dim strSQL As String
strSQL = "SELECT Member_Names.Membership_Number, Member_Names.[Full Name], Member_Names.[Address Line 1], Member_Names.[Address Line 2], Member_Names.Town, Member_Names.Region, Member_Names.Country, Member_Names.[Post Code], Member_Names.Last_Payment, Member_Names.Status, Member_Names.Membership_Class, Member_Names.Salutation, Member_Names.Envelope, Member_Names.Pay_by_SO FROM Member_Names WHERE (((Member_Names.Last_Payment)<#12/31/2016#) AND ((Member_Names.Status)='Current') AND ((Member_Names.Membership_Class)='Ordinary' Or (Member_Names.Membership_Class)='Family (Lead)' Or (Member_Names.Membership_Class)='Senior' Or (Member_Names.Membership_Class)='Associate' Or (Member_Names.Membership_Class)='Corporate'))"
Dim strDocumentName As String 'name of word template document
strDocumentName = "C:\Users\User5\Documents\LUCS\TestReminder1.docx"
Call SetQuery("Sub_Reminder_Query", strSQL)
Dim strNewName As String
strNewName = "Reminder Letters " & Format(CStr(Date), "dd MMM yyyy")
Call OpenMergedDoc(strDocumentName, strSQL, strNewName)
Exit Sub
ErrorHandler:
MsgBox "Error #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Error"
Exit Sub
End Sub
'Open Word Document etc
Private Sub OpenMergedDoc(strDocName As String, strSQL As String, strMergedDocName As String)
On Error GoTo WordError
'Save the merged document to this directory
Const strDir As String = "C:\Users\User5\Documents\LUCS"
Dim objWord As New Word.Application
Dim objDoc As Word.Document
objWord.Application.Visible = True
Set objDoc = objWord.Documents.Open(strDir & strDocName)
objWord.Application.Visible = True
'release the variables
Set objWord = Nothing
Set objDoc = Nothing
Exit Sub
WordError:
MsgBox "Error #" & Err.Number & " occurred." & Err.Description, vbOKOnly, "Word Error"
objWord.Quit
Exit Sub
End Sub