Send Email Using ClickYes

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 our 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 versions 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.

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 protected]"

    'Turn on error handling
    On Error GoTo Cleanup

    'Activate ClickYes
    Set objwShell = CreateObject("wscript.shell")
    objwShell.Run ("""C:Program FilesExpress ClickYesClickYes.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 FilesExpress ClickYesClickYes.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

Syndication:
This article was also been published at Professional Office Developers Association (before it went offline).