Excel dashboard based on Outlook data with events

[a g]

My first post :) If this post should be posted in the Outlook-thread, please move it.

I'm making a dashboard in Excel showing different kind of data from a outlook account/folder.

What I'm unable to do, is create a new Outlook.Application instance with events attached.

I have created a class module in my Excel workbook. My code looks like this:


Private WithEvents OA as Outlook.Application

Public Sub Class_Initialize()
On Error Resume Next
Set OA = GetObject("", "Outlook.Application")
If Err.Number <> 0 Then
Set OA = New Outlook.Application
Err.Clear
End If
End Sub

If I remove the WithEvents in the declaration, this code work just fine.

Anyone got an explanation why I'm unable to create a Outlook object with events?
My Excel and Outlook version is 2013 (PC)

[snb]

I don't think you need a class module.

You could have a look over here:

http://www.snb-vba.eu/VBA_Outlook_external_en.html

[a g]

To be a little bit more clear. I have tested the same code without a class module as well. With the same negative result.
The class module approach was selected because of easier reuse of the code later.

But this doesn't change my initial problem. That I'm unable to set a variable that is declared with WithEvents, resulting that I'm unable to hook any function to Outlook.Appliction events

[Paul_Hossler]

With this in a standard module



Option Explicit
Dim oMyOutlook As clsOutlook

Sub Test()

Dim oItem As Outlook.MailItem
Set oMyOutlook = New clsOutlook

Set oItem = oMyOutlook.MyOutlook.CreateItem(olMailItem)
With oItem
.Subject = "Test"
.To = "somebody@somewhere.com"
.Send
End With
Stop

Set oMyOutlook = Nothing

End Sub




and this in my class module




Option Explicit
Private WithEvents OA As Outlook.Application

Public Sub Class_Initialize()
On Error Resume Next
Set OA = CreateObject("Outlook.Application") ' <<<<<<<<<<<<<<
If Err.Number <> 0 Then
Set OA = New Outlook.Application
Err.Clear
End If
End Sub


Public Property Get MyOutlook() As Outlook.Application
Set MyOutlook = OA
End Property

Private Sub OA_ItemSend(ByVal Item As Object, Cancel As Boolean)

MsgBox "In ItemSend -- " & Item.Subject

End Sub




seems to send email and I get the MsgBox in the ItemSend event


Paul

[snb]

Which Outook events you are after ?