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 our article on Early vs Late binding.
These routines were tested successfully using Express ClickYes v1.2, on Windows XP Pro (SP2). Microsoft Office versions tested include:
- Excel 2003 to Outlook 2003
- VBS to Outlook 2003
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.
Sub EmailWithClickYes() '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 = "email@example.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
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
This article has also been published at Professional Office Developers Association.