vbaexcellyncoffice-communicator

Excel VBA - Sending Group Message via Lync / Communicator API


I am trying to send a group message to more than one user over Lync/Microsoft Communicator from Excel using VBA.

The below code works for a single e-mail address/user but if a cell range of two e-mail addresses is provided, it gives "Method 'CreateGroup' of Object IMessengerAdvanced' failed" error. Any advice would be greatly appreciated.

Sub sendIM()
 Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
 Dim msgTo As Variant
 msgTo = Sheets("Sheet1").Range("A1:A2").Value
 msgr = Messenger.InstantMessage(msgTo)
 msgr.SendText ("Test")
End Sub

The InstantMessage(Object) method supposedly works for >1 user according to this previous topic below, but in practice it doesn't seem to...

Lync notification of offline people using VBA


Solution

  • The interface expects an Array of email addresses when sending to a group.

    instead of:

    Sub sendIM()
     Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
     Dim msgTo As Variant
     msgTo = Sheets("Sheet1").Range("A1:A2").Value
     msgr = Messenger.InstantMessage(msgTo)
     msgr.SendText ("Test")
    End Sub
    

    test this:

    Sub sendIM()
     Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
    
     Dim msgTo() As Variant
     ReDim msgTo(0 To 0)                       'Allocate first element
     For Each cell In Sheets("Sheet1").Range("A1:A2")'put your range here
         msgTo(UBound(msgTo)) = cell.Value2          'Assign the array element
         ReDim Preserve msgTo(UBound(msgTo) + 1) 'Allocate next element
     Next
     ReDim Preserve msgTo(LBound(msgTo) To UBound(msgTo) - 1)  'Deallocate the last, unused element
    
     'sometimes you need to use Set, sometimes you dont, depending on environment you have, or maybe OPTION EXPLICIT
     Set msgr = Messenger.InstantMessage(msgTo)
     'msgr = Messenger.InstantMessage(msgTo)
     msgr.SendText ("Test")
    End Sub