SendObject method does not send, no error message

L

Lance

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
 
D

Dirk Goldgar

Lance said:
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

Are you using Access 2000, by any chance? If so, see this MS
KnowledgeBase article about a bug:

http://support.microsoft.com/default.aspx?scid=kb;en-us;260819
SendObject method fails in Access 2000

The bug is fixed by service packs, and I think it no longer occurs in
later versions, though I could be mistaken.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top