Sending EMail via Outlook

Suppose you want to be able to send e-mail messages to people listed in a table named Customers in an Access database. You're absolutely certain that you can send and receive e-mail with Microsoft Outlook. (Important: None of the code described here works with Outlook Express or a Web browser.) For this example, you want to create a standard form-letter-type e-mail message to whatever customer a user chooses from a drop-down list, as in Figure 14-4. There, the controls named MsgAddress and MsgSubject are text, and MsgBody is a memo field.

Microsoft Outlook has built-in security to prevent you from sending huge mass mailings from Access. If you're thinking of using it to flood the Internet with some junk e-mail message, it doesn't work.

When the user clicks the Send button in Figure 14-4, you want VBA to create and display an e-mail message. Because you'll call on Outlook to do the job, the first step in the VBA Editor is to choose ToolsOReferences from the menu bar and set a reference to the Microsoft Outlook 12.0 Object Library.

To write the procedure to create the message, create a procedure that's attached to the Send button. In our example, that button is named SendMailBttn. All the code for the procedure is shown in Listing 14-1.

_Chapter 14: Integrating with Other Office Applications 321

Listing 14-1: Sending Mail via Outlook

Private Sub SendMailBttn_Click()

'Open an instance of Microsoft Outlook, name it Olk.

Dim Olk As Outlook.Application

Set Olk = CreateObject("Outlook.Application")

'Create a new, empty Outlook e-mail message.

Dim OlkMsg As Outlook.Mailltem

Set OlkMsg = Olk.Createltem(olMailltem)

'Put data from form into the new mail message. With OlkMsg

'Make MsgAddress the "To" address of message. Dim OlkRecip As Outlook.Recipient Set OlkRecip = .Recipients.Add(Me![MsgAddress]) OlkRecip.Type = olTo .Subject = Me![MsgSubject] .Body = Me![MsgBody] 'Display the finished message. .Display End With

'Clean up object variables, then done. Set Olk = Nothing Set OlkMsg = Nothing Set OlkRecip = Nothing End Sub

Figure 14-4:

Controls on a sample e-mail form.

MsgSubject MsgAddress

MsgSubject MsgAddress

Figure 14-4:

Controls on a sample e-mail form.

MsgBody

The procedure looks like a lot of code. Like all procedures, though, it's just a series of small, simple steps. The procedure reaches into Outlook and creates a new, empty e-mail message. The code then fills in that new message with data from the MsgAddress, MsgSubject, and MsgBody controls on the form and displays it — and that's the end of it. Take a look at the code one chunk at a time.

The first two statements under the first comment declare an object variable named Olk and set it to an open instance of Microsoft Outlook:

'Open an instance of Microsoft Outlook, name it Olk.

Dim Olk As Outlook.Application

Set Olk = CreateObject("Outlook.Application")

The Application object for Outlook lets you declare a create object variable of the type Outlook.Mailltem, to which you can then assign a new, blank e-mail message. In the following code, we create a new, blank e-mail message named OlkMsg. (The name OlkMsg is one we just made up. The Olk. in Olk.CreateItem(... is a reference to the open Outlook program, and olMailltem is a constant from the Outlook object library.)

'Create a new, empty Outlook e-mail message.

Dim OlkMsg As Outlook.Mailltem

Set OlkMsg = Olk.Createltem(olMailltem)

The With...End With block of code sets properties for the newly created e-mail message, OlkMsg:

With OlkMsg

End With

Within the With...End With block of code, the first three lines provide the recipient's e-mail address. The first line, as shown here, declares a new object variable named OlkRecip as an Outlook.Recipient object. This is a general object for e-mail addresses and can be a To, CC, or BCC (blind carbon copy) address field. The second line sets the OlkRecp value to whatever e-mail address is on the MsgAddress control on the form. The last line, OlkRecip.Type=olTo, turns the recipient address into the To address (where olTo is a constant from the Outlook object library):

'Make MsgAddress the "To" address of message. Dim OlkRecip As Outlook.Recipient Set OlkRecip = .Recipients.Add(Me![MsgAddress]) OlkRecip.Type = olTo

The next two lines copy the contents of the form's MsgSubject and MsgBody controls into the Subject line and body of the e-mail message:

The last line within the With...End With block displays the message, using the syntax OlkMsg.Display:

'Display the finished message. .Display End With

Note that all those properties are being applied to the new e-mail message named OlkMsg. The full syntax would be OlkMsg.Subject=..., OlkMsg. Body=, and OlkMsg.Display. But here you can omit the OlkMsg part of the name because the With OlkMsg statement means All properties and methods from here to End With refer to the new e-mail message named OlkMsg.

After the message is displayed, the job is done. Although it isn't absolutely necessary, we did a little housekeeping at the end of this procedure, by setting the object variable names we created to Nothing. Doing so breaks the link between the name and object and reclaims any memory that those things were using:

'Clean up object variables, then done. Set Olk = Nothing Set OlkMsg = Nothing Set OlkRecip = Nothing

End Sub

When you run the procedure, you see why the Access-Outlook combination really isn't appropriate for any mass mailing. When you first run the procedure, you see a security warning, as at the top of Figure 14-5. You need to grant permission (for a maximum of ten minutes). The bottom half of the figure shows the displayed message in Outlook.

^\NG/ If you use the OlkMsg.Send method rather than the OlkMsg.Display method to send the message without first opening it in Outlook, the real torture sets in as it makes you wait a few seconds and then answer Allow before each sent message. If you were trying to send out hundreds or thousands of messages, you would click Allow over and over again for a long time.

Like all object libraries, Outlook's library is quite large and not something that we can discuss in detail here. It would take more pages than there are in this entire book to even list and briefly define each object, property, method, and event in the Outlook object library. After you set a reference to Microsoft Outlook in the VBA Editor References dialog box, you can get information from the Object Browser. Everything described in this example comes from Outlook's Application object (see Figure 14-6).

Figure 14-5:

Outlook security prevents mass mailings.

Figure 14-5:

Outlook security prevents mass mailings.

Figure 14-6:

The Outlook Application object selected in the Object Browser.

^ Object Browser

'JA&lf

Outlook

- i MM m]

1

3 üJT

Library

I Class | Member

Mo items found

Classes

Members of'Application'

0 AddressRuleConditilA

ß ContextMenuClose

A-

01Application

CopyFile

0 Appointmeniltem

^ Create Item

0 AssignToCategoryR

CreateltemFromTemplate

0 Attachment

-ÄlÜreataÖbject

0 Attachments

& DefaultProfileName

0 AttachmentSelection

EÜ? Explorers

=

0 AutoFormatRule

f FolderContextMenuDlsplay

0 AutoFormatRules

GetNamespace

0 BusinessCardView

GetObjectReference

0 CalendarModule

& Inspectors

0 CalendarSharing

IsSearchSynchronous

0 CalendarView

& IsTrusted

0 CardView

f ItemContextMenuDisplay

0 Categories

v|

$ ItemLoad

V

Function Create0bject(0jbyeci/Ve/r>e/l3 String) As Object Member of Outlook.Application

The Microsoft Web site is another good resource for getting more information on automating activity between Access and Outlook. To see what's available, go to http://search.microsoft.com and search for Access Outlook Automation.

Whenever you're searching the Microsoft Web site for information on programming interactions between Office programs, include both program names and the word Automation in your search.

Was this article helpful?

0 -1

Responses

  • armida
    HOW DO YOU SEND EMAIL FROM ACCESS 2007 FORM USING VBA?
    8 years ago
  • kidane
    What is the vba code for sending an email in access 2007?
    7 years ago

Post a comment