Checking If E-Mail has been sent by user

[BrennaBoy1]

Hi,

I am working on a Userform in an Excel Automation project where once the user clicks a button, it creates and displays an E-Mail.

I want to mark a cell in Excel as "Sent" if the user actually clicks the send button on the E-Mail or "Not Sent" if they just close it.

I did some research and found some code to use on a forum, but I am getting error messages.

I have attached a doc file with the code I found on the web with my code beneath that.

I am getting an error message with the line: "Public WithEvents itm As Outlook.MailItem"

The forum post I got the code from mentions, "With the added note that it requires the Outlook library reference to run, works well."

I am not sure what this means. My VBA learning is coming along, but this is the first time I have encountered Classes as such and I do not understand them at the moment - so trying to learn.

Can anyone provide guidance or suggestions?

Thanks,

B.

[gmayor]

Using your examples, basically you need two modules in your macro enabled workbook.

1. a class module renamed CMailItemEvents with the code as follows:

Class Module.jpg

Option Explicit
Public WithEvents itm As Outlook.MailItem

Private Sub itm_Close(Cancel As Boolean)
Dim blnSent As Boolean
    On Error Resume Next
    blnSent = itm.sent
    If Err.Number = 0 Then
          Debug.Print "not sent"
       Else
          ' do what you need to do
       End If
End Sub

2. an ordinary module, the name of which doesn't matter containing the following code:

Ordinary module.jpg

Option Explicit
Public itmevt As New CMailItemEvents
Public aOutlook As Outlook.Application

Public Sub SendISINRequestEmail(SendMailTo, CCAddresses, MailSubject, MailBody)
Dim aEmail As Outlook.MailItem
Dim rngeAddresses As Range, rngeCell As Range, strRecipients As String

On Error Resume Next
    Set aOutlook = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If aOutlook Is Nothing Then Set aOutlook = New Outlook.Application

    Set aEmail = aOutlook.CreateItem(0)
    Set itmevt.itm = aEmail
    aEmail.Subject = MailSubject

    aEmail.HTMLBody = MailBody

    aEmail.SentOnBehalfOfName = "SH-SPVOPS@ubs.com"
    aEmail.To = SendMailTo
    aEmail.CC = CCAddresses

    aEmail.Attachments.Add ("P:\Test Termsheet PDF.pdf")
    aEmail.Display

End Sub

Finally you need to ensure that you have a reference to the Outlook object library checked in Tools > References

Object Library.jpg

Save the workbook and it should then work.