email using Access and VBA without MAPI

C

5

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?

Cr answered 20/4, 2009 at 19:26 Comment(0)

K

1

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

Kaduna answered 20/4, 2009 at 19:36 Comment(3)

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? – Cr 20/4, 2009 at 19:39

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.). – Kaduna 20/4, 2009 at 20:6

I was really using gmail as an example; I think they use an unusual port or something. I'll give CDO a try. – Cr 20/4, 2009 at 22:14

C

5

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

Cr answered 21/4, 2009 at 21:3 Comment(1)

been looking for code that works perfectly with gmail. I messed up on the smtpauthenticate configuration. thanks! – Chalcanthite 26/11, 2011 at 19:56

K

1

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

Kaduna answered 20/4, 2009 at 19:36 Comment(3)

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? – Cr 20/4, 2009 at 19:39

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.). – Kaduna 20/4, 2009 at 20:6

I was really using gmail as an example; I think they use an unusual port or something. I'll give CDO a try. – Cr 20/4, 2009 at 22:14

W

1

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

Wristlet answered 20/4, 2009 at 20:23 Comment(1)

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). – Aluminothermy 15/3, 2019 at 15:18

F

1

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

Festal answered 21/4, 2009 at 21:13 Comment(1)

I didn't want to use Outlook because this will be running unattended possibly in an account with no outlook installed. – Cr 21/4, 2009 at 21:38

P

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

Plica answered 11/6, 2009 at 6:25 Comment(0)

Recommended topics

Hot tags