SendObject random failure

[Valeriya]

Hi guys. I'm having trouble with the following code. Sometimes the emails do get sent and at other times they don't. There is no pattern to that problem. Also, is there any way to insure that empty reports do not get sent? I'm using Microsoft Outlook 2003 and my database is an MS Access 2000. I've noticed that there a lot of problems with SendObject action when there are more than 70 characters in the body of the email, however, in my case the body of the email is blank. What do you thing the problem is? Here is the code:

Private Sub Command72_Click()
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strMsg As String


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Assignee.ID, Assignee.[E-mail Address] FROM Assignee")

While Not rst.EOF
DoCmd.OpenReport "Open Project Details", acViewPreview, , "[Project Lead]=" & rst.Fields(0)

DoCmd.SendObject acSendReport, "Open Project Details", acFormatSNP, rst.Fields(1), , , "Your Spinal Supply Chain Projects by Status", True, False

DoCmd.Close acReport, "Open Project Details", acSaveNo

rst.MoveNext


Wend
rst.Close
Set rst = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
   strMsg = "Error number is: " & Err.Number & vbCrLf
   strMsg = strMsg & "Error message is: " & Err.Description & vbCrLf
   strMsg = strMsg & "Error source is: " & Err.Source
   MsgBox strMsg, vbOKOnly + vbCritical, "Error"
   Resume Next



End Sub

Thank you!

Valeriya

 

[Remou]

Perhaps something wrong with the email address?

As for empty reports, if you cancel in the NoData event of the report, you will get an error 2501. You could test for this error before SendObject. Alternatively, just use DlookUp or DCount to check if report data is available.

 

[Valeriya]

Hi Remou. I tried to use NoData event, however, it still does not work properly. When I click the button (on the form)to send reports I do get error 2501, just like you told me. I click "OK" so the remaining reports get sent until I get another empty one and so on until the last report is sent. When
I opened Outlooked and checked "Send Messages" I found the following problem. The email addresses that were not supposed to get reports(since there was no data for them)get the report that was for the first recipient in the record set. Another words they get a report that does not belong to them. All the other ones work fine. The problematic ones are those that were not supposed to get an email at all. I know there is a problem with logic in my code, however, I do not see it. Could you please look at the code and pin point were exactly my logic fails? Here is my code...I hope I was clear in my explanation of the problem...Thank you for all your help!!!

Private Sub Command72_Click()
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strMsg As String


Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT Assignee.ID, Assignee.[E-mail Address] FROM Assignee")

While Not rst.EOF

    DoCmd.OpenReport "Open Project Details", acViewPreview, , "[Project Lead]=" & rst.Fields(0)

    If Err.Number = 2501 Then
    
     MsgBox "There is no data in the report, Please click OK"
    
    Else
    DoCmd.SendObject acSendReport, "Open Project Details", acFormatSNP, rst.Fields(1), , , "This is a Test!!! Your Spinal Supply Chain Projects by Status", True, False
    DoCmd.Close acReport, "Open Project Details", acSaveNo
    
    End If

rst.MoveNext

Wend

rst.Close
Set rst = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
   strMsg = "Error number is: " & Err.Number & vbCrLf
   strMsg = strMsg & "Error message is: " & Err.Description & vbCrLf
   strMsg = strMsg & "Error source is: " & Err.Source
   MsgBox strMsg, vbOKOnly + vbCritical, "Error"
   Resume Next



End Sub

 

[Remou]

I should have put DCount as the first option, which woud look something like:

If DCount("*","Open Project Details","[Project Lead]=" & rst.Fields(0)) = 0
    MsgBox "There is no data in the report, Please click OK"
Else
    DoCmd.SendObject acSendReport, "Open Project Details", acFormatSNP, rst.Fields(1), , , "This is a Test!!! Your Spinal Supply Chain Projects by Status", , True, False
End If

However, if you want to go with No Data, I think you will need these changes:

While Not rst.EOF

    DoCmd.OpenReport "Open Project Details", acViewPreview, , "[Project Lead]=" & rst.Fields(0)
    DoCmd.Close acReport, "Open Project Details"
    
    'If Err.Number = 2501 Then
    
     'MsgBox "There is no data in the report, Please click OK"
    
    'Else
    DoCmd.SendObject acSendReport, "Open Project Details", acFormatSNP, rst.Fields(1), , , "This is a Test!!! Your Spinal Supply Chain Projects by Status", , True, False
    'DoCmd.Close acReport, "Open Project Details", acSaveNo
    
    'End If

SkipToHere:
rst.MoveNext

Wend

rst.Close
Set rst = Nothing
Set db = Nothing

Exit Sub

ErrorHandler:
   If Err.Number = 2501 Then
        'MsgBox "There is no data in the report, Please click OK"
        DoCmd.Close acReport, "Open Project Details"
        Err.Clear
        Resume SkipToHere
   End If
   strMsg = "Error number is: " & Err.Number & vbCrLf
   strMsg = strMsg & "Error message is: " & Err.Description & vbCrLf
   strMsg = strMsg & "Error source is: " & Err.Source
   MsgBox strMsg, vbOKOnly + vbCritical, "Error"
   Resume Next
End Sub

I think you are missing a comma at the end of DoCmd.SendObject acSendReport, which I have put in:
[tt]... Status", , True, False[/tt]

You have:
[tt]... Status", , True, False[/tt]

 

[Valeriya]

I will try both. Thank you very much for your kind help!
Valeriya

 

[Valeriya]

Hi Remou,

I tried both. Neither one works. DCount can't look up data in report. From what I could find on it is that it can only look up/count records in table or query. As far as the other approuch, it sends reports to the people that are not intended to receive the report. Another words it just sends to whoever is in the record set.

Thanks,

Valeriya

 

[Remou]

It seemed to me that the report must be based on a query and that, due to access naming, the query was likely to have the same name as the report. Is it not possible for you to use DCount on the table that the report is based on?

As for the second idea, if you still wish to try it, please post the code that you are using, with any changes you made.