Solved

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

Posted on 2009-04-02
7
409 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:ALPC_EngineeringIT
  • 5
  • 2
7 Comments
 

Author Comment

by:ALPC_EngineeringIT
ID: 24050597
Increasing the points on this one ... I need a quick solution!
0
 
LVL 2

Expert Comment

by:AdminGMP
ID: 24051012
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
 

Author Comment

by:ALPC_EngineeringIT
ID: 24051622
Can you provide a little code please?  
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 2

Accepted Solution

by:
AdminGMP earned 250 total points
ID: 24051887
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
 

Author Comment

by:ALPC_EngineeringIT
ID: 24052153
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
 

Author Comment

by:ALPC_EngineeringIT
ID: 24052408
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
 

Author Closing Comment

by:ALPC_EngineeringIT
ID: 31565795
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

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now