Sending email to Outlook with ClickYes

[TheAntiGates]

I've cobbled this code together. All seems well but I want to terminate execution of the 3rd party app. What do you folks use to kill applications?

' You should create a reference to the Microsoft 
'Outlook 11.0 Object Library in the VBEditor (or whatever version you have)

Sub Send_Msg(sMsg As String)
Dim objOL As New Outlook.Application
Dim objMail As MailItem
DoEvents
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)
With objMail
    .To = Range("MyEmail")
    .Subject = "test Mail Response"
    .Body = sMsg
    '.Display 'this will do everything EXCEPT send - 
    'most excellent for debugging after .display
    Shell "C:\Program Files\Express ClickYes\clickyes.exe"
    'note: this program is configured to NOT start at startup; 
    'and NOT start suspended. Thus it's active RIGHT NOW!!
    .Send
    'Kill Clickyes
End With
Set objMail = Nothing: Set objOL = Nothing
End Sub

Clickyes is at http://www.contextmagic.com/express-clickyes/ and seems be be recommended by at least one Outlook MVP, FWIW

[Ken Puls]

I actually have a ClickYes script at home. If you can wait till later tonight, I'll dig it up for you...

[TheAntiGates]

Perfecto. Most excellent. Gracias.

[Ken Puls]

Here we go...

Sub Send_Msg(sMsg As String)
    Dim objOL As New Outlook.Application
    Dim objMail As MailItem
    Dim wshell As Object
'Activate ClickYes
    Set wshell = CreateObject("wscript.shell")
    wshell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")
DoEvents
    Set objOL = New Outlook.Application
    Set objMail = objOL.CreateItem(olMailItem)
    With objMail
    .To = Range("MyEmail")
    .Subject = "test Mail Response"
    .Body = sMsg
    .Send
    End With
'Kill Clickyes
    wshell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop")
Set objMail = Nothing: Set objOL = Nothing: Set wshell = Nothing
End Sub

Fwiw, I used a late bind to create the shell object. It's the way I coded it in the past, and... well... it ain't broke.

Hope it helps,

Ken

[mdmackillop]

I think that's worth a KB entry Ken.

[Ken Puls]

LOL!

Funny enough, Malcolm, I'm in the middle of typing up an article on it for my site.

I could submit it here as well though...

[Ken Puls]

http://www.excelguru.ca/node/44

[TheAntiGates]

Outstanding.

The website is also meritorious. I'll be baaack to visit it.

And maddog, thanks for the linefeed. It vastly helps, as I don't like code running off the screen(due to the long comment) any more than you or anyone does!

[TheAntiGates]

Ken, I get a 429 error from the CreateObject. I know that the brain surgeons at Microsoft only recognize this as a GetObject error, but it's for real. ("ActiveX component can't create object")

If this is a reference issue, my checked ones are
VB for Apps
M$ Excel 11.0 Object Lib
OLE Automation
MM Offic 11.0 Object Lib
M$ Forms 2.0 Object Lib
M$ Outlook 11.0 Object Lib

[Ken Puls]

Can you post the whole routine you're using and tell me which CreateObject line? Don't know if you're using the one above, or the one from my site.

I don't actually have Outlook here, so can't test for you (can later tonight), but I might be able to see it from the code...

[TheAntiGates]

Sorry, idiot user error! Thanks for getting it right, Ken. (/sheepish retreat mode on)

[Ken Puls]

So you're dying on setting the ClickYes object?

Set wshell = CreateObject("wscript.shell")

I actually have the Beta Outlook 2007 here, but it crashes when I try starting it. It allowed me to step part way through the code though...

My references are:
VB for Apps
M$ Excel 11.0 Object Lib
OLE Automation
M$ Office 11.0 Object Lib
M$ Outlook 12.0 Object Lib

Tested last night with the 11.0 version of Outlook though...

[TheAntiGates]

No, you're fine. Late last night when you posted it, I wrote it down, and foolish moi mistranslated it to CreateObject("C:... somewhere over the chasm.

Your code rocks.

[Ken Puls]

You... what...??? You wrote it down?

[TheAntiGates]

I know, I should have shot the info over on my Dick Tracy wristwatch...

or Ctrl - V doooooooooohhh