Outlook VBA with Communicator API Messenger

I have a VBA application that synchronizes the Calendar with SQL server. One of the columns in my DB table needs to show the current IM presence status image the same way you'll see it in Outlook or in the Messenger client.

I have managed to connect and get values from my Messenger object but fail when trying to implement the event handler OnMyStatusChange. My VBA project gets compilation error saying "Procedure declaration does not match description of event or procedure having the same name".

This is non-working my attempt:
Public WithEvents oComm As CommunicatorAPI.Messenger
Set oComm = CreateObject("Communicator.UIAutomation")

Private Sub oComm_OnMyStatusChange(hr As Long, ByRef mMyStatus As MISTATUS)
Dim username As String

If hr = S_OK Then
    MsgBox "My status changed from " + CStr(mMyStatus)
End If
' Do something like get the new status and update the DB

End Sub

Open in new window

When I look into the object explorer I see that the doc for the Messenger class event handlers exactly assumes this implementation:

Event OnMyStatusChange(hr As Long, mMyStatus As MISTATUS) Member of CommunicatorAPI.Messenger My status changed.

Please, anyone, I have googled this for days now and I seem to be the only one on this planet facing these problems :(

Office 2010, Communicator 2007

Thanks, Peer
Who is Participating?

Improve company productivity with a Business Account.Sign Up

TheNauticanConnect With a Mentor Commented:
Here is code from a database I use for sending IMs. I do a status check before sending. I scrubbed it a little but left all you should need.

Private Sub Button1_AfterUpdate()
    Dim strContact As String,  strStatus As String
    Dim comMsgr As CommunicatorAPI.Messenger
    Dim comMsgrContact As CommunicatorAPI.IMessengerContact

    Set comMsgr = CreateObject("Communicator.UIAutomation")
    Set comMsgrContact = comMsgr.GetContact(strContact, CStr(comMsgr.MyServiceId))

    Select Case comMsgrContact.Status

    Case 0
        strStatus = "UNKNOWN"
    Case 1
        strStatus = "Offline"
    Case 2
        strStatus = "Online"
    Case 6
        strStatus = "Invisiable"
    Case 10
        strStatus = "Busy"
    Case 14
        strStatus = "Be Right Back"
    Case 18
        strStatus = "Idle"
    Case 34
        strStatus = "Away"
    Case 50
        strStatus = "On The Phone"
    Case 66
        strStatus = "At Lunch"
    Case 82
        strStatus = "In a Meeting"
    Case 98
        strStatus = "Out Of The Office"
    Case 114
        strStatus = "Do Not Disturb"
    Case 130
        strStatus = "In Conference"
    Case 146
        strStatus = "Allow Urgent Interruptions"
    Case 162
        strStatus = "May Be Available"
    Case 178
        strStatus = "Custom"
    Case 256
        strStatus = "Trying To Find Server"
    Case 512
        strStatus = "Connecting To Server"
    Case 768
        strStatus = "Syncing With Server"
    Case 1024
        strStatus = "Disconnecting From Server"

    End Select

    Me.IMStatus = strStatus
End Sub

Open in new window

peer754Author Commented:
Thank you for sharing your code.
Do you think that I should implement a new event handler through the Contact object instead?

My problem is that I can't implement the event handler according to the documentation and thus it does not even compile.
From what i read online there have been problems using various event handlers for this. Most threads I saw just did what i ended up doing and that's manually checking it.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

peer754Author Commented:
I see what you mean. I've spent several days already with this.
However, your suggestion won't help me since I can't do this manually. I need an auto update of the status through an event handler.

I did managed to get some events yesterday but later on they stopped working again ???
One of the suggestions was to listen to the OnContactStatusChange event instead of the OnMyStatusChange. This event does fire BUT I need MyStatus which I can't capture since I am not in my own contact list. and there's no way I can add myself to it.

Here's what I ended up with:

Public WithEvents oComm As CommunicatorAPI.Messenger
Public myContacts As Collection

Private Sub Application_Startup()
    Set oItems = Application.GetNamespace("MAPI").GetDefaultFolder(olFolderCalendar).Items
    Set objExplorer = Application.ActiveExplorer
    'Set oComm = CreateObject("Communicator.UIAutomation")
    Set oComm = New CommunicatorAPI.Messenger
    Set myContacts = New Collection
    'MsgBox "DEBUG: oComm added "
End Sub

Private Sub oComm_OnMyStatusChange(ByVal hr As Long, ByVal mMyStatus As CommunicatorAPI.MISTATUS)
    Dim status As String
    status = GetUserStatus(mMyStatus)
    MsgBox "My status has changed to " + status + " updating the database!"
    DBHandler.SetCommunicatorStatus (status)
' Do something like get the new status and update the DB
End Sub

Private Sub oComm_OnContactStatusChange(ByVal pMContact As Object, ByVal mStatus As CommunicatorAPI.MISTATUS)
    'MsgBox "DEBUG Event handler oComm_OnContactStatusChange fired for contact " + pMContact.SigninName
    Dim userStatus As String
    userStatus = GetUserStatus(mStatus)
    'Check if contact is within appartment
    If DBHandler.isValidContact(pMContact.SigninName) Then
        SetStatus pMContact.SigninName, userStatus
    End If
End Sub

Open in new window

So, the OnMyStatusChange event did occasionally fire and I now need to find out why it doesn't do that all the time.

The OnContactStatusChange is working but that event is no use for me at this point.

Peer - So I've implemented your code albeit in my original access database that i used my code for. I've noticed the when my status changes it fires for both events you have. However, it appears to stop working after some amount of time. I'm wondering if there is a bug in the way this event it handled. I'll keep digging and see what i can come up with, at least now though i can duplicate your problems.

peer754Author Commented:
Hi Naut,

Great that you can duplicate the behaviour :)

I did comment out the event that I don't need but still don't get the other to fire.
I've red some thread discussing about COM object getting released by the main Outlook thread (that's why I go through the collection object). I do initialise the collection but I forgot to post it, sorry.
The collection is there for preventing the release of the COM object but I'm probably doing it the wrong way?

Today I managed to implement the same events in a C# console app without problems. Problem though is that I then need to figure how to run this application in Windows 7 OS as a background event handler. I might post another thread dealing with this. I don't want to create a Win Service + I don't want to force any user interaction merely to update the DB.

Peer - After doing a compact and repair on my database I've yet to have either event not fire. Unfortunately, macros are completely blocked in outlook for my work machine so I can't test it in the same environment you need. I wish I could help further.

peer754Author Commented:

Ok, thanks for trying, hopefully you're not the only expert here or?

David LeeCommented:
Hi, peer754.

"I have a VBA application that synchronizes the Calendar with SQL server. One of the columns in my DB table needs to show the current IM presence status image the same way you'll see it in Outlook or in the Messenger client."

"I have managed to connect and get values from my Messenger object but fail when trying to implement the event handler OnMyStatusChange."

Sorry, but I don't understand what it is you're trying to do.  Are you saying that you want to trap Communicator status changes?  If so, what does that have to do with synching your calender to SQL?
peer754Author Commented:
Hi BlueDevilFan,

Now I'm sorry and I'm the one that don't understand. Are you trying to answer my question with another question? If that's the case, I can't see how that's gonna help me?
I did provide some background info regarding the application where the Communicator is one part of, how could that possible confuse you?

If you read the complete thread you might see what we're discussing.

Sorry, I thought this forum was for getting help with serious programming problems and not a place where some "experts" are patronizing over how you exactly describe your problem.

I also red a previous thread with a similar problem where you provided a complete VBA code as solution. In here you choose to skip the event handler part and instead implemented a timer. Was that because you don't have a solution for this?

Don't bother to answer this.

For anyone else reading this I want you to know that both my events are now firing and my code is working as expected. The problem seemed to lie in my Communicator client which I re-installed.

Oh, and btw, I'll give all the point to Naut who at least tried to help me.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.