Excel 2007 & Office Communicator 2007 - Sending Cell Content to OC from Excel

[iherndon]

I currently have a working macro assigned to a button where when run will instant message a list of specified users with a particular message based on whatever information is in cell A10.

What I really need to be able to do is have it send the contents of A10:B:10 at the same time but that doesn't seem to work.

Anyone have any ideas?

Sub Macro1()
'
' Macro1 Macro
'


Dim X As Variant
X = Array("email1@dot.com", "email2@dot.com")
Dim msg As String
msg = Sheets("Task Assignments").Range("A10").Value
Call sendIM(X, msg)

End Sub


Sub sendIM(toUsers As Variant, message As String)
Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
Set msgr = Messenger.InstantMessage(toUsers)
msgr.SendText (message)
Set msgr = Nothing

End Sub

Thanks in advance for your help!

-Ian

 

[GermanPath]

This is the easiest variant:

Sub SendMessage_OCS()
Dim X As Variant
Dim msg As String
    msg = msg & Sheets("Task Assignments").Range("A10").Value & vbTab & Sheets("Task Assignments").Range("B10").Value & vbCrLf
    X = Array("[EMAIL="email1@dot.com"]email1@dot.com[/EMAIL]", "[EMAIL="email2@dot.com"]email2@dot.com[/EMAIL]")
    Call sendIM(X, msg)
End Sub
 
Sub sendIM(toUsers As Variant, message As String)
 Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
 Set msgr = Messenger.InstantMessage(toUsers)
 msgr.SendText (message)
 Set msgr = Nothing
 
End Sub

This can enhanced by using an array for an wider range. But for two cells this should be ok.

 

[iherndon]

This is the easiest variant:

Sub SendMessage_OCS()
Dim X As Variant
Dim msg As String
    msg = msg & Sheets("Task Assignments").Range("A10").Value & vbTab & Sheets("Task Assignments").Range("B10").Value & vbCrLf
    X = Array("[EMAIL="email1@dot.com"]email1@dot.com[/EMAIL]", "[EMAIL="email2@dot.com"]email2@dot.com[/EMAIL]")
    Call sendIM(X, msg)
End Sub
 
Sub sendIM(toUsers As Variant, message As String)
 Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
 Set msgr = Messenger.InstantMessage(toUsers)
 msgr.SendText (message)
 Set msgr = Nothing
 
End Sub

This can enhanced by using an array for an wider range. But for two cells this should be ok.

Bingo! That did it. Thank you kind sir. ^_^

Would this next part be more appropriate for a new thread altogether or is it okay to ask in this same one? I'd like the font formatting in excel to remain the same when sent to OC. As of right now it sends as plain text. If part of the cell content is bolded it will not IM bolded.

 

[GermanPath]

As far as I know the Communicator 2007 API Reference documentation this is not scriptable.

 

[iherndon]

As far as I know the Communicator 2007 API Reference documentation this is not scriptable.

Thank you for confirming that. I wasn't able to find anything online elsewhere either that suggested it was possible to carry over font formatting. Thanks again!