Access 2010 - VBA - How can I send email with Outlook? It just sits in outbox!

[Corwin Moyne]

Hi there. I'm a complete noob when it comes to VBA. I have some code that I got online to send an email using Outlook through Access 2010. The code works by displaying an Outlook message window, but the message sits in the outbox until I open Outlook. Is there anyway to keep Outlook open long enough for the message to send. The reason I am asking is because this database will be used in work and I cannot guarantee that the guys will open Outlook to complete the message sending process.

Here is what I have so far. I have not added any content to the message yet as I am still testing:

Private Sub Re_Order_Click()

        Dim OutApp As Object
        Dim OutMail As Object
        Dim blnOLOpen As Boolean
       
        On Error Resume Next
        Set OutApp = GetObject(, "Outlook.Application")
        blnOLOpen = True
        If OutApp Is Nothing Then
        Set OutApp = CreateObject("Outlook.Application")
        blnOLOpen = False
        End If
        On Error GoTo 0
       
        Set OutMail = OutApp.CreateItem(olMailItem)
       
        With OutMail
        .To = "email address"
        .Subject = "This is the Subject line"
        .Body = "testing"
        .Display
        '.Send - I have tried this without .display and                 does not work either
        End With
End Sub

[zmbd]

If you're sending only a single report, query etc... then the DoCmd.SendObjec t (type of obj), (name of obj), (format to send obj), (email to sendto), , , zsubject, zmessage, True would be the simplest [F1] to get the details.

Otherwise, you'll be wanting a more than a few changes to your code.

[Corwin Moyne]

I don't need to send any objects. The body of the message will contain info on the fields from the originating form. I just need to keep Outlook open long enough for the message to send.

[NeoPa]

OutApp needs to be maintained somewhere that won't die as soon as the procedure is finished. A Public variable in a persistent (Not object related) module would do the trick.

With Application Automation though, as with anything in code really, you should take resoponsibility for ensuring all objects used are closed tidily when finished with. This means ensuring Outlook is closed when it's finished with.

[zmbd]

IF you will read thru the sendobject method you will find that you can just send a message:

http://msdn.microsoft.com/en-us/library/office/ff197046(v=office.14).aspx

I use this method in all of my production DB to send me an email if an error occurs... especially in beta testing!

The only issue I've found is when Outlook is not the default email client... but I.T. has solved that for the most part by force changing all of the PC to run Office... Gota luv a good I.T. staff (and I highly respect my local team!!!!)

[fripau]

I had the same problem today.
In the end, I had to go to Outlook Options > Advanced > Send and Receive and select Send immediately. That worked. With .send, it sends immediately without displaying and with .display it displays and when you then click on send it sends immediately.

[Corwin Moyne]

I tried that a long time ago but it didn't work. I ended up bypassing outlook altogether and sending using gmail's SMTP server. Works great.

[fripau]

Thanks. Please point me in the right direction. How do you use gmail's SMTP server? I like gmail.

[Corwin Moyne]

Hey. The following link shows how. Just replace the red writing with your own details.

http://www.blueclaw-db.com/access_email_gmail.htm

[zmbd]

That link will take to a method using GMail.
You do not need to use GMail and instead one can usually have a go from within the PC, take a look at the code block here:

http://bytes.com/topic/access/answers/947246-how-send-email-access-without-using-outlook#post3742801

As Seth mentions in this post there are more complex versions.
You can also search this site for other examples of sending with CDO by using the search bar at the top of this page and the key words: Access, CDO, Email

[twinnyfo]

Corwin,

Based on the code you used in your original post, I have found that based on your network's security settings, you may not be able to send e-mail directly from outlook with your methodolgy. I have used that same code before, and I would not get an error, but the e-mail would simply not send.

I have never, however, experienced a problem of the e-mail simply sitting in the outbox (this is the whole purpose of the ".Display") . My code would always generate the e-mail, and I can navigate throughout my db while the e-mail just sat open--it required me to click send, and that was it.

Did you ever find an acceptable solution to your original question?