Pass a variable between Outlook and Excel - was email actually sent

Question

Not even sure if this is possible but wanted to ask anyway. I have an Excel sub where I create an Outlook e-mail which is then displayed for editing on a user's PC. I know how to do all of the coding to mark Excel to show that the e-mail was created but what I don't know is whether the user actually pressed the "Send" button or not. Is there any way to pass a TRUE variable back to Excel if the user does indeed press the "Send" button or leave it as False if they do not?

Answer 1

You need to subscribe to the Send event of the Outlook item displayed, or consider using the ItemSend event of the Application class to handle all outgoing items in Outlook.

Public WithEvents myItem As Outlook.MailItem 
 
Sub SendMyMail() 
 Set myItem = Outlook.CreateItem(olMailItem)
 myItem.To = "Eugene"
 myItem.Subject = "Data files information" 
 myItem.Display 
End Sub 
 
Private Sub myItem_Send(Cancel As Boolean) 
 myItem.ExpiryTime = #2/2/2023 4:00:00 PM# 
End Sub