sendobject on cancel error

[Ruegen]

I have a button with

On Error GoTo ErrorHandler
If IsNull(Me.TeacherEmail) Or IsNull(Me.TeacherName) Or IsNull(Me.TeacherSurname) Or IsNull(Me.TheatreSeats) Or IsNull(Me.TheatreTeachersAttending) Then
MsgBox "You require a teacher email, name and surname, and the seating in order to email the confirmation"
Else
DoCmd.SendObject acReport, "rptConfirmationTAXTheatreEdit", "PDFFormat(*.pdf)", Me.TeacherEmail, , , "School Performance Tours - Booking Confirmation", , True
End If



ErrorHandler:
Select Case Err
Case 2501
MsgBox "You have cancelled the email"


End Select

I keep getting an error if the person cancels the email in access runtime "cancel error" then the database freezes. I thought my errorhandler would cover that but it seems to not.

I'm not sure if it is my placement and I am hoping someone can notice.

[ItsMe]

How is the user cancelling? What does "True" do at the end of DoCmd? Is this where the user is cancelling?

[Ruegen]

The error handling code always runs, even when there is no error. Need an Exit branch.

Exit_sub:
DoCmd.Close acForm, "frmBookingTheatreSearch", acSaveYes
Exit Sub

ErrorHandler:
Select Case Err
Case 2501
MsgBox "You have cancelled the email"
Resume Exit_sub
End Select

I did not know. I didn't have one on the code below:

Option Compare Database


'------------------------------------------------------------
' btnNewBookingEmail_Click
'
'------------------------------------------------------------
Private Sub btnNewBookingEmail_Click()


AlternativeName = IIf(Me.SchoolTypeID = 3, " or the Director, ", IIf(Me.SchoolTypeID = 9, " ", " or the Principal,"))


TourOrganiserORPeter = IIf(Me.MergedNameT = Null, "Name" & vbCrLf & "Manager", Me.MergedNameT & vbCrLf & vbCrLf & "Tour Organiser")


emailmsgconfirm = "Dear " & Me.MergedName & AlternativeName _
& vbCrLf _
& vbCrLf _
& "Thank you for including us in your cultural calendar.  " _
& vbCrLf _
& vbCrLf _
& "The confirmation of your booking is attached as a PDF file.  " _
& vbCrLf _
& vbCrLf _
& "Please sign and return it to Name by Email Fax or Post. " _
& vbCrLf _
& vbCrLf _
& "Please remember if you need to change or cancel this booking for any reason we require 28 days notice from the date of the performance and if cancelling a cancellation number will be issued at the time of cancellation." _
& vbCrLf _
& vbCrLf _
& "We trust you and your students will enjoy the performance." _
& vbCrLf _
& vbCrLf _
& "Regards" _
& vbCrLf _
& vbCrLf _
& TourOrganiserORPeter _
& vbCrLf & "Name" & vbCrLf & "address" & vbCrLf & "MARRICKVILLE NSW  2204" & vbCrLf & "Ph number" & vbCrLf & "Email  email"




On Error GoTo ErrorHandler




If IsNull(Me.TeacherEmail) Or IsNull(SchoolEmail) Then
MsgBox "You require an email in the teacher email field to email the school"




'code that stops user from not filling out all required fields
ElseIf IsNull(Me!frmSubBookingsControl.Form!BookingDate) Or IsNull(Me!frmSubBookingsControl.Form!ShowsIDDrop) Or IsNull(Me!frmSubBookingsControl.Form!BookingMinimum) Or IsNull(Me!frmSubBookingsControl.Form!ShowTime1st) Or IsNull(Me!frmSubBookingsControl.Form!StatusID) Then
MsgBox "You must give a Booking Date, Show, Booking mimimum, at least one Show Time and Show Status. Please ensure you have filled these fields"


'code that sends email
Else


    DoCmd.SendObject acReport, "rptNewBookingEmail", "PDFFormat(*.pdf)", TeacherEmail, SchoolEmail, , "name", emailmsgconfirm, True
    Me.ConfirmationSent1st = True
    Me.frmSubBookingsControl.Form![ContactName] = Me.TeacherName
    Me.frmSubBookingsControl.Form![ContactSurname] = Me.TeacherSurname
    DoCmd.Close acForm, "frmBookingNew", acSaveYes
    
End If
'This code detects error if you don't send the email and displays an alert
ErrorHandler:
Select Case Err
Case 2501
MsgBox "You have cancelled the email"


End Select










End Sub

and I never encountered that error... would love to know why

[Ruegen]

How is the user cancelling? What does "True" do at the end of DoCmd? Is this where the user is cancelling?

True is for editing the email (rather than just sending it off, it's a way of checking what they are sending is correct before they send it).

Yes user is closing the email program (in this particular case outlook 2010)

[ssanfu]

Unless there is more code, you never cancel the email; in fact there is not even an option to cancel the email!
Thus you never get to the error handler. And canceling the email is not an error! So, again, the error handler would not come into play.

I made some changes (in BLUE)

On Error GoTo ErrorHandler
If IsNull(Me.TeacherEmail) Or IsNull(Me.TeacherName) Or IsNull(Me.TeacherSurname) Or IsNull(Me.TheatreSeats) Or IsNull(Me.TheatreTeachersAttending) Then
  MsgBox "You require a teacher email, name and surname, and the seating in order to email the confirmation"

Else
  DoCmd.SendObject acReport, "rptConfirmationTAXTheatreEdit", "PDFFormat(*.pdf)", Me.TeacherEmail, , , "School Performance Tours - Booking Confirmation", , True
End If


DoCmd.Close acForm, "frmBookingTheatreSearch"  '  , acSaveYes

ExitHere:
Exit Sub '(or Exit Function)

ErrorHandler:
Select Case Err.Number
  Case 2501
   MsgBox "You have cancelled the email"

End Select

Resume ExitHere

The "acSaveYes" is not needed. "acSaveYes" is used when a form is opened in design mode using VBA and modified.
To close a form, use

DoCmd.Close acForm, "frmBookingTheatreSearch"

[Ruegen]

Unless there is more code, you never cancel the email; in fact there is not even an option to cancel the email!
Thus you never get to the error handler. And canceling the email is not an error! So, again, the error handler would not come into play.

I made some changes (in BLUE)

On Error GoTo ErrorHandler
If IsNull(Me.TeacherEmail) Or IsNull(Me.TeacherName) Or IsNull(Me.TeacherSurname) Or IsNull(Me.TheatreSeats) Or IsNull(Me.TheatreTeachersAttending) Then
  MsgBox "You require a teacher email, name and surname, and the seating in order to email the confirmation"

Else
  DoCmd.SendObject acReport, "rptConfirmationTAXTheatreEdit", "PDFFormat(*.pdf)", Me.TeacherEmail, , , "School Performance Tours - Booking Confirmation", , True
End If


DoCmd.Close acForm, "frmBookingTheatreSearch"  '  , acSaveYes

ExitHere:
Exit Sub '(or Exit Function)

ErrorHandler:
Select Case Err.Number
  Case 2501
   MsgBox "You have cancelled the email"

End Select

Resume ExitHere

The "acSaveYes" is not needed. "acSaveYes" is used when a form is opened in design mode using VBA and modified.
To close a form, use

DoCmd.Close acForm, "frmBookingTheatreSearch"

Thanks, I changed it to

Private Sub btnEmailConfirm_Click()
On Error GoTo ErrorHandler


If IsNull(Me.TeacherEmail) Or IsNull(Me.TeacherName) Or IsNull(Me.TeacherSurname) Or IsNull(Me.TheatreSeats) Or IsNull(Me.TheatreTeachersAttending) Then
MsgBox "You require a teacher email, name and surname, and the seating in order to email the confirmation"
Else
DoCmd.SendObject acReport, "rptConfirmationTAXTheatre", "PDFFormat(*.pdf)", Me.TeacherEmail, , , "School Performance Tours - Booking Confirmation", , True
 End If






ExitHere:
Exit Sub '(or Exit Function)


ErrorHandler:
Select Case Err.Number
  Case 2501
   MsgBox "You have cancelled the email"


End Select


Resume ExitHere






End Sub

however I still encounter the same error in runtime (full access has no problem).

[Ruegen]

Thanks, I changed it to

Private Sub btnEmailConfirm_Click()
On Error GoTo ErrorHandler


If IsNull(Me.TeacherEmail) Or IsNull(Me.TeacherName) Or IsNull(Me.TeacherSurname) Or IsNull(Me.TheatreSeats) Or IsNull(Me.TheatreTeachersAttending) Then
MsgBox "You require a teacher email, name and surname, and the seating in order to email the confirmation"
Else
DoCmd.SendObject acReport, "rptConfirmationTAXTheatre", "PDFFormat(*.pdf)", Me.TeacherEmail, , , "School Performance Tours - Booking Confirmation", , True
 End If






ExitHere:
Exit Sub '(or Exit Function)


ErrorHandler:
Select Case Err.Number
  Case 2501
   MsgBox "You have cancelled the email"


End Select


Resume ExitHere






End Sub

however I still encounter the same error in runtime (full access has no problem).

(perhaps add the exithere, before the end if?)

This error is when you close the compose window off access instead of sending the email.

[ItsMe]

Have you tried it without the error handler? You are passing it off to another program. VBA probably does not know what to do.

[Ruegen]

Have you tried it without the error handler? You are passing it off to another program. VBA probably does not know what to do.

I tried it first without, then remembered I had used it before (code above, post 3) so I figured since it worked there it would fix.

This is totally new. The button is on a subform on one form, main form on another (they all do the same/similar email procedure)

I can cancel an email on the other form which works fine because when it encounters the error it jumps to error handler but for some reason it doesn't this time around and gives me that new error (photo above).

Access runs fine, runtime gives me that error

[Ruegen]

I removed the errorhandler and code - still has the error

It thinks I have cancelled the action just by closing the compose message in the email program.

[ItsMe]

Must be something else that is causing this. I have similar code running with docmd. Don't recall having this problem. I may have a chance to do some testing in RT tomorrow. But, like already mentioned, there must be additional code hanging.....

[Ruegen]

Must be something else that is causing this. I have similar code running with docmd. Don't recall having this problem. I may have a chance to do some testing in RT tomorrow. But, like already mentioned, there must be additional code hanging.....

I've given the entire onclick event code - I'm trying different things myself, would be very interested to find out what you come up with.

[June7]

I deleted my first post when I realized it wasn't relevant. You were too fast and already read it. In your case the Exit branch isn't really needed but there are situations where it could be. Review http://allenbrowne.com/ser-23a.html
Allen shows using the Exit branch to make sure objects such as recordsets get cleared even if the procedure errors. This could be really important when opening apps like Excel and Outlook as VBA objects. If the procedure errors and the objects aren't cleared and the apps closed, could be left with disconnected processes running in Windows Task Manager.


This problem code is behind a subform? The procedure that works without issue in RT is behind a main form?


Ssanfu, the email can be canceled from the Outlook message that opens. Just close it without clicking Send. That produces the error message that Reugen is trying to handle. The error handler works fine in a regular db but the runtime has an issue.

[ssanfu]

@June
Thanks for the info.
I haven't had to set up any email code.... I will keep this in mind for when I have to start emailing from Access.

[ItsMe]

I was able to test in W7 with accdr file and in XP, Office 2003 in full RT environment. I first tested with only "On Error Resume Next" Then I tested with the following. Did not have an issue of the app crashing. Got a msgbox and everything was fine.

On Error GoTo ErrorHandler

DoCmd.SendObject acReport, "Report", "PDFFormat(*.pdf)", strTo, strCc, strBcc, "Email Address Subject Line", "Text to Include within Email Body", True
ExitHere:
Exit Sub '(or Exit Function)

ErrorHandler:
Select Case Err.Number
  Case 2501
   MsgBox "You have cancelled the email"
End Select
Resume ExitHere