Script Monkey Blog – 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 off things occur.

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.

I decided to resolve this issue:

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 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 Sub

This 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 communicated after this error was handled. Counterintuitively, the solution was 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.
I then added procedure level error handling to deal with mail issues (inlcuding 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_Exit

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.