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

MS Project- Use the TaskTable to Get the Fields Project Displays



If you're building an application that uses data about tasks in an MS Project project plan, you may simply need all the properties of all the tasks, or you may wish to use the data as it's currently displayed in MS Project. A task has dozens of pieces of data associated with it; only some are displayed in the current view.

Read more »

MS Project Automation Links

MS Project is part of Microsoft's Office family and so behaves like the other Office tools in terms of providing a rich object model for VBA development. I've built integration between Project and Access and between Project and Excel. Here are some links you might find useful if you're considering Project automation:

Read more »

Download the Automation Help File from Microsoft

Automation is the term used to describe one application using another to do its work- for instance Access using Excel to create a new spreadhseet and to put data from a into the cells of the worksheet. Automation is tremendously powerful, but can seem like black magic to the "uninitiated".

Microsoft provides references which help you get started with automation using Microsoft Office products. They are distributed as compiled help files. The most recent version of the help file is here.

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

Excel won't close!

If you're using automation from Access to Excel (or I suppose between any two MS Office apps) and the application you've opened through automation won't close, it may be because of "unqualified references". For a complete explanation see http://support.microsoft.com/kb/319832.

The likely culprit is a line like:

  Cells(6,3)=SomeValue

VBA figures out that Cells must refer to the active worksheet of the excel application, but when it does so it leaves something hanging somewhere and bad things happen later.

Instead, specify what Cells relates to- something like:

  dim xlMySheet as Excel.Worksheet
  ... (you have to Set xlMySheet to the worksheet in here somewhere)
  xlMySheet.Cells(6,3)=SomeValue

The other symptom you sometimes see caused by this error is "it works once, but then doesn't work the second time".