Send instant message on Office Communicator using Excel VBA -

i want send instant message using office communicator , excel vba. use excel sheet containing list of email ids.

**a            b    c** serial no   name    email 1            abc    abc.abc@abc.com 2            xyz    xyz.xyz@xyz.com 3            pqr    pqr.pqr@pqr.com 

i wrote below code send message. not working. have enabled communicator reference in vba.

sub sendim() dim msgr communicatorapi.imessengerconversationwndadvanced dim touser string dim message string application.screenupdating = true     each cell in columns("c").cells.specialcells(xlcelltypeconstants)     on error resume next         if cell.value "?*@?*.?*"                 touser = chr(34) & cell.value & chr(34)                 'msgbox touser                 message = "hi " & cells(cell.row, "b").value _                       & vbnewline & vbnewline & _                         "how you"                 set msgr = messenger.instantmessage(touser)                 msgr.sendtext (message)         end if     next cell application.screenupdating = true end sub  

for single email id, working. use below mentioned code send single message.

sub sendim() dim msgr communicatorapi.imessengerconversationwndadvanced dim touser string dim message string application.screenupdating = true touser = "abc.abc@abc.com" message = "hai" on error resume next set msgr = messenger.instantmessage(touser) msgr.sendtext (message) application.screenupdating = true end sub 

but need loop through sheet message sending everyone. changes have make work ?

note: email ids here mentioned not real.

i have never worked office communicator since saying second code works try this. (untested)

sub sendim()     dim msgr communicatorapi.imessengerconversationwndadvanced     dim touser string, message string     dim acell range     dim ws worksheet      '~~> change relevant sheet     set ws = thisworkbook.sheets("sheet1")      application.screenupdating = false      ws         '~~> why on error resume next? if know error going         '~~> handle it. time being, skipping record         '~~> keeping out of loop         on error goto skipit          each acell in .columns("c").cells.specialcells(xlcelltypeconstants)             if acell.value "?*@?*.?*"                 touser = acell.value '<~~ don't need quotes                 message = "hi " & .cells(acell.row, "b").value _                           & vbnewline & vbnewline & _                           "how you"                 set msgr = messenger.instantmessage(touser)                 msgr.sendtext (message)                  doevents '<~~ let excel send message. give time             end if skipit:         next acell     end      application.screenupdating = true end sub 

edit

an improved version. takes care of error handling

sub sendim()     dim msgr communicatorapi.imessengerconversationwndadvanced     dim touser string, message string     dim acell range     dim ws worksheet      '~~> change relevant sheet     set ws = thisworkbook.sheets("sheet1")      application.screenupdating = false      ws         each acell in .columns("c").cells.specialcells(xlcelltypeconstants)             if acell.value "?*@?*.?*"                 touser = acell.value '<~~ don't need quotes                 message = "hi " & .cells(acell.row, "b").value _                           & vbnewline & vbnewline & _                           "how you"                  '~~> place can think error happen                 on error resume next                 set msgr = messenger.instantmessage(touser)                  '~~> check if object created                 if not msgr nothing msgr.sendtext (message)                 set msgr = nothing                 on error goto 0                  doevents '<~~ let excel send message. give time             end if         next acell     end      application.screenupdating = true end sub