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

Depends on how the class will be initialized and where (in what scope) the class object will be stored. So please show the code in which the class is used. — Axel Richter
You probably need to have a variable of your class on module level — Storax
The Dim myMailItem As clsMailItem must be outside the Sub. Then it is in global scope and not in Sub's scope. If it is in Sub's scope, it will be terminated if the Sub ends. — Axel Richter
@Storax Yeah, i did a little mistake. I wrote "Dim myMailItem As clsMailItem" in the sub itself. Not on the module level. Thank you very much. Problem solved :) — tolbas re
@AxelRichter Thank you very much! :) Now i also did unterstand why my code didnt work and learned for the future. Thank you for your help — tolbas re

Storax Storax · Accepted Answer · 2018-05-12T08:37:58

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.

How to control when a VBA Class Terminates

1 Answers