Adding Contacts and Sending E-mail in Access 2007 by Using Outlook 2007
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)
Applies to: 2007 Microsoft Office System, Microsoft Office Access 2007, Microsoft Office Outlook 2007
Adapted from Access Cookbook, 2nd Edition by Ken Getz, Paul Litwin, and Andy Baron. Copyright © 2004, O'Reilly Media, Inc. All rights reserved. Used with permission.
Ken Getz, MCW Technologies, LLC
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.
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.
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.
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
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.
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.
All the power of this example comes from the code for basAutomateOutlook; this section works through each of the procedures in that module.
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.
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.
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.
'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.