• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2729
  • Last Modified:

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

0
ITUCIRL
Asked:
ITUCIRL
  • 3
  • 2
  • 2
  • +1
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
SimonCommented:
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:

http://www.rondebruin.nl/cdo.htm

There are also ways to prevent the security dialog. See:
http://www.outlookcode.com/article.aspx?ID=52
and
http://msdn.microsoft.com/en-us/library/aa168346%28office.11%29.aspx
and
 "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. "
http://www.everythingaccess.com/tutorials.asp?ID=Outlook-Send-E-mail-Without-Security-Warning

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.

Simon
0
 
Helen FeddemaCommented:
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

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Helen FeddemaCommented:
The Website for the Redemption library is http://www.dimastr.com
0
 
Helen FeddemaCommented:
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.
0
 
ITUCIRLAuthor Commented:
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?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
0
 
ITUCIRLAuthor Commented:
Hi guys

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now