asked on

Email through VBA, using sendkeys

Hi
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
        .Display
      End With
    Set olappa = Nothing
    Set oitem = Nothing
        SendKeys "%{s}", True
End Sub

Open in new window

Scott McDaniel (EE MVE )

Many people use this:

http://www.contextmagic.com/express-clickyes/

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.

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Helen Feddema

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
         .Send
      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.
   Else
      '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
         .Display
         .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

Helen Feddema

The Website for the Redemption library is http://www.dimastr.com

Helen Feddema

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.

ITUCIRL

ASKER

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?

SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ITUCIRL

ASKER

Hi guys

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