Solved

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

Posted on 2009-04-02
7
412 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

863 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

22 Experts available now in Live!

Get 1:1 Help Now