Excel VBA -通过Lync / Communicator发送组消息

Question

我试图使用VBA从Excel通过Lync/Microsoft通信器向多个用户发送组消息。

下面的代码适用于单个电子邮件地址/用户，但如果提供了两个电子邮件地址的单元格范围，则会给出“Object IMessengerAdvanced‘的方法’IMessengerAdvanced‘失败”错误。如有任何建议，将不胜感激。

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

根据下面这个主题，InstantMessage(Object)方法应该适用于>1用户，但在实践中，它似乎并不适用于.

Lync notification of offline people using VBA

Answer 1

当发送到组时，该接口需要一个电子邮件地址的数组。

而不是：

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

测试这个：

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