G
Guest
I am using an small function that uses sendobject to send a customized report
to a series of email recipients. I recently created a modified version for a
different report and audience. For some reason, in the new version, as the
function loops trhu the table, sendobject only works for the first record.
The rest it skips with no error message. When I step through it, it does
step into the DoCmd.SendObject command, but nothing happens when it moves to
the next step. Has anyone seen this before??
Below is a copy of the code I am using:
++++++++++++++++++++++++++++++++++++++++++++++++
Option Compare Database
Option Explicit
Public strAuditReqWhere As String, strToEmail As String, strCCEmail As
String, strBCCEmail As String
'****************************************************************
'This function will walk through the AuditPullAgents table and Email the
'AuditReqRpt3 report, which is filtered by the AGENT field using
'the MS Access SendObject.
'
'This function assumes the current Audit Pull has been imported into the
ActiveAuditPull table,
'and that the appropriate queries have been run to prep the AuditPullAgents
table.
'****************************************************************
Function DirectFraudAuditEmails()
Dim dbsFraudRequestAutomation1 As DAO.Database
Dim rstAuditPullAgents As DAO.Recordset
Dim intStartHere As Integer
Set dbsFraudRequestAutomation1 = CurrentDb()
Set rstAuditPullAgents =
dbsFraudRequestAutomation1.OpenRecordset("AuditPullAgents", _
dbOpenDynaset)
intStartHere = 0
If MsgBox("Do you want to email Audit Requests" & Chr(13) & _
"to all Agents in the Active Audit Pull?", 4) = 6 Then
With rstAuditPullAgents
DoCmd.OpenReport "FraudAuditRequestRpt1", acViewDesign
Do Until .EOF
'Set strAuditReqWhere variable to current Agent Code
'so that the Reports! method can apply the filter.
strAuditReqWhere = ![Agent]
'Concatenate non-blank emails for the email recipient
Variables
strToEmail = ""
If ![Email1] <> "" Then strToEmail = "'" & ![Email1] & "'"
If ![Email1] <> "" And ![Email2] <> "" Then strToEmail =
strToEmail & " ; "
If ![Email2] <> "" Then strToEmail = strToEmail & "'" &
![Email2] & "'"
If ![Email3] <> "" And (![Email1] <> "" Or ![Email2] <> "")
Then strToEmail = strToEmail & " ; "
If ![Email3] <> "" Then strToEmail = strToEmail & ![Email3]
' "'" &
![Email3] & "'"
'---*Set strCCEmail=![CCEmail], this field is a list of the
IAMs emails for the Area
strCCEmail = ""
strBCCEmail = ""
'![CCEmail]
'Set a filter by setting the reports record source with a
SQL statement
'including a WHERE criteria based on the current agent code
in the strAuditReqWhere variable.
Reports!FraudAuditRequestRpt1.RecordSource = "SELECT
[ActiveAuditPull].* FROM [ActiveAuditPull] WHERE
((([ActiveAuditPull].[AGENT])= '" & strAuditReqWhere & "')); "
'A trigger step to allow the program to start AFTER a
specific agent code
'If intStartHere < 1 Then strToEmail = ""
'If strAuditReqWhere = "SCRVA12X" Then intStartHere =
intStartHere + 1
'Send the filtered report using SendObject to the recipients
for that agent code.
If (strToEmail <> "") Then 'Or strCCEmail <> "" Or
strBCCEmail <> ""
DoCmd.SendObject acSendReport, "FraudAuditRequestRpt1",
acFormatHTML, _
strToEmail, strCCEmail, strBCCEmail, ![AgentName] &
" (" & ![Agent] & ")" & ": " & "Fraud Audit Request for " & ![Region] & "
Region, " & ![AREA] & "November 2004", _
"Please find attached the November 2004 Fraud Audit
Request(s) from the Nextel Fraud and Order Compliance Team." & Chr(10) &
"Please be sure to open all attachments, as you may have multiple reports
attached.", _
True, "H:\Business Operations\Bus Ops Dbases\Audit
Request Automation\DirectFraudReqTemplate1.html"
Else
End If
.MoveNext
Loop
DoCmd.Close acReport, "FraudAuditRequestRpt1", acSaveNo
MsgBox "Audit Request Email Delivery Complete!", vbOKOnly
End With
End If
rstAuditPullAgents.Close
End Function
to a series of email recipients. I recently created a modified version for a
different report and audience. For some reason, in the new version, as the
function loops trhu the table, sendobject only works for the first record.
The rest it skips with no error message. When I step through it, it does
step into the DoCmd.SendObject command, but nothing happens when it moves to
the next step. Has anyone seen this before??
Below is a copy of the code I am using:
++++++++++++++++++++++++++++++++++++++++++++++++
Option Compare Database
Option Explicit
Public strAuditReqWhere As String, strToEmail As String, strCCEmail As
String, strBCCEmail As String
'****************************************************************
'This function will walk through the AuditPullAgents table and Email the
'AuditReqRpt3 report, which is filtered by the AGENT field using
'the MS Access SendObject.
'
'This function assumes the current Audit Pull has been imported into the
ActiveAuditPull table,
'and that the appropriate queries have been run to prep the AuditPullAgents
table.
'****************************************************************
Function DirectFraudAuditEmails()
Dim dbsFraudRequestAutomation1 As DAO.Database
Dim rstAuditPullAgents As DAO.Recordset
Dim intStartHere As Integer
Set dbsFraudRequestAutomation1 = CurrentDb()
Set rstAuditPullAgents =
dbsFraudRequestAutomation1.OpenRecordset("AuditPullAgents", _
dbOpenDynaset)
intStartHere = 0
If MsgBox("Do you want to email Audit Requests" & Chr(13) & _
"to all Agents in the Active Audit Pull?", 4) = 6 Then
With rstAuditPullAgents
DoCmd.OpenReport "FraudAuditRequestRpt1", acViewDesign
Do Until .EOF
'Set strAuditReqWhere variable to current Agent Code
'so that the Reports! method can apply the filter.
strAuditReqWhere = ![Agent]
'Concatenate non-blank emails for the email recipient
Variables
strToEmail = ""
If ![Email1] <> "" Then strToEmail = "'" & ![Email1] & "'"
If ![Email1] <> "" And ![Email2] <> "" Then strToEmail =
strToEmail & " ; "
If ![Email2] <> "" Then strToEmail = strToEmail & "'" &
![Email2] & "'"
If ![Email3] <> "" And (![Email1] <> "" Or ![Email2] <> "")
Then strToEmail = strToEmail & " ; "
If ![Email3] <> "" Then strToEmail = strToEmail & ![Email3]
' "'" &
![Email3] & "'"
'---*Set strCCEmail=![CCEmail], this field is a list of the
IAMs emails for the Area
strCCEmail = ""
strBCCEmail = ""
'![CCEmail]
'Set a filter by setting the reports record source with a
SQL statement
'including a WHERE criteria based on the current agent code
in the strAuditReqWhere variable.
Reports!FraudAuditRequestRpt1.RecordSource = "SELECT
[ActiveAuditPull].* FROM [ActiveAuditPull] WHERE
((([ActiveAuditPull].[AGENT])= '" & strAuditReqWhere & "')); "
'A trigger step to allow the program to start AFTER a
specific agent code
'If intStartHere < 1 Then strToEmail = ""
'If strAuditReqWhere = "SCRVA12X" Then intStartHere =
intStartHere + 1
'Send the filtered report using SendObject to the recipients
for that agent code.
If (strToEmail <> "") Then 'Or strCCEmail <> "" Or
strBCCEmail <> ""
DoCmd.SendObject acSendReport, "FraudAuditRequestRpt1",
acFormatHTML, _
strToEmail, strCCEmail, strBCCEmail, ![AgentName] &
" (" & ![Agent] & ")" & ": " & "Fraud Audit Request for " & ![Region] & "
Region, " & ![AREA] & "November 2004", _
"Please find attached the November 2004 Fraud Audit
Request(s) from the Nextel Fraud and Order Compliance Team." & Chr(10) &
"Please be sure to open all attachments, as you may have multiple reports
attached.", _
True, "H:\Business Operations\Bus Ops Dbases\Audit
Request Automation\DirectFraudReqTemplate1.html"
Else
End If
.MoveNext
Loop
DoCmd.Close acReport, "FraudAuditRequestRpt1", acSaveNo
MsgBox "Audit Request Email Delivery Complete!", vbOKOnly
End With
End If
rstAuditPullAgents.Close
End Function