none
send email using vba in MS Access

    Question

  • All,
      I have some basic vba code that can send an email out from MS Access. My problem is that it only works when Outlook is running. So here are my questions:

          1. Does Outlook have to be open for basic vba code to instaniate an Outlook object, create an email item, and then send it?
          2. If not, then what must I do to my code or computer environment to allow Access the ability to send emails using vba code while Outlook is not running.

         Here is my basic code:

               Dim objOutlook As Outlook.Application
              Dim objOutlookMsg As Outlook.MailItem
            
              'Create the Outlook session.
              Set objOutlook = CreateObject("Outlook.Application")
              'Create the message.
              Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

              With objOutlookMsg
                  .To = "myeamil@yahoo.com"
                 .Subject = "This is an Automation test with Microsoft Outlook"
                 .Body = "This is the body of the message." & vbCrLf & vbCrLf
                 .Send
              End With
             
              Set objOutlook = Nothing


    Thomas
    • Moved by Cindy Meister MVPMVP Saturday, August 27, 2011 5:32 PM Access-related (From:Visual Studio Tools for Office)
    Saturday, August 27, 2011 4:57 PM

Answers

  • OK, I see now. This behavior is different from previous versions - the prompt to allow a program to send a message is apparently suppressed and No is answered automatically. If I look at the properties of the message for a while in the Watch window before sending, it does get sent.

    But if I then start Outlook, the message has remained in the Outbox, to be sent almost immediately. So it looks like Outlook needs to be running (visibly) anyway...


    Regards, Hans Vogelaar
    • Marked as answer by Macy Dong Friday, September 02, 2011 1:13 AM
    Sunday, August 28, 2011 7:33 AM

All replies

  • Your code starts Outlook!
    Regards, Hans Vogelaar
    Saturday, August 27, 2011 7:29 PM
  • I tested the logic again after see Hans' post and it doesn't work on my computer. I also tried this on another laptop with office 2010 and it did not work there. If I change the .SEND to .DISPLAY it appears to work. When I use the .SEND command I get the following error:


    Thomas
    Sunday, August 28, 2011 12:40 AM
  • OK, I see now. This behavior is different from previous versions - the prompt to allow a program to send a message is apparently suppressed and No is answered automatically. If I look at the properties of the message for a while in the Watch window before sending, it does get sent.

    But if I then start Outlook, the message has remained in the Outbox, to be sent almost immediately. So it looks like Outlook needs to be running (visibly) anyway...


    Regards, Hans Vogelaar
    • Marked as answer by Macy Dong Friday, September 02, 2011 1:13 AM
    Sunday, August 28, 2011 7:33 AM
  • Hans,

     Thanks for the reply. So, are you suggesting that Outlook (part of Office 2010) must be open (visible) for vba code from another office app (in this case MS Access) can create and send an email without receiving an error?

    Thoams

     


    Thomas
    Sunday, August 28, 2011 2:51 PM
  • Yes, it looks that way to me. If Outlook 2010 isn't open (visible), I either get an error message or the email remains in the Outbox until the next time I start Outlook manually.


    Regards, Hans Vogelaar
    Sunday, August 28, 2011 2:59 PM
  • Hans,

    Thanks for the update. At this point, I think I need to follow these rules when it comes to using automation between Access and Outlook:

    1. If I want to send emails from Access via Outlook, the best thing to do is have Outlook open before running my vba code within Access to create and send emails.
    2. If Outlook is open, the emai that gets created will get sent immediately.
    3. If Outlook is NOT open, then all emails get stored in the OUTBOX until the next time Outlook opens. At which point, the user can then send the emails
    4. I need to have the appropriate Error handling logic in my vba code to handle errors that either are produced when Outlook isn't open or when the Automation server causes a problem.

      The other path to go down with my project would be to get the customer to go with using CDO and an outside SMTP server instead of using Outlook.

      I appreciate your thoughts on this issue. If you find something out that might help me out, I'd love to hear about it. And thanks for following up on this issue.

    Thomas



    Thomas
    Monday, September 05, 2011 12:34 PM
  • I have been using vbMapi from http://www.everythingaccess.com/vbMAPI.htm.

    Developer pricing is very reasonable, it is easy to implement and support is great.

    Currently using it on a large Access 2007 & Outlook 2007 project without any problems.

    Tuesday, September 06, 2011 12:37 AM