Friday, November 9, 2007

Send e-mail through Outlook without a security warning

A common feature sought in Access applications is sending e-mail. If you're going to send a bunch of messages, such as reminders to each of the team members with due dates next week, you'll run acrosss Outlook's anti-spam provisions, which will require you to approve each message. There are a number of different solutions posted on the web. We'll look at two here- one from the web and one of my own.

Option one

This article caught my eye: it sees you create a function in Outlook that Access can call to send the message. Since code running within Access is trusted the security warning won't come up.

Option two

Here's another approach that also relies on the fact that code running within Outlook is trusted. Have Outlook open up the Access database to get data for the e-mails and do all the sending. This approach allows you to easily use more of Outlook's features, but may not tie into your user's workflow as it will be launched by the user from within Outlook.

For this exercise here's sample data that will be stored in an Access database:

In this case (which is really simplified for the case of the example) the column "Body" includes the HTML code for the message.

The VBA code for this solution will go into an Outlook VBA module. Just like other hosting applications, you get at the code by pressing Alt-F11. Once you have the code in and tested you can run it from Tools/Macro or you can make a toolbar button that launches it. One more thing- I tested this in Outlook2003 only. Oh, and you'll need a reference to DAO under Tools/References [show me].

Sub SendMessages()
 Dim mailMyMail As MailItem
 Dim rsMessages As DAO.Recordset
 Dim db As DAO.Database
 Dim ws As DAO.Workspace
 Set ws = DAO.DBEngine(0)
 Set db = ws.OpenDatabase(--- put the path and name of your database here ---)
 Set rsMessages = db.OpenRecordset(Name:="tblMessages", Options:=dbReadOnly)
 Do Until rsMessages.EOF
 Set mailMyMail = CreateItem(olMailItem)
 With mailMyMail
 .To = rsMessages!ToList
 .cc = rsMessages!CCList
 .Subject = rsMessages!Subject
 .HTMLBody = rsMessages!Body
 End With
 Set mailMyMail = Nothing
 Set rsMessages = Nothing
 Set db = Nothing
 Set ws = Nothing
End Sub

See also:


a.p.r. pillai said...

The examples on Emails covered in your blog is interesting especially accessing MS-Access from Outlook through VBA Code, which I have not thought of trying so far.

I have MS-Access Applications that sends Emails through Lotus Notes, configuring MS-Outlook, within our LAN and I have touched this subject with few examples of my own, in my Blog (formerly

Nice work.

a.p.r. pillai

JP said...

I was looking for some basic Access automation code, thanks for posting this. I've just started using Access and would much prefer to use VBA rather than the interface (which I am struggling to understand).

Thx again,

Email Security Solutions said...

Great post. Best wishes.

VBA programmer said...

I have been looking for a sample code like this for sometime already. I will be using it to automate the sending of email messages to members of my team. I will do a little modification to the code, but you have already provided the basic functionality. Thank you very much for this example. More power to you blog. Thanks again! :)