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.

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!


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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Many people use more than one email account and so it becomes difficult for them to manage them when they use separate accounts,  so, in this article, I have shared an easy way to add Other Mail Accounts in your Google Inbox. It helps to combine all…
Many of my clients call in with monstrous Gmail overloading issues with Outlook. A quick tip is to turn off the All Mail and Important folders from synching. Here is a quick video I made to show you how to turn off these and other folders in Gmail s…
CodeTwo Sync for iCloud ( automatically synchronizes your Outlook 2016, 2013, 2010 or 2007 folders with iCloud folders available via iCloud Control Panel. This lets you automatically sync them with…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now