email using Access and VBA without MAPI

Question

I would like to send email from Microsoft Access unattended using VBA. I understand that the built-in method “SendObject” uses MAPI meaning security prompts and something like Outlook configured. Since I want to use the Task Scheduler to kick off different reports, I’m leaning away from MAPI and would prefer some other solution. Not an application for shipping but just in-house. Ideas?

score 5 · Answer 1 · edited Mar 01 '11 at 14:30

Here's the test code that worked for me with CDO and gmail.

Sub mtest()

Dim cdoConfig
Dim msgOne

Set cdoConfig = CreateObject("CDO.Configuration")
With cdoConfig.Fields
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "gmailname"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "yourpw"

.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

.Update
End With

Set msgOne = CreateObject("CDO.Message")
Set msgOne.Configuration = cdoConfig
msgOne.To = "target@target.com"
msgOne.From = "I@dontThinkThisIsUsed.com"
msgOne.Subject = "Test email"
msgOne.TextBody = "It works just fine"
msgOne.send
End Sub

been looking for code that works perfectly with gmail. I messed up on the smtpauthenticate configuration. thanks! — killerbarney, Nov 26 '11 at 19:56

score 1 · Accepted Answer · answered Apr 20 '09 at 19:36

1

You'll need an SMTP server that will allow you to send email. Then you need to use the CDO message object.

answered Apr 20 '09 at 19:36

JeffO

7,957
3
44
53

I thought CDO came with IIS - I use it on the client? For the SMTP server would I be able to use something like gmail? – Knox Apr 20 '09 at 19:39
1

Yes, use it in the VBA. I haven't found anything that limits this to IIS or even Exchange, but you may have issues with your provider. I don't know enough about gmail. I'm guessing if they let you connect with Outlook through SMTP (I know you don't want to do this with your application, but it would be a good connection test.). – JeffO Apr 20 '09 at 20:06
I was really using gmail as an example; I think they use an unusual port or something. I'll give CDO a try. – Knox Apr 20 '09 at 22:14

score 1 · Answer 3 · answered Apr 20 '09 at 20:23

1

You might find Tony Toews's Access EMail FAQ handy.

answered Apr 20 '09 at 20:23

David-W-Fenton

22,871
4
45
58

I downvoted because the answer was a stub, and the page you link to has non-working code and some advice that is not aimed at solutions (even in 2009). – Henrik Erlandsson Mar 15 '19 at 15:18

Ron · Answer 4 · 2009-04-21T21:19:24.283

I do it this way, note, you must have Outlook installed for it to work.


Sub btnSendEmail_Click()
    Dim OutApp As Object
    Dim OutMail As Object

    Application.ScreenUpdating = False
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon

    strBody = "<html><head></head><body>"
    strBody = strBody & "Your message goes here"
    strBody = strBody & "</body></html>"

    Set OutMail = OutApp.CreateItem(0)

    OutMail.To = "name@example.com"
    OutMail.BCC = "bcc@example.com"
    OutMail.Subject = "Test message"
    OutMail.HTMLBody = strBody


    OutMail.Send  'Send | Display
    Set OutMail = Nothing
End Sub

I didn't want to use Outlook because this will be running unattended possibly in an account with no outlook installed. — Knox, Apr 21 '09 at 21:38

score 1 · Answer 5 · answered Jun 11 '09 at 06:25

1

Outlook Redemption is free and very widely used: http://www.dimastr.com/redemption/

It is very very close to the original outlook object model, so the learning curve is cake:)

answered Jun 11 '09 at 06:25

Oorang

6,630
1
35
52

email using Access and VBA without MAPI

5 Answers5

Linked