?
Solved

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

Posted on 2009-04-02
7
Medium Priority
?
435 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 2

Accepted Solution

by:
AdminGMP earned 1000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses
Course of the Month15 days, 22 hours left to enroll

850 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