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:

13407



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:

13408


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

13409

Save the workbook and it should then work.