VBA, Outlook 2013: Catch user cancelling send operation without saving to draft folder

How can I catch a mailitem, that a user decided to cancel the send operation and to not save in draft Folder? If a user aborts send Operation and decides to save the mailitem in the draft Folder, I can use the _itemadd Event for the items collection to catch this. But how would I be able to catch a mailitem, before it gets destroyed when a user just aborts sending the message?
Maybe I'm thinking anyway in the wrong direction to start with. Maybe there is a totally different solution to my issue. So here is the original task I want to solve:
When a user initiates sending a message regarding a particular Topic (a refernece number referenced in the mail itself), a macro is involved checking, whether a message to this Topic has already been send. This is done by checking whether the Topic reference number exists in an Excel database. if a mail has been sent already, the user is notified acordingly. If no reference exists yet, a reference is added to the Excel database and the mailmessage is presented to the user for making changes in the mailbody and then send the message. If the user now decides to NOT send the message and NOT to save it in the draft Folder, the entry added to the Excel database needs to be removed. So catching the mail message before it gets destroyed is to revert those changes made to Excel.
Any ideas?
Thanks in advance!
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

We might be able to help with you original piece of the questions, but I think it would be better to evaluate the process first.  I personally think a better practice would be to not update the database until the email is sent.  That would be much easier code to maintain from the Item_Send event.  In that event you can check if this topic has already been addressed.  If so, then you can cancel the send operation and display a message.  If it has not been sent, then you can update the database and know that it is being sent.  If you need help with that piece of code, let us know (You may need to post the code you have).

Now if you want to proceed with your current method, I think we could make this work with an item close event.  A little more tricky, but we should be able to do it.
I still think re-doing the code and only writing to the database when the item is sent is best.  However, I went ahead and took a stab at some code to handle your original question.

Put this in the ThisOutlookSession Module and adapt as needed:
Private WithEvents myEmailItem As MailItem
Private g_blnMailWasSent As Boolean
Private g_strSubject As String

Private Sub myEmailItem_Close(Cancel As Boolean)
    g_strSubject = myEmailItem.Subject
End Sub

Private Sub myEmailItem_Unload()
    If Not g_blnMailWasSent Then
        Dim objDrafts As Outlook.Folder
        Dim objFoundMatch As Outlook.MailItem
        Set objDrafts = Session.GetDefaultFolder(olFolderDrafts)
        If LenB(g_strSubject) > 0 Then
            Set objFoundMatch = objDrafts.Items.Find("[Subject] = " & g_strSubject)
            If objFoundMatch Is Nothing Then
                Debug.Print "Do your work to undo the database entries"
            End If
        End If
    End If
End Sub

Private Sub Application_ItemLoad(ByVal Item As Object)
    If Item.Class = Outlook.olMail Then
        Set myEmailItem = Item
        g_blnMailWasSent = False
    End If
End Sub

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KKressAuthor Commented:
ltlbearand3, thanks for your solution. I see and understand how your code is supposed to work. But I did follow your other Suggestion and only modify my Excel data at send time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.