How to send an instant message from excel using communicator

Sub sendIM()

Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
Dim ToUser As String
Dim message As String

Application.ScreenUpdating = False

ToUser = “someone@somewhere.com”
message = “test vba communicator”

On Error Resume Next
Set msgr = Messenger.InstantMessage(ToUser)

‘Send message to window

msgr.SendText (message)
msgr.Close

Application.ScreenUpdating = True

End Sub

Advertisements

86 responses

26 05 2011
Jose

I tried to run this code but it gives me an error at CommunicatorAPI.IMessengerConversationWndAdvanced
command.

27 05 2011
vbaninja

Assuming you have Microsoft Communicator running you will need to add its references.

From Excel:
1) Alt-F11 (to view VBA)
2) Tools > References
3) Check box of anything beginning with Microsoft Offcie Communicator
4) Save, Close Excel, Reopen, try again

13 10 2014
Kannan Mohan

Hi,

The name Microsoft office communicator is not available in reference list. Where shall i get this?.

Thanks

Kannan M

8 07 2013
Solairajan

I am having Microsoft @ Lync 2010. Please provide me the code which connets the Lync 2010.

20 12 2013
fred

This code is working the same way with Lync, you could have tried first =)

13 07 2011
Naveen

Its awsome,,,, it is working perfect….

where can i read stuff like this more… plz tell me

14 07 2011
vbaninja

What specifically are you looking for?

15 07 2011
Naveen

Actually I was looking for a code which will check all the contact status (like idle, away, offline, online etc) continuously; actually Ur code was first step for me to get confidence to build my complete project.
When I got successful result on running Ur code posted, I thought if I get more materials to study I can make my project using them. However I have successfully built my project and it is working well now. (For the moment my requirement is fulfilled). Thanks a lot for ur post above… and I will ping u when I want some help
Thanks a lot vbaNINJA

15 07 2011
vbaninja

Glad to hear you got it working! Feel free to post your code if you feel like helpping out.

17 07 2011
Naveen

Ok here is my code and before entering into it we have to do some preworking

1. create an excel in ur local or desktop
2. create a empty module in the macro window (ALT+F11)

and paste the following code in the module1
———————————————————-
Sub TimerApp()

dTime = Now + TimeValue(“00:05:00”) ‘ this code is to automate running of the status for every 5MIN u can change according to ur convinence, and point 2 after runnig once this code it will run all the time even if ur excel is closed (to stop running for nxt time commetn out the dtime line. 🙂
With Application
.OnTime dTime, “TimerApp”
.ScreenUpdating = True
.EnableEvents = True
Sheet1.showContacts
End With
‘Other Code to run at this point
End Sub
——————————————————
Paste the below in the sheet1 macro coding window
——————————————————–
Sub showContacts()
Dim p As CommunicatorAPI.Messenger
Dim s As CommunicatorAPI.IMessengerContacts
Dim t As CommunicatorAPI.IMessengerContact
Dim o As CommunicatorAPI.IMessengerContact

Sheet1.Cells.ClearContents

Dim w As String
Dim v As Long
v = 1
w = “”
Set p = CreateObject(“Communicator.UIAutomation”)
p.AutoSignin
Set s = p.MyContacts

Dim b As Workbook
Dim sh As Worksheet
Set b = ActiveWorkbook
Set sh = b.Sheets(1)
sh.Activate
sh.Cells(1, 1).Select

For v = 1 To s.Count – 1
Set o = s.Item(v)
additem_in_sheet v, o.FriendlyName, o.Status

‘sh.Cells(v, 1).Value = o.FriendlyName
‘sh.Cells(v, 2).Value = CStr(o.Status)
Next v

update_sheet2

End Sub

Public Function GetStatus(I As Integer) As String
Dim str As String
Select Case I
Case 1
str = “Offline”
Case 2
str = “Online”
Case 6
str = “Invisible”
Case 10
str = “Busy”
Case 14
str = “Be Right Back”
Case 18
str = “Idle”
Case 34
str = “Away”
Case 50
str = “On the Phone”
Case 66
str = “Out to Lunch”
Case Else
str = “Unknown”
End Select

GetStatus = str
End Function

Sub additem_in_sheet(irow As Long, name As String, stat As Integer)

Sheet1.Cells(irow, 1) = name
Sheet1.Cells(irow, 2) = GetStatus(stat)
Sheet1.Cells(irow, 3) = Time
Sheet1.Cells(irow, 4) = Date
Sheet1.Cells(irow, 5) = DatePart(“ww”, Date, vbMonday, vbFirstFourDays)
Sheet1.Cells(irow, 6) = Month(Date)
Sheet1.Cells(irow, 7) = Year(Date)
Sheet1.Cells(irow, 8) = Day(Date)
Sheet1.Cells(irow, 9) = Hour(Time)
‘Sheet1.Cells(irow, 10) = Minute(Time)
End Sub

Sub update_sheet2()

Dim x As Long
For x = 2 To 65536
If Sheet2.Cells(x, 9) = Empty Then
empty_row = x – 1
Exit For
End If
Next x

Dim irow2 As Long
For j = 1 To 200
Sheet2.Cells(empty_row + j, 1) = Sheet1.Cells(j, 1)
Sheet2.Cells(empty_row + j, 2) = Sheet1.Cells(j, 2)
Sheet2.Cells(empty_row + j, 3) = Sheet1.Cells(j, 3)
Sheet2.Cells(empty_row + j, 4) = Sheet1.Cells(j, 4)
Sheet2.Cells(empty_row + j, 5) = Sheet1.Cells(j, 5)
Sheet2.Cells(empty_row + j, 6) = Sheet1.Cells(j, 6)
Sheet2.Cells(empty_row + j, 7) = Sheet1.Cells(j, 7)
Sheet2.Cells(empty_row + j, 8) = Sheet1.Cells(j, 8)
Sheet2.Cells(empty_row + j, 9) = Sheet1.Cells(j, 9)

Next j

End Sub
——————————————————-
Thats all
now in sheet 1 u will find the current timestamp contact status
and sheet2 is ur database..

make sure that ur MS office communicator is logged in

thanks
Naveen kumar.M

17 07 2011
Naveen

i dont know why smiles are inserted in my code…… plz use ur brain in decoding them (actually i dont know how they automatically generated)

20 07 2011
senglam

Hey Naveen, Im getting a Object variable not set (Error 91) on this line

Sheet1.showContacts

How do i define it?

Thanks

29 04 2013
Kundan

Hey Naveen, thanks for posting this beautiful post, but I am getting the compiler error, user-defined type not defined. I don’t know why i am getting this error message…any help will be appreciated..thanks

18 07 2011
ginglgs

That’s impressive! Nice coding, Naveen! Works great.

20 07 2011
senglam

Hey Naveen, Im getting a Object variable not set (Error 91) on this line

Sheet1.showContacts

How do i define it?

Thanks

20 07 2011
Naveen

OK… there are 2 seperate codes which has to be pasted in two differnt places in the above

now,,, first create a module and paste the first sub (TimerApp())
second open macro sheet1 (not module) and in sheet1 macro editior paste the second half from here
——————————————————–
Sub showContacts()

from here to end….. finally run the module it will work fine

21 07 2011
senglam

Thats what I’ve been doing. However, after fixing some syntax because when i direct copy it comes out weird, now it gives me an “automation error” instead. Any ideas what I might be doing wrong?

2 08 2011
Naveen

oh sorry for my late response pelase do the following to run my code correctly…..

after ALT+F11 (code window)
secondly press Cntrl+t (to open preferences)
in the preferences select all microsoft communicator related checkboxs…

now you are all done…. reply me if it fails

19 10 2011
Steve

The email address we use have a space in the last, first@here.com and that is causing error message invalade procedrue call. Is there a way to correct this .

28 10 2011
G

You may try using a % in place of the space.

30 11 2011
Embemnencub

Help plz!

4 01 2012
milind

ERROR SHOWING 438

4 01 2012
vbaninja

What method/property is failing?

14 06 2012
Nitin nair

Hi i tried the code above it gives an erro sub or funstion not defined
I am using Lync 2010. will this code also work with

14 06 2012
vbaninja

This code works for communicator but shouldn’t work with Lync 2010. To get more info on the Lync object library check the video linked below.

http://msdn.microsoft.com/en-us/gg482063

27 08 2012
Nitin nair

Thank you for your response.
ok after some effor i was able to run this but when i run the code it sends the message twice how do i stop this
please help!!!

4 09 2012
vbaninja

What error are you getting twice? Odds are very good that if an error appears more than once then there is a looping construct somewhere with code causing the error.

14 11 2012
Ken Momrik

what did you have to do to get it working with Lync 2010?

14 11 2012
Nitin Nair

Lync 2010 will run with the above code due to n-1 support from microsoft for Communicator. You just need to have turn right references for this code

14 11 2012
Ken Momrik

Not trying to be obtuse… but what references need to be turned on? I’ve turned on anything marked as UC, Lync, Communicator or Outlook Social and still cannot make it work. I do have Lync installed and running.

22 01 2013
vbaninja

Sorry for the slow reply. The references have changed from “Microsoft Office Communicator 2007 WAB Extension” to an API call when Lync 2010 was released. For the API call convention please see the information here: http://social.msdn.microsoft.com/Forums/en-US/communicatorsdk/thread/476e6c25-7c4a-45be-8dee-2d2b8cba87e2

23 01 2013
Ken Momrik

Apparently I’m just not a good enough programmer for this. I don’t understand how that link points me to anything resembling a answer. 😦 I’ll being looking again for a different way….

24 01 2013
vbaninja

I’m dumb, wrong link. The answer you’re looking for is “Microsoft Office Communicator 2007 API Type Library”. Tested ~2 minutes ago with Lync 2010 Version 4.0.7577.4356

29 05 2013
DonW

Also have Lync 2010.

The above code has been wonderful. However, since my company switched from Communicator to Lync 2010, I have been able to open the IM window (to the person I’m sending the message to), but NO MESSAGE is sent to the “sendto” person.

I’ve added the Communicator 2007 API Type Library and 2007 API Private Type Library to the references. Still not able to have message sent.

Any suggestions?

26 06 2012
Manu

hey, i need help in making a vba programme. it is like this
1. open a web browser , say chrome, apple safari… but certainly not Internet explorer
2.open a login page (gmail or facebook or gmx or…)
3 automatically put username and password
4 sign in to that page
all these step in one click

Can you please check this…:)

26 06 2012
vbaninja

The only real catch for this is I do not know of any libraries for the objects in Safari or Chrome. I agree IE is no bueno but it is the only browser I know of that VBA can control. That said, here is what I do know.

To open chrome use someting like shell(“C:\Users\loginname\AppData\Local\Google\Chrome\Application\Chrome.exe -url http://www.google.com“)

This link explains a solution to your problem in the IE world. If you can figure out how to control objects within Chrome or Safari using VBA I would love to hear how. Perhaps another scripting language can help.

http://excelvbaandmacros.blogspot.com/2012/02/excel-login-to-gmail-with-vba.html

2 12 2013
Uppe Ugo

why trying to hijack this thread? shame on you

21 06 2016
bob

Shame on you.

26 06 2012
Manu

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Sub Login_2_Website()

Dim oHTML_Element As IHTMLElement
Dim sURL As String

On Error GoTo Err_Clear
sURL = “http://www.gmail.com”
Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.timeout = 60
oBrowser.navigate sURL
oBrowser.Visible = True

Do
‘ Wait till the Browser is loaded
Loop Until oBrowser.readyState = READYSTATE_COMPLETE

Set HTMLDoc = oBrowser.document

HTMLDoc.all.Email.Value = “username”
HTMLDoc.all.passwd.Value = “password”

For Each oHTML_Element In HTMLDoc.getElementsByTagName(“input”)
If oHTML_Element.Type = “submit” Then oHTML_Element.Click: Exit For
Next

‘ oBrowser.Refresh ‘ Refresh If Needed
Err_Clear:
If Err 0 Then
Err.Clear
Resume Next
End If
End Sub

…..Put username and password in the above string(“username”, “password”)
you can log into gmail account…but this is applicable only for gamil…we cant use any other accounts…

11 01 2013
Sw

Hi Guys..too long!! .. I have problem as it is not sending using msgr.SendText(“”) .. , the problem might be referance ms office communicator 2007 wab can’t be added, I’m getting error adding it and can’t find away to repair it.. any idea guys?

22 01 2013
vbaninja

Yep, the references have probably changed. Lync now uses API calls. http://social.msdn.microsoft.com/Forums/en-US/communicatorsdk/thread/476e6c25-7c4a-45be-8dee-2d2b8cba87e2

8 04 2013
innocuoussoul

is there any way to alter naveen’s code to search via first/last name and provide a status? I work with lists that provide me first/last but no email. I need their status to know when I can contact them.

help?

9 04 2013
Naveen

without knowing email how the names are added in ur communicator???
you can jsut tag for status alerts in the communicator itself… no need of any seperate code…
if u want to modify the code then it is easy… before u post every name in excel sheet u use a vba function (instr) to sort ur desired name and then post in excel…. i hope this helped

thanks

9 04 2013
innocuoussoul

Ah, so this only works if they’re added to your list? I was hoping this could utilize the search function.. Thanks!

10 05 2013
Ish

Hello! Thank you for the code. Worked great! Trying to figure out how to use this same code but pull message and username from spreadsheet. Ideal end result:

ToUser = “(A2)”
message = “(B2)”

Etc
Kind of like a mail merge + this macro. Possible?

13 05 2013
vbaninja

I would wrap the executed portion in a loop of some sort.

Something like:
cnt=1 ‘start row
Do while cells(cnt,).value “”
ToUser = cells(cnt,1).value ‘1 for the A column
message = cells(cnt,2).value ‘2 for the B column

On Error Resume Next
Set msgr = Messenger.InstantMessage(ToUser)
msgr.SendText (message)
msgr.Close

cnt=cnt+1
Loop

14 05 2013
Ish

Sorry, is this in addition to existing code to replacing?

15 05 2013
vbaninja

In addition to…sort of.

Sub sendIM()

Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
Dim ToUser As String
Dim message As String
Dim cnt as Double

Application.ScreenUpdating = False

cnt=1 ‘start row
Do while cells(cnt,).value “”
ToUser = cells(cnt,1).value ’1 for the A column
message = cells(cnt,2).value ’2 for the B column

On Error Resume Next
Set msgr = Messenger.InstantMessage(ToUser)
msgr.SendText (message)
msgr.Close

cnt=cnt+1
Loop

Application.ScreenUpdating = True

End Sub

15 05 2013
Ish

Thank you! I am getting a syntax error on line = cnt=1 ‘start row

23 05 2013
Ish

Thank you!

(Edit) I am getting a syntax error on line = cnt=1 ‘start row

19 06 2014
chethanlvhan

Hi Ish,

I just happened to come across the same requirement and indeed utilized the above program.. however with minor adjustments to to this code to work.. hope the below helps!

Cheers!
Chethan

Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
Dim ToUser As String
Dim message As String
Dim cnt As Double

Application.ScreenUpdating = False

cnt = 1 ‘start row

Do While Cells(cnt, 1).Value “”
ToUser = Cells(cnt, 1).Value ‘1 for the A column
message = Cells(cnt, 2).Value ‘2 for the B column

On Error Resume Next

Set msgr = Messenger.InstantMessage(ToUser)
msgr.SendText (message)
‘msgr.Close

cnt = cnt + 1
Loop
Application.ScreenUpdating = True
End Sub

6 06 2013
Praveen

Hi there,
Is there any way, we can send instant message to yahoo messenger using excel sheet? Actually, I need to send a cell text to predefined yahoo ids.

10 06 2013
Fernando Esparza

Hello,

Is there any way to send the message for several communicator ids? i mean, to send the same message to all of them in the same window.

Regards,

22 11 2013
labssougs

Hi, my name is Leo Messi and I’m a football player.

My site: google

31 03 2014
Milind

Hi Team,

I want to do is if any person send me message on communicator then i want copy that message into my excel sheet.

Example : If my friend send me message “Hi Milind,” Then i want “Hi Milind” on my communicator.

Is this possible?

7 05 2014
Johng722

Fantastic website. A lot of useful information here. I’m sending it to a few pals ans also sharing in delicious. And naturally, thank you in your sweat! edkacdfbaecb

20 06 2014
Thad

Is there a way to add a code where if microsoft communicator is not on/signed in, the code will not run and cause a debug error, if any an error stating that you would need to log into communicator?

19 08 2014
Oscar

Hey,

The code is not running for me… It says “Syntax Error” Can you provide me the reason? THanks in Advance 😛

27 11 2014
Moro

The code run great in Lync 2010, but it does not work in Lync 2013. I was not able to find a turnaround.

Sub sendIM()

Dim msgr As CommunicatorAPI.IMessengerConversationWndAdvanced
Dim ToUser As String
Dim message As String

Application.ScreenUpdating = False

ToUser = “someone@somewhere.com”
message = “test vba communicator”

On Error Resume Next
Set msgr = Messenger.InstantMessage(ToUser)

‘Send message to window

msgr.SendText (message)
msgr.Close

Application.ScreenUpdating = True

End Sub

Many thanks!

1 12 2014
Anup Dutta

Hi, this is just fabulous job… Thanks to everyone… I am just annoyed to say that i am getting some silly error and can’t figure out what is the issue on line Do While Cells(cnt, 1).Value

I can see some special character like after this line “”. Not sure why this is? Can anyone help to resolve this issue?

I can see some special character like after this line “”. Not sure why this is? Can anyone help to resolve this issue?

1 12 2014
Anup Dutta

Just to update on this…i am getting Runtime error13:Type Mismatch Error….My excel contains the Lync email in cell A1 and Normal text message in cell B1.

1 12 2014
Anup Dutta

Oh got it resolved myself……it should be Do While Cells(cnt).Value “”

1 12 2014
Anup Dutta

seems this website doesnot allow to enter the between () symbol….

8 12 2014
marek korzeniewski

hi,

has anyone tried to add title/subject to the message window? manually you can do it when you select ‘Change Conversation Subject’ option but how to add title using VBA???

thanks
mkvarious

26 12 2014
Milind

Hi Team,

I want to do is, if any one reply or send me message on communicator. i want save that reply into excel file or notepad.

Is this possible?

Thanks in advance.

16 01 2015
Steve Boyce

How about using this code to send an IM to multiple recipients, i.e. a group message? Tried a few things, but nothing worked. From start/run you can use this cmd im:

16 01 2015
Steve Boyce

Nevermind i’ve sussed it, it just needed an array for the userlist;

Sub IM_Recip_Msg()
Dim Msg As String, X As Variant

X = Array(“abc@somewhere.com”, “def@somewhere.com”)
Msg = “Test Group IM message from VBA”
Call SendIM(X, Msg)
End Sub

Sub SendIM(ToUsers As Variant, Message As String)
Dim Msgr As CommunicatorAPI.IMessengerConversationWndAdvanced

Application.ScreenUpdating = False
On Error Resume Next
Set Msgr = Messenger.InstantMessage(ToUsers)

‘ Send message to window
Msgr.SendText (Message)
Msgr.Close
Application.ScreenUpdating = True
End Sub

19 06 2015
sdorttuii plmnr

Hello my friend! I want to say that this article is awesome, nice written and come with almost all important infos. I¦d like to look more posts like this .

23 06 2015
vbaninja

Thank you for the feedback, we are happy to help!

22 06 2015
Mick

Guys…is it possible to specify a range to allow you update the recipients without opening the code up ?

23 06 2015
vbaninja

That is doable a few different ways. You could store the list of recipients in a column and loop through until cell(row,clm).value “”. If you wish to use a named range you could loop through that list using ActiveWorkbook.Names(“NamedRange”).

More info on how to loop through a range can be found here: http://www.ozgrid.com/forum/showthread.php?t=84594

21 10 2015
Smooth

This code is great and works. Can use the same to send the message to a Lync 2010 Group Chat channel?

24 11 2015
vbaninja

Sorry for the late reply. Although I don’t have experience with group chat channels it does appear that you can define a group and post directly to a group rather than a single contact. The link I found with more info is: https://msdn.microsoft.com/en-us/library/office/hh243703(v=office.14).aspx

I hope this helps!

15 03 2016
Harry

Hello,

I have a question about messaage part. How to add range instead of one cell in Msgr.SendText?

Now in message part my code looks like that:

message = Cells(2, 1).Value

Msgr.SendText (Message)

And that gives mi a value from this cells. But I would like to add whole range, for exp. A1:D4 which will contain text and numbers to message. How can I do it? Could you help me?

18 03 2016
vbaninja

Full disclosure: I no longer have the software to run a test of my potential solution.

My first hunch is you can dimension message as a range rather than a string. Sending a range value to .sendtext could produce a type mismatch but it may be worth testing.

Another option is string concatenation using some sort of looping construct to build a large string containing the range of info you’re looking to send.

Something like this could replicate the data table range without actually sending a range type variable:

clm_A = 1 ‘first column
clm_B = 2
clm_C = 3
clm_D = 4
cnt=1
Do While Cells(cnt,1) “”
message = message & Cells(cnt, clm_A).value & ” ” & _
Cells(cnt, clm_B).value & _
” ” & Cells(cnt, clm_C).value & ” ” & Cells(cnt, clm_D).value & _
Chr(10)
cnt=cnt+1
Loop

26 04 2016
MATTHEW AUGUSTINE

Is it possible to get the Instant Message window text using API calls – so that I can copy the text to excel or write to a text file?

2 05 2016
vbaninja

We’ve not played around with it but the MSDN actually has some info on the topic at this location: https://msdn.microsoft.com/en-us/library/office/microsoft.rtc.sip_di_2_ocsserveragent.aspx

If you find a way we would love to see the result. Good luck!

10 05 2016
lstadic

I’m actually using Lync 2010 and unfortunatelly I’m unable to run those codes . It seems that excel’s VBA doesn’t recognizes “CommunicatorAPI.dll”.
Do you know what must i do to integrate it?

10 05 2016
lstadic

solved!

10 05 2016
vbaninja

Excellent! Did you get a copy of the dll?

30 12 2016
S RAJKUMAR

Hey can you tell me how did you resolve it.. Im also facing the same problem..

21 07 2016
Sivaranjani A

How To change the Conversation subject using VBA code in a lync. Kindly help on this

9 08 2016
vbaninja

I believe the ConversationContextualInfo.Subject call similar to the link below should work.

https://msdn.microsoft.com/en-us/library/microsoft.lync.controls.conversationcontextualinfo.subject_di_3_uc_ocs14mreflyncwpf(v=office.14).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-1

4 10 2016
Dinesh

When i used this code for lync 2010 it is working. But now lync was updated to Skype for Business and this code is not working. Could anyone give me solution?

4 10 2016
vbaninja

This is a very useful document from Skype with examples. http://users.skynet.be/fa258239/bestanden/skype4com/skype4com.pdf

29 05 2017

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: