• 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.

    Code:
    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 = "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
    Syndication:
    This article has also been published at Professional Office Developers Association.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!