CODEWITHSUNDEEP
exceloutlookvba
Sgdva
Sgdva

Reputation: 2800

Outlook Send Event Class


Background:
A while ago, I discovered a class module to get events from outlook, specifically, as the title suggest, I'm using it to catch the send event item -to know if it was really sent or not-. This is the Class module itself. I can't remember how I used to call it (I just saved it for later references, but, deleted the way to call it in my main sub).

Option Explicit
Public WithEvents itm As Outlook.MailItem
Private Sub itm_Send(Cancel As Boolean)
   Dim blnSent As Boolean
   On Error Resume Next
   blnSent = itm.Sent
   If Err.Number = 0 Then
      Debug.Print "Email not sent"
   Else
    Debug.Print "Email sent")
    End If
End Sub

Problem:
I forgot how should I call it in my sub that's sending the email. I have tried the following declarations at top:

Dim itmevt As New CMailItemEvents
Public EmailToSend As Outlook.MailItem

Then in my sub that's sending the email:

Set itmevt.itm = EmailToSend

However, I can't get the event of sending triggered in the class.

Specific Questions:
1. How do I call the class correctly?
2. How can I get the value sent/not sent efficiently (I'd like to write it in a cell for later analysis -sent/not sent-) I thought of parsing to a public function, that could get back the value to the sub that's calling it, but, I don't think that's the best approach

Upvotes: 2

Views: 572

Answers (1)

user6432984
user6432984

Reputation:

From what I was able to workout this class is somewhat bogus. The proper usage would be Set itmevt.itm = OutApp.CreateItem(0). The problem lies in using the Send event to test whether or not the item was sent. Notice that the Cancel parameter of the event. Setting Cancel = True prevent the email from being sent. That tells us that if the email isn't sent till after this event completes. Sent will always return false and will never cause an error from the Send event.

On the other hand, if we test to see MailItem.Sent after we send the email it will throw a The item has been moved or deleted. error.

enter image description here

Knowing that we can create a function that will send our emails that will return True if the email was sent and False if it wasn't.

Function SendEmail(addressTo As String, addressCC As String, Subject As String, HTMLBody As String) As Boolean
    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")

    With OutApp.CreateItem(0)
        .To = addressTo
        .CC = addressCC
        'OutMail.BCC = ""
        .Subject = Subject
        .HTMLBody = HTMLBody
        .Send
        On Error Resume Next

        Call .Sent

        SendEmail = Err.Number <> 0

        If Err.Number = 0 Then
            Debug.Print "Email not sent"
        Else
            Debug.Print "Email sent"
        End If

        On Error GoTo 0

    End With



    Set OutApp = Nothing

End Function

Upvotes: 1

Related Questions