Send Email Using ClickYes
Posted by Ken Puls, CMA, Microsoft MVP (Excel) on 17th April 2007
Introduction
Every now and then, you may need to send an email from another application through Outlook. If you've ever thought about automating this process using Outlook 2000, Outlook 2002 or Outlook 2003, then no doubt you've run into the dreaded Outlook security prompt upon trying to send:
This article covers using Express ClickYes from ContextMagic, to deal with the issue. It does not completely avoid the Outlook security prompt, but it does dismiss it after a short period of time. One of the things that makes Express ClickYes more attractive than competitive solutions is that Express ClickYes is licensed to use for free in both personal and commercial activities.
It should also be noted that each of the examples in this section use a Late Bind. If you are not familiar with the difference between Early and Late Binding, please read my article on Early vs Late binding.
Versions Tested:
These routines were tested successfully using Express ClickYes v1.2, on Windows XP Pro (SP2). Microsoft Office version tested include:
- Excel 2003 to Outlook 2003
- VBS to Outlook 2003
Requirements:
Once Express ClickYes has been downloaded and installed, this code can be placed in a standard module in any Office application. It is assumed that you will change the recipient, subject and body to suit.
'Author      : Ken Puls (www.excelguru.ca)
'Macro Purpose: To send an email through Outlook without worrying about
'Â Â Â Â Â Â Â Â Â Â Â Â Â Â security prompts
Dim objOL As Object
Dim objMail As Object
Dim objwShell As Object
Dim strEmail As String
'Set your email address here
strEmail = "youraddy@yourdomain.com"
'Turn on error handling
On Error GoTo Cleanup
'Activate ClickYes
Set objwShell = CreateObject("wscript.shell")
objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -activate")
'Bind to Outlook
Set objOL = CreateObject("Outlook.Application")
'Create a new email and send it
Set objMail = objOL.CreateItem(0)Â Â Â '0=olmailitem
With objMail
.To = strEmail
.Subject = "Testing ClickYes Routine"
.Body = "This is a test of the ClickYes program"
.Send
End With
'Stop Clickyes
objwShell.Run ("""C:\Program Files\Express ClickYes\ClickYes.exe"" -stop")
Cleanup:
'Release all objects
Set objMail = Nothing
Set objOL = Nothing
Set objwShell = Nothing
On Error GoTo 0
End Sub
Alternatives To Running From Office
The first time I ever used ClickYes was building an application for a client. We needed to email an Access report daily, including weekends. Naturally, this made it rather difficult as someone would need to be there to do it. Rather than dealing with an OnTime script to produce and email the report, we elected to use a scheduled VBS Script to do it.
This VBS Script was scheduled through Windows Task Scheduler to run at a certain time of night. It would then:
- Create an instance of Access
- Retrieve the required data into a recordset
- Create an instance of Outlook
- Create a new email to the client, with the Access data in the body of the email
- Send the email using Express ClickYes to deal with the security prompt
- Close all the applications down
Article Source:
This article was originally published, and is maintained at Excelguru.ca.
Posted in Office (All), Outlook | 2 Comments »