[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Email through VBA, using sendkeys

Posted on 2009-12-16
8
Medium Priority
?
2,501 Views
Last Modified: 2013-11-27
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
Comment
Question by:ITUCIRL
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 85
ID: 26061738
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
 
LVL 18

Accepted Solution

by:
Simon earned 1000 total points
ID: 26061883
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26062951
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26062965
The Website for the Redemption library is http://www.dimastr.com
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 26062972
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
 

Author Comment

by:ITUCIRL
ID: 26071156
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
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 26071180
0
 

Author Closing Comment

by:ITUCIRL
ID: 31666704
Hi guys

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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

873 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