Outlook VBA with Communicator API Messenger

Posted on 2014-02-14
Last Modified: 2014-03-01
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
Question by:peer754
  • 5
  • 4

Accepted Solution

TheNautican earned 500 total points
ID: 39862260
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


Author Comment

ID: 39862514
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.

Expert Comment

ID: 39864964
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.

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.


Author Comment

ID: 39866746
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.


Expert Comment

ID: 39867934
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.


Author Comment

ID: 39868801
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.


Expert Comment

ID: 39870938
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.


Author Comment

ID: 39872788

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

LVL 76

Expert Comment

by:David Lee
ID: 39884259
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?

Author Comment

ID: 39897226
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.


Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this step by step procedure, you will come to know the details of creating an Outlook meeting in 2007, 2010, 2013 & 2016.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question