All, using access 2010. Attached is a snippet of code from an “Email Module”. Everything works but I have two reports that attaches to the email and in a folder. As of now; both reports are attached to a corresponding email. But I want to use the reports (either rpt1 or rpt2) to attach to the email based on criteria from a query. I tried below an “IF Then Else” Statement. When I run the code; only the second rpt(rpt2) attaches even though both criteria are available the qry used. (Bolded below) I can’t find why. Help please.
Code:
' Loop through each broker in the "tblBroker" table - determining which brokers
' need to receive e-mails. For each eligible broker, create the necessary
' report file(s), and attach those files to a new e-mail for that broker.
Set rstBrokers = CurrentDb().OpenRecordset("EA01_qryBroker_email1")
With rstBrokers
' Get the total e-mail count.
.MoveLast
intNumEmailsToCreate = Nz(.RecordCount, 0)
.MoveFirst
' Loop through the brokers - creating an e-mail (with attached reports) for each broker.
Do While Not .EOF
' Create the e-mail.
If (Not g_blnTestMode) Or (intNumEmailsCreated <= 2) Then
' Make broker name filename-ready.
strScrubbedBrokerName = Replace(!Bus_Name, ".", "")
strState = Replace(!state_abbr, ".", "")
' Set current broker ID (used by the reports' underlying queries).
g_varCurrentBrokerID = !client_id
[B] If ("[EA01_qryBroker_email1]).[letter_code]" = "NEW") Then
' Create the report #1.
strPathAndFilename_Report1 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt1_email", acFormatPDF, strPathAndFilename_Report1, False
DoEvents ' Allow this operation to be fully completed before proceeding.
Else
' Create the report #2.
strPathAndFilename_Report2 = strPathToStatementFiles & strReportFilenamePrefix & strScrubbedBrokerName & " " & strState & " " & varCheckIssueDate & ".pdf"
DoCmd.OutputTo acOutputReport, "rpt2_email", acFormatPDF, strPathAndFilename_Report2, False
DoEvents ' Allow this operation to be fully completed before proceeding.
End If[/B]
' Create the e-mail subject.
strSubject = strSubjectPrefix & !Bus_Name
' Get a new copy of the template.
varHTMLBody = varHTMLBody_StaticTemplate
.MoveNext
Loop
' Close the recordset.
.Close
End With
Set rstBrokers = Nothing
End If