I have a query (qry_receipts) which contains the members email addresses. I can create an individual receipt (rpt_receipts) for each member and then I want to be able to email their receipt as a PDF to only that member. This means that the members email address created by the query (tempQry) should be substituted into strEmail each time it is run for a different member.
Can someone help me understand where my logic is incorrect.
Private Sub cmd_CGN_pick_Click()
Dim strQueryCGN As String 'Select CGN from list
Dim strReport As String 'Create report based on CGN selection
Dim strText As String 'prompt to create report
Dim strTitle As String
Dim strSQL As String
Dim qdf As QueryDefs
Dim strEmail As String
strQueryCGN = "qry_receipts"
strReport = "rpt_receipts"
strText = "Do you want to create a receipt"
strTitle = "Receipt of Payment"
strEmail = "tempQry"
DoCmd.OpenQuery strQueryCGN, acViewNormal, acReadOnly
If MsgBox(strText, vbCritical + vbYesNo, strTitle) = vbYes Then
DoCmd.OpenReport strReport, acViewPreview
End If
DoCmd.SetWarnings False ' turns off popup warnings
strSQL = "SELECT qry_receipts.Email FROM qry_receipts;"
On Error Resume Next
DoCmd.DeleteObject acQuery, "tempQry"
' On Error GoTo 0
Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
DoCmd.OpenQuery ("tempQry")
DoCmd.SendObject acReport, strReport, acFormatPDF, strEmail, "", "", "NQCG Payment Receipt", "This Receipt is for 2019 fees", False, ""
DoCmd.SetWarnings True ' turns pop up warning back on
End Sub
Can someone help me understand where my logic is incorrect.
Private Sub cmd_CGN_pick_Click()
Dim strQueryCGN As String 'Select CGN from list
Dim strReport As String 'Create report based on CGN selection
Dim strText As String 'prompt to create report
Dim strTitle As String
Dim strSQL As String
Dim qdf As QueryDefs
Dim strEmail As String
strQueryCGN = "qry_receipts"
strReport = "rpt_receipts"
strText = "Do you want to create a receipt"
strTitle = "Receipt of Payment"
strEmail = "tempQry"
DoCmd.OpenQuery strQueryCGN, acViewNormal, acReadOnly
If MsgBox(strText, vbCritical + vbYesNo, strTitle) = vbYes Then
DoCmd.OpenReport strReport, acViewPreview
End If
DoCmd.SetWarnings False ' turns off popup warnings
strSQL = "SELECT qry_receipts.Email FROM qry_receipts;"
On Error Resume Next
DoCmd.DeleteObject acQuery, "tempQry"
' On Error GoTo 0
Set qdf = CurrentDb.CreateQueryDef("tempQry", strSQL)
DoCmd.OpenQuery ("tempQry")
DoCmd.SendObject acReport, strReport, acFormatPDF, strEmail, "", "", "NQCG Payment Receipt", "This Receipt is for 2019 fees", False, ""
DoCmd.SetWarnings True ' turns pop up warning back on
End Sub