How do I capture an Docmd.send object when sending an email?

seanlhall
seanlhall used Ask the Experts™
on
I have a button that creates an email in MS Outlook with .pdf attachment from a report. Everything is fine if you send the email, or cancel it one time. If you attempt to send it again and cancel it you get the following error.

You used a method of the DoCmd object to carry out an action in VB, but then clicked Cancel in a dialog box. For example, you used the close method to close a changed for, then clicked Cancel in te dialog box that asks if you want to save the changes you made to the form.

Private Sub Command443_Click()
On Error GoTo ErrorControl_Click_Err
If Me.Dirty = True Then Me.Dirty = False
Dim Report As String
Report = Nz(Me.cmbreportname, "InvoiceLH")
Dim strBody As String
Dim clientemail As String
Dim usercompany As String
Dim useroffice As String
    clientemail = DLookup("[email]", "tblclients", "[clientid]  = Form![comboclient]")
    usercompany = DLookup("[companyname]", "tblusersettings")
    useroffice = DLookup("[officenumber]", "tblusersettings")
'Email Client with attachment'
    strBody = "Dear valued client," & vbCrLf & vbCrLf & _
    "Your invoice is attached. Please remit payment at your earliest convenience." & vbCrLf & vbCrLf & _
    "IF YOU BELIEVE YOU HAVE RECEIVED THIS IN ERROR, PLEASE NOTIFY OUR OFFICE VIA PHONE " & useroffice & vbCrLf & vbCrLf & _
    "Thank you for your business we appreciate it very much." & vbCrLf & vbCrLf & _
    "Sub-Total:" & Format(Me.Text165, "$#,##0.00") & vbCrLf & _
    "Tax:" & Format(Me.invoicetotal1, "$#,##0.00") & vbCrLf & _
    "Total Paid:" & Format(Me.discount1, "$#,##0.00") & vbCrLf & _
    "Total Due:" & Format(Me.Text194, "$#,##0.00") & vbCrLf & vbCrLf & _
    "Sincerely," & vbCrLf & vbCrLf & _
    usercompany
DoCmd.OpenReport Report, acPreview, , "[invoiceid]=" & Me![invoiceid]
DoCmd.SendObject acSendReport, Report, acFormatPDF, _
    "" & clientemail, _
    , _
    , _
    "Notification from " & usercompany, _
    "" & strBody, _
    True

ErrorControl_Click_Exit:
    Exit Sub
    
   
ErrorControl_Click_Err:
DoCmd.Echo True
Select Case Err.Number
Case 2501
    MsgBox "The email was canceled and has not been sent", , "Email Delivery Canceled"
Case Else
    MsgBox Err.Number & Err.description
   
End Select
    
Resume ErrorControl_Click_Exit

End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Probably because the open report is "hanging around".

Try closing the report in the error handler and other appropriate places.  You can even close it immediately befor the docmd.open acReport, just to be safe.  If you don't need to see the report, then you can open it with acHidden instead of acPreview.

Case 2501
DoCmd.Close acReport, Report

Author

Commented:
Ok the report now closed on canceling the email, but I get a message form Access it is the following:

You can't exit Database now. If you're running a VB module that is using OLD or DDE, you may need to interrupt the module.

Author

Commented:
I put in the code to close the report just after the  Docmd.send object and it works now. Thanks.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial