Sending Email via Outlook

JUNG/

Suppose you want to be able to send e-mail messages to people listed in a table named Customers in an Access database. You are absolutely certain that you can send and receive e-mail with Microsoft Outlook. (Important: None of the code described here will work with Outlook Express or a Web browser.) For this example, say you want to create a standard form letter-type e-mail message to whatever customer a user chooses from a drop-down menu, 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 won't work.

Figure 14-4:

Controls on a sample e-mail form.

Figure 14-4:

Controls on a sample e-mail form.

Access Vba Mail

MsgAddress MsgSubject

MsgBody

MsgAddress MsgSubject

MsgBody

When the user clicks the Send button in Figure 14-4, you want VBA to create and send an e-mail message. Because you'll be calling 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 object library.

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

Listing 14-1: Procedure Attached to Send Button

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] 'Send the finished message. .Send End With

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

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 sends 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, I create a new, blank e-mail message named OlkMsg. (The name OlkMsg is one I 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, a CC, or a 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 sends the message, using the syntax OlkMsg.Send:

'Send the finished message. .Send 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=..., OlkMsgBody=, and OlkMsg.Send. But here, you can omit the OlkMsg part of the name because the With OlkMsg statement means All properties from here to End With refer to the new e-mail message named OlkMsg.

After the message is sent, the job is done. Although not absolutely necessary, I did a little housekeeping at the end of this procedure; by setting the object variable names, I created the 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 actually run the procedure, you'll 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). Then the real torture sets in as it makes you wait a few seconds and then answer Yes before each sent message. If you were trying to send out hundreds or thousands of messages, you'd be clicking Yes over and over again for a long time.

Figure 14-5:

Outlook security prevents mass mailings.

Figure 14-5:

Outlook security prevents mass mailings.

Like all object libraries, Outlook's library is quite large and not something that I 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 Outlook's 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).

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.

Outlook

Figure 14-6:

Outlook's Application object selected in the Object Browser.

Outlook

Figure 14-6:

Outlook's Application object selected in the Object Browser.

Application CreateObject

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

0 0

Post a comment