How to control when a VBA Class Terminates

Question

I wrote a VBA Class Module to create a Outlook Mailitem from my Excel Workbook and monitor the MailItem_Send Event to run a macro in my Workbook after the Mailitem has been sent.

The Class Module looks like this:

Option Explicit

Public MailSubject As String
Public MailRecipient As String
Public MailBody As String

Public WithEvents mOutlook As Outlook.Application
Public WithEvents mMailItem As Outlook.MailItem

Private Sub Class_Initialize()
    Set mOutlook = New Outlook.Application
    MsgBox "mMailItem Class-object has been initialized"
End Sub

Public Sub CreateAndDisplayMailItem()

    Set mMailItem = Outlook.CreateItem(olMailItem)
    With mMailItem
        .To = MailRecipient
        .Subject = MailSubject
        .Body = MailBody
        .Display
    End With

End Sub

Private Sub Class_Terminate()
    Set mMailItem = Nothing
    MsgBox "mMailItem Class-object has ben terminated"
End Sub

Private Sub mOutlook_ItemSend(ByVal Item As Object, Cancel As Boolean)
    MsgBox "mOutlook Item Send Event has ben triggered"
    'Call MyMacro
End Sub

• After I create a Outlook Mailitem out of my Excel Workbook using the above shown Class-Module, the Outlook Send-A-Mail window opens correctly.

• If I stay in the Send-A-Mail window (without changing to any other window) until the mail is sent; my ItemSend Event triggers correctly.

The Problem is:

• If I go back to my Excel Workbook (so I change the window from my displayed MailItem back to my Workbook), the class-object is terminated before my MailItem is sent.

The Class_Terminate Event from my MailItem should fire after the MailItem is sent! Not before.

The Class is used here:

Sub Mail_Test()

Dim myMailItem As clsMailItem

Set myMailItem = New clsMailItem
myMailItem.MailSubject = "Test überschrift"
myMailItem.MailBody = "Test Body"
myMailItem.MailRecipient = "[email protected]"

myMailItem.CreateAndDisplayMailItem

End Sub

Answer 1

This is working for me if I name your classol

Option Explicit

Dim xOl As New ol

Sub test()

    xOl.MailRecipient = "[email protected]"
    xOl.MailBody = "Test"
    xOl.MailSubject = "Subject"
    xOl.CreateAndDisplayMailItem

End Sub

If you declare xOl in the sub it will be terminated with the end of the sub.