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...
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