Access VBA Macro Programming

Recently, I had the task of writing a program to populate an SLA (Service Level

Agreement) Report. The SLA Report was a Word document with many tables of data and

charts, but the input came from several databases. Using the methods just detailed, I was able

to write VBA code that worked through the database, extracted the relevant data, and placed

the figures in the correct tables or charts in the Word document. Previously, it had taken

someone half a day to do this manually, but the code accomplished it in under five minutes.

Sub Test_Word()

Dim oWd As Word.Application, oWdoc As Word.Document

Dim orx As Word.Range, ot As Word.Table

Set oWd = CreateObject("Word.Application")

Set oWdoc = oWd.Documents.Add

Set orx = oWdoc.Range

Set ot = oWdoc.Tables.Add(orx, 4, 5 )

ot.Cell(1, 1 ).Range.Text = "test"

oWdoc.SaveAs ("MyTest")

oWdoc.Close

oWd.Quit

Set oWdoc = Nothing

Set oWd = Nothing

End Sub

This example cannot be done from within the Access menu unless you manually copy and

paste, which can get laborious if there is a lot of data. This is a good example of the macro

language giving the user enormous power to automate tasks within Microsoft Office and

enabling you to work outside the menu structure that Microsoft has put in place.

Driving Microsoft Outlook

You can use exactly the same technology to drive Microsoft Outlook and make it send

e-mails from your database or capture address book entries. Of course, Access has e-mail

features for e-mailing an entire database, but this method lets you supply just a part of the

sheet.

In order to use this code, you must have Microsoft Outlook installed on your computer

(not Outlook Express or Windows Mail), although Outlook does not need to be actively

running for this to work.

Start off by adding a reference to the Object Library file for Outlook. You do this by

selecting Tools | References from the Visual Basic Editor menu. Choose the Microsoft

Outlook Object Library and then click the check box next to it, as shown in Figure 17-2.

Next, enter the following code into a module:

Sub Test_Outlook()

Dim oFolder As Outlook.MAPIFolder

Dim oItem As Outlook.MailItem

Dim oOutlook As New Outlook.Application

224 Microsoft Access 2010 VBA Macro Programming