CODEWITHSUNDEEP
ms-accessemailvbams-access-2007mapi
Knox
Knox

Reputation: 2919

email using Access and VBA without MAPI

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?

Upvotes: 5

Views: 22791

Answers (5)

Knox
Knox

Reputation: 2919

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

Upvotes: 5

Oorang
Oorang

Reputation: 6780

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:)

Upvotes: 1

Ron
Ron

Reputation: 1794

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 = "[email protected]"
    OutMail.BCC = "[email protected]"
    OutMail.Subject = "Test message"
    OutMail.HTMLBody = strBody


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

Upvotes: 1

David-W-Fenton
David-W-Fenton

Reputation: 23067

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

Upvotes: 1

JeffO
JeffO

Reputation: 8043

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

Upvotes: 1

Related Questions