Excel VBA - Sending Group Message via Lync / Communicator API

Question

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

Answer 1

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