1

Right now all of my VBA code is in ThisOutlookSession. I want to put everything in a module so I can export it and other folks can import it without having to muck around with their own ThisOutlookSession. I want it to be easy for the user -- the user just imports my module file.

My code depends on Application_MAPILogonComplete and WithEvents. Neither of these are available/work in a module.

I see that classes have a Class_Initialize but it only triggers when a class object is initialized so I'd still need some kind of Application_MAPILogonComplete event.

Is there anyway to do what I want? Keep everything in a module or class that can be exported and imported that has code run when Outlook opens and supports WithEvents so I can execute a function when new e-mails are added to a folder?

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89

1 Answers1

0

Only an object can handle events, so you need a class - ThisOutlookSession is one, but if you want to modularize your code you'll need to have a class module to do that work.

Option Explicit
Private WithEvents App As Outlook.Application

Private Sub Class_Initialize()
    Set App = Application
End Sub

Private Sub Class_Terminate()
    Set App = Nothing
End Sub

'select "App" from the left-hand dropdown at the top of the code pane.
'then select the event(s) you want to handle from the right-hand dropdown;
'the VBE will automatically generate the handler(s) with the correct signature.

All that's left to do is to have an actual instance of that class (let's say it's called Class1) - you'll want to create that instance in ThisOutlookSession

Option Explicit
Private AppEvents As Class1

Private Sub Application_Quit()
    Set AppEvents = Nothing
End Sub

Private Sub Application_Startup()
    Set AppEvents = New Class1
End Sub
Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Is there no way to do it without having to edit/modify `ThisOutlookSession`? – IMTheNachoMan Aug 10 '18 at 19:17
  • @IMTheNachoMan you could always have an auto-instantiated object in a standard module, e.g. `Private AppEvents As New Class1` - but I'd be more inclined to trust using `ThisOutlookSession` as an entry point works perfectly fine and reliably. Auto-instantiated objects (`As New`) are always kind of weird. – Mathieu Guindon Aug 10 '18 at 19:19
  • When I add `Dim AppEvents As Class1` then `Set A = New Class1` to a `public sub` in a module and run it the `Class_Initialize` function **does** fire. When I try `Dim AppEvents As New Class1` in a `public sub` or `Private AppEvents As New Class1` in the top of the module then the `Class_Initialize` function **does not** fire. – IMTheNachoMan Aug 10 '18 at 19:28
  • Yeah see `As New` is weird. Declarations aren't executable, so the object only gets *actually* created when the identifier is referred to by the first executable statement that gets to it. – Mathieu Guindon Aug 10 '18 at 19:59