NEED URGENTLY .... Application Freezes if User Cancels SendObject

I was originally using a DoCmd to generate an email with an HTML attachment.  This works fine until a user cancels the email.  The application would then freeze up entirely and produce the following error even when trapping for Errors.

"The Send Object action was canceled"
"You used a method of the DoCmd object to carry out an action in Visual Basic, but then Clicked Cancel in a dialog box"

Form that point - the database is locked up and the only way out is to kill the db via Task Manager.  When I try to exit the database I get the next error.

"If youre runing a Visual Basic module that is using OLE or DDE you may need to interupt the module

To remedy this I'm using  the code listed below which works great, however, it no longer appears to trap if a user cancels the email..   I need to roll back data if the user cancels an email.  What is the best approach to capture this event?
Set objOutlook = CreateObject("Outlook.Application")
        Set o = objOutlook.CreateItem(olMailItem)
            o.To = strTO
            o.Subject = "DCN Notice:   DCN " & strDCN 
            o.Body = "DCN " & strDCN & " has been submitted for review/assignment"
            o.Importance = olImportanceHigh  'High importance
            DoCmd.OutputTo acOutputReport, "rptReviewNotice", acFormatRTF, strLoc
            o.Attachments.Add strLoc, olByValue
            o.Display
            MsgBox "DCN " & strDCN & " has been submitted for review"
        End If
 
ErrSend:
    Select Case err.Number
    Case 0 To 5000
        MsgBox "DCN Not Submitted for review"
        f.Status = "In Process"
        f.StatusDate = strDate
        f.S_DCNCoord = ""
        f.btnSubmitReview.Enabled = True
        Set f = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookRecip = Nothing
        Set objOutlookAttach = Nothing
 
        Case Else
        MsgBox "DCN Not Submitted for review"
        f.Status = "In Process"
        f.StatusDate = strDate
        f.S_DCNCoord = ""
        f.btnSubmitReview.Enabled = True
        Set f = Nothing
        Set objOutlookMsg = Nothing
        Set objOutlook = Nothing
        Set objOutlookRecip = Nothing
        Set objOutlookAttach = Nothing
 
        Exit Function

Open in new window

ALPC_EngineeringITAsked:
Who is Participating?
 
AdminGMPConnect With a Mentor Commented:
Inline error handling is probably easiest to follow in this case.

Also be aware that the user could SAVE your email as a draft without sending it. It's up to you how you handle that situation, but you can check for it with o.Saved
'...
o.Attachments.Add strLoc, olByValue
o.Display True
 
On Error Resume Next
Dim blnSent as Boolean
blnSent = o.Sent
If Err.Number = -2147221238 then
    'Error caused by checking property of sent email
    blnSent = True
 
ElseIf Err.Number <> 0 Then
    MsgBox "Some other error....."
    'Handle this how you want
End If
 
If blnSent Then
    MsgBox "DCN " & strDCN & " has been submitted for review"
Else
    MsgBox "DCN Not Submitted for review"
    f.Status = "In Process"
    f.StatusDate = strDate
    '.... etc
End If

Open in new window

0
 
ALPC_EngineeringITAuthor Commented:
Increasing the points on this one ... I need a quick solution!
0
 
AdminGMPCommented:
Change o.Display to:
o.Display True

to cause your code to pause until the user sends or cancels the email. Then you can check the boolean o.Sent property... except if the user sent the email then checking any property gives you error -2147221238 "The Item has been moved or deleted". So check o.Sent - if it is False, then the user cancelled the send - if it gives an error, then they sent it!
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
ALPC_EngineeringITAuthor Commented:
Can you provide a little code please?  
0
 
ALPC_EngineeringITAuthor Commented:
Couldn't get it to work at first. I ended up having to change the following:

If Err.Number = -1075576566 then
'Error caused by checking property of sent email
blnSent = True

Is this a code that will be changing and need to be trapped for?
Also, how would I set  a  red flag for emails that are sent?

0
 
ALPC_EngineeringITAuthor Commented:
That code changes upon each sent email, but the following appears to work now.

          If err.Number = 0 Then
                'Error caused by canceling email
                blnSent = False
            Else
                blnSent = True
            End If

Thanks for steering me in the right direction for this.  Any suggestions about how to mark a Red Flag status for emails that are sent?
0
 
ALPC_EngineeringITAuthor Commented:
That code changes upon each sent email, but the following appears to work now.

          If err.Number = 0 Then
                'Error caused by canceling email
                blnSent = False
            Else
                blnSent = True
            End If

Thanks for steering me in the right direction for this.  Any suggestions about how to mark a Red Flag status for emails that are sent?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.