VBA to Check if Email Sent/Not Sent and Display Text+Date

I have a code to generate and display an email using some data in an excel sheet. The email is generated based on an initial range of cells, let’s call it myRNG, selected via input box, from a single column (A). Of course, some other data is added to the email body (myRNG offset etc.), some attachments are added too, and then the email is displayed in order to check it before finally sending it.

Upon displaying the email I might decide to close it because some data is wrong and then generate it again after I correct the data in excel.

In the case I close it, after pressing the close button I would like some code to add “NOT SENT” to a range (let’s say in column B), corresponding to my initial range of data (myRNG).

myRNG is A1:A3

EG: Case I close the email to correct errors:

        A          B          C          D          E
1    009123    NOT SENT
2    009331    NOT SENT
3    009509    NOT SENT
4    008326
5    008222
6    007119

In the case I send it instead of closing it (when column B cells are blank) or I send it after correcting data (when column B cells contain “NOT SENT”) after pressing the send button the same range in column B should be completed/updated with “SENT” and in column C the date of sending the email should be added (today, but not as a formula of course).

EG: After I send the email:

        A          B          C          D          E
1    009123      SENT    25.07.2018     
2    009331      SENT    25.07.2018     
3    009509      SENT    25.07.2018     
4    008326
5    008222
6    007119

But, in the future I might want to resend the same email again, I would like the date to remain unmodified and columns D and E to be updated with RE-SENT (col D) and date when it was re-sent in column E.

EG: After I send the email again:

        A          B          C          D          E
1    009123      SENT    25.07.2018   RE-SENT   26.07.2018
2    009331      SENT    25.07.2018   RE-SENT   26.07.2018
3    009509      SENT    25.07.2018   RE-SENT   26.07.2018
4    008326
5    008222
6    007119

If I choose to send the email for the 3rd time, column E should be updated with the new date.

I hope this isn’t very confusing, I tried to explain as best as possible.

I tried to find a way to display SENT/NOT SENT in column B upon sending/closing the email without sending, but I get stuck at using class modules and referring to them in my code. Simply nothing gets displayed. Dunno if I am allowed paste here the source (website) of the code I tried. I will add the code below, inside PRE tags.

It was based on the _Send event in the Outlook.MailItem class, for which I added Microsoft Outlook 15.0 Object Library as reference. The member on that website didn’t give this detail, nor did he give much detail for me to work with, but his answer dates from 2010. I’m guessing there might be some compatibility issues since back then they were using Office 2007 and I’m using Office 2013.

I tried to step through that part of the code, but there are no errors and I have no clue what to do. The member didn’t mention a range for BoolRange, or for DateRange.

This is entirely possible, using the _Send event in the Outlook.MailItem class.

The way I use it, I create a class called EMailWatcher, so when I create the email and do
 the .Display, I then create a new EMailWatcher object and tell it to watch that email for
 send, then report back when it happens.

Here's the class as I use it. Basically, I also optionally can set the BoolRange so that if 
the user sends the email, that Excel range gets updated with True. I can also have the
 class update an Excel range with the time the email is sent.

Public BoolRange As Range
Public DateRange As Range
Public WithEvents TheMail As Outlook.MailItem

Private Sub TheMail_Send(Cancel As Boolean)
    If Not BoolRange Is Nothing Then
        BoolRange.Value = True
    End If
    If Not DateRange Is Nothing Then
        DateRange.Value = Now()
    End If
End Sub

And here's how I use it:

With OutMail
    .To = addr
    .Subject = "CCAT eVSM Utilities License Code"
    .Body = "Message body"
    .Display
End With

Set CurrWatcher = New EmailWatcher
Set CurrWatcher.BoolRange = Range("B1")
Set CurrWatcher.TheMail = OutMail