Email through VBA, using sendkeys

Posted on 2009-12-16
Last Modified: 2013-11-27
I'm currently trying to automate sending an email through Access 2003 using VBA. I first created an Outlook mail object, and used the .send function, but this was giving me the security dialog. I then tried using the sendkeys method to pass Alt and "S" to send the message. This works, but if the desktop is locked it won't. I assume i cannot pass a keyboard input to the locked desktop...
Has anyone any ideas of a workaround?
Leaving the desktop unlocked is not an option...
Public Sub autoEmail(address As String, message As String)


    Dim olappa As Object

    Dim oitem As Object


    Set olappa = CreateObject("Outlook.Application")

    Set oitem = olappa.createitem(0)

    With oitem

        .subject = "subject"

        .to = address

        .body = message   

        .NoAging = True


      End With

    Set olappa = Nothing

    Set oitem = Nothing

        SendKeys "%{s}", True

End Sub

Open in new window

Question by:ITUCIRL
    LVL 84
    Many people use this:

    There are other ways - Outlook Redemption, for example - but this seems to be easiest, plus there is a free version.

    You can also use CDO or a 3rd party utility. I use CSMail, and have liked the results.
    LVL 18

    Accepted Solution

    Why not use CDO for sending email?

    I've implemented it before but don't have access to that code at the moment. Try this link:

    There are also ways to prevent the security dialog. See:
     "This article solution:  If using Outlook 2003, the VBA code stored inside of the VBA Project of Outlook is assumed to be "Trusted" - this then bypasses the warning messages - then you can call this VBA code using automation of Outlook. "

    The last link may be your best one, if you are able to access the Outlook VBA environment. Not so useful if you want lots of users to be able to send email as it requires a per-client Outlook VBA routine.

    LVL 31

    Expert Comment

    The Redemption Library works well, though you do have to modify your code somewhat.  Here is code for using Redemption objects:
    'Redemption objects and corresponding Outlook objects must
       'be declared as Object type
       Dim omsg As Object
       Dim msg As Object
       Dim otsk As Object
       Dim tsk As Object
          'Create new mail message and send it now
          Call OpenOutlook
          Set omsg = gappOutlook.CreateItem(olMailItem)
          Set msg = New Redemption.SafeMailItem
          msg.Item = omsg
          With msg
             .To = strToEMail
             .Subject = strMessageSubject
             .Body = strBody
          End With
          'Note:  Messages created with Redemption Library objects
          'are created in the Drafts folder instead of the Outbox,
          'but they will be sent as if from the Outbox.
          'Create task item for sending the mail message later
          Set otsk = gappOutlook.CreateItem(olTaskItem)
          Set tsk = New Redemption.SafeTaskItem
          strTaskMessage = "When the task reminder fires, an email message will " _
             & "be created and placed in the Drafts folder to be sent"
          With tsk
             .Item = otsk
             .Subject = strTaskSubject
             .DueDate = dteReminder
             .StartDate = dteReminder
             .Categories = "Reminder"
             .Body = strTaskMessage
             'Store info for mail message in unused Task fields
             .BillingInformation = strToEMail
             .CardData = strMessageSubject
             .Mileage = strBody
             'Set task reminder for date when message should be sent
             .ReminderSet = True
             .ReminderTime = dteReminder
             .Close (olSave)
          End With
       End If
          Set onitm = fldClientsStoreID.Items(1)
    	'There is no SafeNoteItem, but SafeMailItem can be used for note items
          Set nitm = New Redemption.SafeMailItem
          nitm.Item = onitm
          Set jitm = New Redemption.SafeJournalItem
          jitm.Item = ojitm

    Open in new window

    LVL 31

    Expert Comment

    The Website for the Redemption library is
    LVL 31

    Expert Comment

    BTW, that annoying pop-up (one of Microsoft's worst security features, IMHO) is called the Object Model Guardian.  Its primary function is to protect you from running your own code on your own computer.

    Author Comment

    I tried using CDO, and it works for mail to our internal domain. I've installed IIS with SMTP on the server than runs my app. I've never used this before, but i assume i need to configure the Virtual SMTP server?
    We use MS Exchange in here, will the virtual smtp server need to know about that?
    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )

    Author Closing Comment

    Hi guys

    I got it working. Thanks for all the help. Helen, i'd already started on using CDOs, but thanks anyway!

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Access question - SELECT/UPDATE 11 25
    Saving history changes to sub form 4 17
    Access 2003, modify export spec 11 14
    Open Args 16 24
    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now