Access/Outlook Automation

Here's a good looking article from Microsoft on automation between Access and Outlook: Using Automation in Microsoft Office Access 2003 to Work with Microsoft Office Outlook 2003

Using SnapShot files to distribute reports

View as
a full page
article

Download
sample
database
A2K3 A97

This article from Microsoft Office Online reviews the use of the Snapshot file format to distribute reports.

I've used snapshot in my organization for a couple of years now and have found them very usable. They provide all of the benefits normally associated with PDF files, and can be easily generated from Access without any additional components. Compared with saving to Word or RTF they have the advantage of preserving all your report formatting.

The article covers the basics of saving a snapshot file manually from a print preview window, but it doesn't cover automating the production of snapshot files. What makes this task harder is that the the snapshot format is not covered in the help or the list of allowed constants for the relevant commands. Despite this, it's really easy once you know how. And in this post I'll tell you how.

Read more »

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
 .Send
 End With
 rsMessages.MoveNext
 Loop
 
 Set mailMyMail = Nothing
 
 rsMessages.Close
 Set rsMessages = Nothing
 
 db.Close
 Set db = Nothing
 Set ws = Nothing
End Sub

See also:

• OL98: Developer Information About the Outlook E-mail Security Update
• Outlook Redemption v. 4.4 A product that "works around limitations imposed by the Outlook Security Patch..."
• Getting started with Outlook VBA
• Distributing Microsoft Outlook VBA Code