Adding Contacts and Sending E-mail in Access 2007 by Using Outlook 2007

Article
05/05/2014

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

Summary: If you keep a contact list in Microsoft Office Access 2007, you can use Automation to create Microsoft Office Outlook 2007 contacts that are based on that data, and to send Outlook 2007 e-mail from within Access 2007. (7 printed pages)

Office Visual How To

Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007, Microsoft Office Outlook 2007

Ken Getz, MCW Technologies, LLC

June 2009

If you maintain a Microsoft Office Access 2007 database full of contact information, you might want to use that data to add contacts to your Microsoft Office Outlook 2007 address book and to send e-mail messages to a recipient in a particular row. This article shows you how you can add those capabilities to your form by using Automation to control Outlook 2007 from Access 2007 programmatically.

Adding Contacts and Sending E-mail in Access 2007

Watch the Video

Length: 08:05 | Size: 9.15 MB | Type: WMV file

Code It | Explore It

Code It

Download the sample database

Microsoft Office Outlook 2007 provides a rich programming model that makes it easy to create contacts and send e-mail programmatically; doing so requires little more than creating an object in memory, setting some properties, and calling the correct methods. The solution in this article provides a form that demonstrates code that you can use in the process.

Caution
Because of the serious threat that e-mail viruses pose, the Outlook 2007 team has "locked down" the programmability features of Microsoft Office Outlook. When you follow the directions in this article and test the sample forms, you might see an alert warning you that someone is attempting to modify your address book. You can safely ignore those alerts as you test the sample forms, but you should never take those sorts of alerts lightly in your day-to-day use.

Because of the serious threat that e-mail viruses pose, the Outlook 2007 team has "locked down" the programmability features of Microsoft Office Outlook. When you follow the directions in this article and test the sample forms, you might see an alert warning you that someone is attempting to modify your address book. You can safely ignore those alerts as you test the sample forms, but you should never take those sorts of alerts lightly in your day-to-day use.

Load and run frmContacts from AutomatingOutlook.accdb. You can use the form to do the following tasks:

Edit contact information in Access.
Create a new e-mail message to the address that you provide in the contact record by clicking Send Email. You must specify an e-mail address to make the Send Email button active.
Copy contact information in Access to a new contact item in Outlook by clicking Add Contact. You must specify a LastName value to make the Add Contact button active.

Figure 1. Working with contact information in frmContacts

Working with contact information in frmContacts

To create a form similar to frmContacts, follow these steps:

Import the module basAutomateOutlook from AutomatingOutlook.accdb.
Open basAutomateOutlook in the VBA editor, and then click Tools | References on the menu to add a reference to the Microsoft Outlook Type Library. Select the most current version of the library, or the version that you want to target.
Import tblContacts from AutomatingOutlook.accdb.
Import frmContacts from AutomatingOutlook.accdb, or create your own form. If you import the form, you can skip to Step 8. If you create your own form, base it on your tblContacts. You can add fields and modify field names as necessary in tblContacts, but when you are done, make sure that you modify the code that follows to match.

Add the following procedure to the form's module. The code enables and disables the two command buttons based on the Email and LastName values.

Private Sub HandleEnabling( _
 varEmail As Variant, varFirstName As Variant)
    cmdEmail.Enabled = Len(varEmail & "") > 0
    cmdContact.Enabled = Len(varFirstName & "") > 0
End Sub

Add event procedures to call HandleEnabling from the form's Current event and from the Change events attached to the two important text boxes (that is, the Email and LastName text boxes).

Private Sub Email_Change()
    Call HandleEnabling(Me.Email.Text, Me.FirstName)
End Sub

Private Sub FirstName_Change()
    Call HandleEnabling(Me.Email, Me.FirstName.Text)
End Sub

Private Sub Form_Current()
    Call HandleEnabling(Me.Email, Me.FirstName)
End Sub

Note
When you specify the Change event for the Email text box, you must use the Text property instead of the default Value property to refer to the current value in the control. It is worth taking a closer look at this because it can be a confusing area in Access forms. As you edit a control on a form, the Text property contains the actual, current text, and the Value property contains the original text that was in the control before you began to edit it. The Value property is the default property, so there is no need to specify it explicitly. Now in this particular example, the Change event procedure must refer to the Text property of the current control that you are changing, but refer to the Value property of the other control.

When you specify the Change event for the Email text box, you must use the Text property instead of the default Value property to refer to the current value in the control. It is worth taking a closer look at this because it can be a confusing area in Access forms. As you edit a control on a form, the Text property contains the actual, current text, and the Value property contains the original text that was in the control before you began to edit it. The Value property is the default property, so there is no need to specify it explicitly. Now in this particular example, the Change event procedure must refer to the Text property of the current control that you are changing, but refer to the Value property of the other control.

In the Click event procedures for the two command buttons, add code to call the appropriate procedures in basAutomateOutlook.

Private Sub cmdContact_Click()
    Call AddContact( _
     Me.FirstName, Me.LastName, Me.Address, _
     Me.City, Me.State, Me.PostalCode, Me.Email)
End Sub

Private Sub cmdEmail_Click()
    Call SendEmail(Me.Email)
End Sub

Run your form, add some data, and try out the two buttons on the form. Clicking Send Email should open the Outlook e-mail editor. Clicking Add Contact should copy data to the contact editor in Outlook and leave the editor available for you to continue editing.

Notes on the code

All the power of this example comes from the code for basAutomateOutlook; this section works through each of the procedures in that module.

Note
Although this section gives you a good start working with Outlook 2007 programmatically, it is by no means comprehensive. Outlook has an extremely rich and powerful object model that you can use to work with contacts, mail items, and schedule items, as well as the entire Outlook user interface.

The following code is the first block of code in basAutomateOutlook.

Private outlookApp As Outlook.Application
Private outlookNamespace As Outlook.NameSpace

Private Sub InitOutlook()
    ' Initialize a session in Outlook
    Set outlookApp = New Outlook.Application
    
    'Return a reference to the MAPI layer
    Set outlookNamespace = outlookApp.GetNamespace("MAPI")
    
    'Let the user logon to Outlook with the
    'Outlook Profile dialog box
    'and then create a new session
    outlookNamespace.Logon , , True, False
End Sub

Private Sub CleanUp()
    ' Clean up public object references.
    Set outlookNamespace = Nothing
    Set outlookApp = Nothing
End Sub

This code block includes module-level variables that refer to the Outlook Application and Namespace objects. Each example in this article uses these variables, so it makes sense that they are module-level variables, available to all procedures in the module (any code that you write to work with Outlook will likely use the variables as well).

Each procedure in this example calls the InitOutlook procedure, which instantiates a new copy of Outlook if it is not already running, or grabs onto the existing instance if it is already running. (Outlook prevents multiple copies of itself from running concurrently in memory.) After this code runs, you can use the variable outlookApp to refer to the running instance of Outlook.

Set outlookApp = New Outlook.Application

Next, the code creates a new Workspace object. When you work with data in the Outlook data store, you must log on by using the Namespace object. Because you pass in the parameter "MAPI" to the GetNameSpace method, you might think that there are other namespaces that you can use, but that is not the case. Outlook uses only the MAPI namespace, and you always pass "MAPI" to the GetNameSpace method.

Set outlookNamespace = outlookApp.GetNamespace("MAPI")

Finally, the InitOutlook procedure calls the Logon method of the Namespace object, which allows you to log onto Outlook. If Outlook is already running, you will not see a logon dialog box. If it is not running, you will see the standard dialog shown in Figure 2.

Figure 2. Outlook 2007 Choose Profile dialog box

Outlook 2007 Choose Profile dialog box

'Let the user logon to Outlook with the
'Outlook Profile dialog box
'and then create a new session
outlookNamespace.Logon , , True, False

You might want to investigate the Logon method for the Namespace object by reading the Help in Outlook. For example, there are several options that you can use to pass authentication information within the method call, and you can specify whether to show the dialog box.

After the logon code in the sample module, the CleanUp procedure releases the module-level variables. If your code started Outlook (that is, if Outlook was not already running), releasing those variables should allow Outlook to shut down.

Private Sub CleanUp()
    ' Clean up public object references.
    Set outlookNamespace = Nothing
    Set outlookApp = Nothing
End Sub

The following code is for the AddContact method, which creates a new Outlook contact, given the information that you pass to it.

Public Sub AddContact(varFirstName As Variant, _
 varLastName As Variant, _
 varAddress As Variant, varCity As Variant, _
 varState As Variant, _
 varPostalCode As Variant, varEmail As Variant)
    Dim item As Outlook.ContactItem
    
    InitOutlook
    Set item = outlookApp.CreateItem(olContactItem)
    item.FirstName = varFirstName & ""
    item.LastName = varLastName & ""
    item.HomeAddressStreet = varAddress & ""
    item.HomeAddressCity = varCity & ""
    item.HomeAddressState = varState & ""
    item.HomeAddressPostalCode = varPostalCode & ""
    item.Email1Address = varEmail & ""
    item.Display
    
    CleanUp
End Sub

AddContact accepts parameters that contain all the fields that you gathered on your Access form. (Look back at the call to the AddContact method to see that you're passing in all the values from the original form.) It starts by calling the InitOutlook procedure, which initializes Outlook. It then calls the CreateItem method, which creates a new Outlook ContactItem object, and sets properties for the contact by using code similar to the following.

Set item = outlookApp.CreateItem(olContactItem)
item.FirstName = varFirstName & ""
item.LastName = varLastName & ""
item.HomeAddressStreet = varAddress & ""
item.HomeAddressCity = varCity & ""
item.HomeAddressState = varState & ""
item.HomeAddressPostalCode = varPostalCode & ""
item.Email1Address = varEmail & ""

Note that because each of the values you pass in from the form in Access could be Null, the code converts each value to a string by adding an empty string, "". Outlook requires the property values to be strings; passing a Null value to Outlook triggers a runtime exception.

Finally, the procedure calls the Display method of the ContactItem object to display the unsaved item. (If you want to save the item before you display it, call the Save method before you call the Display method.) The Display method isn't synchronous; that is, the code continues to run, releases the ContactItem object from memory, and cleans up the module-level variables that you created earlier.

You might wonder why your release of the variable does not close the contact editor and shut down Outlook. The reason is simple — once you display the contact in the Outlook editor for the user, that user effectively "owns" Outlook. Unless you explicitly call the Quit method of the Outlook Application object, it is the user who decides when to close the contact editor. Then, once the user closes the contact editor, Outlook shuts down because there are no other references to it. Of course, if Outlook was running before you ran the code, the variables that you use in the procedures are simply additional references to the running copy of Outlook. Thus, in that case, Outlook would continue to run, even after the user closed the contact editor.

The following code is from the SendEmail procedure, which works a lot like the AddContact procedure.

Public Sub SendEmail(varTo As Variant)
    Dim mailItem As Outlook.mailItem
    
    InitOutlook
    Set mailItem = outlookApp.CreateItem(olMailItem)
    mailItem.To = varTo & ""
    mailItem.Subject = "Message for Access Contact"
    mailItem.Display
    
    Set mailItem = Nothing
    CleanUp
End Sub

SendEmail receives the e-mail address of the recipient and creates a new e-mail message addressed to that recipient in Outlook. (You could, of course, gather and pass more information for the e-mail message, such as the subject, in this procedure call. The sample merely sends the recipient.) SendEmail sets the To field of the new e-mail message, creates a subject for you, and then displays the new, unsent e-mail message in Outlook. It is up to the user to complete and send the e-mail message.

If you wanted to actually send a message programmatically, you could supply the Subject and Body fields (along with any other fields that you want to include) in your code, and then call the Send method of the MailItem object. The example in this article creates a message and then leaves you in the Outlook e-mail editor.

Of course, there is a lot more to the Outlook object model than you have read in this article. If you want to learn more, a good place to start is to explore the data in the VBA Object Browser (press F2 in a VBA module, select Outlook from the list of libraries in the upper-left corner of the window, and then start exploring). In addition, there are several good books on programming the Outlook object model, and don't forget the Outlook Help and the numerous online sites like MSDN.

Adding Contacts and Sending E-mail in Access 2007 by Using Outlook 2007

Code It

Notes on the code

Explore It

Additional resources