Solved

Outlook VBA with Communicator API Messenger

Posted on 2014-02-14
10
2,402 Views
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
0
Comment
Question by:peer754
  • 5
  • 4
10 Comments
 
LVL 8

Accepted Solution

by:
TheNautican earned 500 total points
Comment Utility
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


Regards,
-Naut
0
 

Author Comment

by:peer754
Comment Utility
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.
0
 
LVL 8

Expert Comment

by:TheNautican
Comment Utility
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.

-Naut
0
 

Author Comment

by:peer754
Comment Utility
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
    DBHandler.UpdateAppointmentsToSQL
    'Set oComm = CreateObject("Communicator.UIAutomation")
    Set oComm = New CommunicatorAPI.Messenger
    Set myContacts = New Collection
    InitMyContacts
    '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
    
    'oComm.
    userStatus = GetUserStatus(mStatus)
    'pMContact.Status
    '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.

Best,
Peer
0
 
LVL 8

Expert Comment

by:TheNautican
Comment Utility
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.

-Naut
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:peer754
Comment Utility
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.

Thanks,
Peer
0
 
LVL 8

Expert Comment

by:TheNautican
Comment Utility
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.

-Naut
0
 

Author Comment

by:peer754
Comment Utility
Naut,

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

Thanks,
Peer
0
 
LVL 76

Expert Comment

by:David Lee
Comment Utility
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?
0
 

Author Comment

by:peer754
Comment Utility
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.

Thanks!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Publishing OWA on TMG 2010 2 23
Expando 4 33
Outlook 2013 will not archive 4 41
calendar, outlook 2 37
Get an idea of what you should include in an email disclaimer with these Top 5 email disclaimer tips.
Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
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: …

772 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

9 Experts available now in Live!

Get 1:1 Help Now