Access 2007 crashes when users cancel email while using SendObject

**zmbd** · Mar 4 '13, 10:53 PM

So... you send the email via macro.
Then before the macro can complete the action, the user closes the application (or attempts to)...
This is one of the reasons I avoid the macros in Access because they don't release the object or the shell, the Macro processor keeps a thread locked to the process and then throws a tantrum when something stalls the process.

The best I think you can do in Access2007 is attempt to error trap: OnError Macro Action

You can not do this in earlier versions.

**imahhua** · Mar 9 '13, 06:04 AM

Thank you zmbd,

But I tried to convert Macro into Module and add up Error handling phrases. The problem does not go away.

And I tried the same code but just to use a simpler report. the problem is gone. When I cancel the Email with "Navigation Pane" hided, I have chance to come back to access.

So I think that maybe the point is not to use macro or VBA code. maybe it is due to some thing from the report I used. But I am not sure if the problem is related to a macro which sets value of the report Caption while loaded. Or some other factors cause the conflict.

Let me do some test and come back with the news...any better idea?

**zmbd** · Mar 9 '13, 08:12 AM

How are you launching the code to send the email?

**zmbd** · Mar 9 '13, 08:24 AM

Also, I need to have the following:
Error Title:
Error Number:
Error Message:

These MUST be the EXACT text as shown in the error message.
Please do not paraphrase or omit any detail of the error message. I must have the EXACT contents of the error message.

I had not asked for this as I read "system crashes" and was thinking the entire PC/OS had locked-up; instead it appears that just Access has frozen.

**imahhua** · Mar 10 '13, 03:14 AM

Thank you for your time of looking into this.
I use the code below on a Email Button's Event Procedure to send the email:

Code:

Private Sub Send_Email_Click()

On Error GoTo EmailClient_Err
 
    DoCmd.OpenReport "Invoice", acViewPreview, "", "[BKID]=[Forms]![Booking]![BkID]", acNormal
    DoCmd.SendObject acReport, "Invoice", "PDFFormat(*.pdf)", Forms!Booking!Email, "", "", "Invoice # " & CStr(Forms!Booking!PaymentSales.Form!InvNum), Eval("""Hello "" & CStr([Forms]![Booking]![ContactName]) & "" , attached PDF is your invoice ."""), True, ""
 
 
EmailClient_Exit:
    Exit Sub
 
EmailClient_Err:
       MsgBox Error$
    Resume EmailClient_Exit
 
End Sub

Since I do not know how to get the Error Number, I can only provide the details as below.

After I cancel the E-mailing by click the "X" of outlook window, the first "Microsoft Office Access" window jumps out is like this:
Title: The SendObject action was canceled.
Message: You used a method of the DoCmd object to carry out an action in visual basic, but then clicked cancelling a dialog box.
For example, you used the close method to close a changed form, then clicked cancel in the dialog box that asks if you want to save the changes you made to the form.

After I click the "ok" button of the window above, the second window appears:
Title: The SendObject action was canceled.

Then I click "OK" again, Access 2007 has no reaction. when Click the "X" and try to close Access. The third Window comes out:
Title: You can not exit Microsoft Office Access now. Message: If you're running A Visual Basic Module that is running OLE or ODE, You may need to interrupt the module.

At this moment, I have to use "Alt+Ctr+De l" to interrupt.

**imahhua** · Mar 10 '13, 03:55 AM

ok...ZMBD, I did some test and probably found the point.
I use DoCmd.SendObjec t to send the report "Invoice".
And In the "invoice" report, I have the code below to open a form named "InvoiceTit le" and setvalue (Such as address,telepho ne,fax etc,.)to line1,2...4. So that I can make the Invoice title changeable according to agents' groups.

Code:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo InvTitle_Err

    DoCmd.OpenForm "InvoiceTitle", acNormal, "", "[Group]=[Forms]![Booking]![AgentGroup]", acReadOnly, acHidden
    Reports!Invoice!CompanyTitle.Caption = CStr(Forms!InvoiceTitle!CompanyTitle)
    Reports!Invoice!Line1.Caption = CStr(Forms!InvoiceTitle!Line1)
    Reports!Invoice!Line2.Caption = CStr(Forms!InvoiceTitle!Line2)
    Reports!Invoice!Line3.Caption = CStr(Forms!InvoiceTitle!Line3)
    Reports!Invoice!Line4.Caption = CStr(Forms!InvoiceTitle!Line4)
    DoCmd.Close acForm, "InvoiceTitle"

InvTitle_Exit:
    Exit Sub

InvTitle_Err:
    MsgBox Error$
    Resume InvTitle_Exit

End Sub

If I skip the code above, I do not have the problem of Access Frozen. Once I put the code back, the problem appears.

But I have no idea of what is wrong with the code and why the problem exists only when "Navigation Pane" is hided.

**zmbd** · Mar 10 '13, 06:28 AM

Wow, that's a lot different than what you started with - I'm going to have to re-think the entire trouble shooting process now.
You had led me to believe that your code was originally Macro Language based, not VBA, and then converted to VBA - wherein such is often called from an embedded macro and the trouble shooting is much different.

sigh.

In Access: MACRO is NOT the same as VBA
Microsoft made a HUGE mistake by using the term MACRO with Excel Workbooks when they are actually using VBA. This causes no end of confusion when people move between the programs and attempt to use the terms interchangeably .

For the moment:
In both of your codes (and I would say all of your code; however, it's a matter of choice):
Change your error message box from:
MsgBox Error$
to:

Code:

MsgBox "Calling Name: " & Me.Name & vbCrLf & "Error Number: " & Err.Number & vbCrLf & " Error Message: " & Err.Description

This will get us the error number and the description. Without that we're grasping at straws.

>Very important: After you change the error message code, do not change anything else.
Duplicate what you have done to receive the errors.
Report back the resulting error messages.

>Next, strictly for the purpose of debugging, in the code "Private Sub ReportHeader... "
Comment out (place a single quote ' ) line 11 (as labeled in post #7) where you are closing the hidden form. I've found, that one can not close any open form that the report is using until after the email is sent or canceled as the report is using that information. Hence why I really need that error number.

Once you've commented out Line 11 in the "Private Sub ReportHeader... " code, duplicate what you've done in the past to generate the errors.

after you've done all of this... let's just make sure some basic conditions are set:
please open the VBA editor back up,
menubar>tools>o ptions
When the options box opens:
[Editor] tab:
Uncheck the "Auto Syntax Check" - your erred code-lines will still turn red (or whatever colour you've set); however, you will no longer get that stupid and useless message box telling you that you made a mistake and click OK
Check mark the "Require Variable Declaration" - this will add the Option Explicit to your code modules and will now require that all variables be declared and classed using the DIM ... AS .... construct.
Now... this will not add the Option Explicit to existing modules, class modules, or forms. ONLY to NEWLY created modules.

Remaining options are your choice; however, I have them all selected.

[General] tab:
Error Trapping group box:
double check that "Break on Unhandled Errors" is the current selection. The other options will cause issues with our error trapping code.

The remaining defaults are usually ok.

Click on [OK] to save these changes.

OK, that's a lot for now... so let's see what happens.

**imahhua** · Mar 10 '13, 03:22 PM

I am appreciate your patience, ZMBD.
I have no knowledge of programming and use Macro originally. Since I would like to post my problem here and look for help, I convert my Macro into Module and Copy the Code to build Event Procedure. So that I can copy the code here. otherwise, I do not know how I can make my macro demonstrated clearly.

Your instruction is a little bite complicated for me. Let me do the first step for now,to change the message box code, and I will follow the rest of your instruction after that.

Step 1:
I changed both error message box code as you said, duplicated the same action and got the same information at the 1st and the 3rd windows jumping out.But at the 2nd Window,I got some info as follow:
Error Title: calling name: PaymentSales
Error Number: 2501
Error Message: The SendObject action was canceled.

"PaymentSal es" is the name of the subform in which I use the "Email" button to trigger SendObject action.

Other than error message box code,I did not change anything.

Then I will continue to follow the rest of your instruction and will come back here with the news.

**imahhua** · Mar 10 '13, 03:38 PM

Step 2:
As you said, I put a single quote at Line 11 to ignore the DoCmd.Close command.

And I do the same thing. I got only one error message window jumping out, which is the exactly same as the 2nd window I mentioned above:
Error Title: calling name: PaymentSales
Error Number: 2501
Error Message: The SendObject action was canceled.

But at this time, after I click "OK" of that window, I can come back to Access. The Problem is gone. Maybe that is the Point. I think that It is probably unnecessary to close the form. It is hidden anyway.

Or is there a better idea?

**zmbd** · Mar 11 '13, 02:11 AM

This is as I expected.
Canceling a sendobject action is considered an error.
In the original macro program there were three problems:
1) You were closing a form that an active report was using.
2) The sendobject method, when canceled, is considered an error.
3) You did not have any error trapping in place.
- These errors carried over in to the converted VBA code.

There's a very long explanation as to what was happening, and part of that is a guess; however, it boils down to this - one really should not close any forms, hidden or not, that an active report is using to pull information from when using it as part of the report used as an object for the SendObject method. You can place the command to close the forms (etc) for housekeeping needs in the on-close event of the report.

As for the 2051 error, there are ways of handling this error so that the user doesn't have to deal with it, or you can leave things as they are - which doesn't hurt anything.

**imahhua** · Mar 11 '13, 03:34 AM

Thank you ZMBD. Thank you for your time and patience to go over my question.Althou gh I am not clear why this error related to "Navigation Pane" , I am very happy to knock the problem off.

I have learned a lot from your suggestion. really appreciate!