script monkey: business solutions using digital intelligence 
Script Monkey blogs: MS Access SendOject Trapping Error 2501.
How to gracefully handle a user cancelling send when the SendObject Action is being used.
19th February 2022
I have had my head down the past few months writing a Stock Management Engine for a busy mechanical services company.
Finalising this database, I had a need to send an Access report to a preconfigured email address, so, I used the VBA SendObject method.
The report was compiled and presented to the user to edit before sending and worked perfectly, or so I thought. It turns out that if the user does not send the email and closes it instead, all manner of odd things occur.
Why does SendObject cause Error 2501 when a user cancels?
When SendObject opens the email for editing, Access hands control to your mail program and waits. If the user closes the email without sending, Access raises run-time error 2501 to report that the SendObject action was cancelled. It is not really a fault at all, just Access reporting that the user changed their mind, but unless it is handled carefully it looks and behaves like one.
The first thing I noticed was that despite my own error trapping catching that an error had fired (in this case Error 2501), Access still threw its own error message to the user which looked ugly and made it necessary to acknowledge the problem twice. The second thing I noticed was that when I allowed the users to use the system in the .accdb front end, after acknowledging the error, Access appeared to crash, then entirely locked up and could only be terminated through the task manager. Not very user friendly.
How do you stop Access showing its own error message?
It took a surprisingly long time to wrap my head around what was going on, but eventually I got there! For the SendObject error to be handled gracefully when a SendObject call was embedded in a form, I found I needed a Form_Error event handler:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 2501 Then
'Debug.Print "Error: [" & DataErr & "], quietly trapped."
Err.Clear
Response = acDataErrContinue
Else
'Debug.Print "Error: [" & DataErr & "], quietly trapped."
Response = acDataErrContinue
End If
End SubThis dealt with the SendObject Error 2501 firing due to a user cancel, but did not solve the lock-up issue. I needed to understand why Access was refusing to communicate after this error was handled. Counterintuitively, the cause turned out to be that I always hide the navbar when offering forms to users as it looks nicer.
This dealt with the SendObject Error 2501 firing due to a user cancel in some circumstances, but not all.
How do you handle other SendObject mail errors?
I then added procedure level error handling to deal with mail issues (including the cancel 2501):
btnMailApproval_Click_Err:
Select Case Err.Number
Case 2046
Debug.Print Err.Description
MsgBox "There is a problem with the mail system on this computer or no mail system is installed." & vbcrlf & vbcrlf & _
"Please contact your network administrator." , vbCritical, "Email Program Missing?"
Case 2296
MsgBox "You cancelled the mail or you are not allowed to send mail.", vbInformation, "Email Cancelled"
Case 2501
MsgBox "You cancelled the email, so it will not be sent. ", vbInformation, "Email not sent"
Err.Clear
Case Else
Errorlog "Forms_" & Me.Name & ":btnAuthorityApproval_Click"
End Select
Resume btnMailApproval_Click_ExitBetween them, these three cases cover the problems users most often run into: no working mail program on the machine, the mail being refused, and the user simply cancelling. Anything else is passed to a central error log, so unexpected problems are recorded rather than lost.
Why does Access lock up after SendObject is cancelled?
Now I get nice error handling, but still have a lock-up / crash when the user cancels the SendObject action, really annoying. The solution eventually revealed itself to be unhiding and rehiding the navbar...
' This code is important to allow the sendObject code to error correctly if user cancels - without the nav bar, access locks
DoCmd.SelectObject acTable, "System_Config", True ' This forces the nav pane to be visible
DoCmd.Minimize
DoCmd.SendObject acSendReport, "Orders", ...
btnAuthorityApproval_Click_Exit:
' The following two lines of code hides the nav bar again
DoCmd.SelectObject acTable, "System_Config", True ' This forces the nav pane to be visible
DoCmd.RunCommand acCmdWindowHide
This code is safe in a compiled environment or in a standard Access file (.accdb) and will always return control after SendObject is cancelled by the user.
Are there alternatives to SendObject?
SendObject is quick to set up, but it gives you limited control over the email and relies on whatever mail program is installed. If your users all have Outlook, automating Outlook directly from VBA gives you far more control over recipients, attachments and formatting, and a cancelled email no longer raises an Access error at all. The trade-off is a little more code and a dependency on Outlook being present. For simple, occasional emails, SendObject with the handling above is perfectly adequate.
If you are wrestling with Access, Excel or VBA problems of your own, our bespoke database development service may help, and for a lighter take on bugs that refuse to die, read a day in the life of a coder.
