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?

Answer 1

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 = "[email protected]"
msgOne.From = "[email protected]"
msgOne.Subject = "Test email"
msgOne.TextBody = "It works just fine"
msgOne.send
End Sub