You maintain an Access database full of contact information. You'd like to be able both to add contact information to your Outlook address book and to send email messages easily, using the email address stored in a particular row. How can you add these features to your form without forcing your users to load Outlook and work there?
Outlook provides a rich programming model, and it's easy for you to programmatically create contacts and send email. You'll find that solving these problems requires little more than creating an object in memory, setting some properties, and calling the correct methods. This sample provides a form that demonstrates code you can use.
Load and run frmContacts from 12-08.MDB. This form, shown in Figure 12-17, allows you to edit contact information within Access. You can click on Send Email to create a new email message to the address you've provided in the contact record. Click on Add Contact to copy the contact information to a new contact item within Outlook. Note that the Send Email button isn't available unless you've specified an email address, and the Add Contact button isn't available unless you've specified a LastName value.
Follow these steps to create a form like frmContacts:
Import the module basAutomateOutlook from 12-08.MDB.
Open basAutomateOutlook and use the Tools References... menu item to add a reference to the Microsoft Outlook Type Library. (Select the most current version of the library, or the version you're intending to target.)
Import tblContacts from 12-08.MDB.
Either import frmContacts from 12-08.MDB, or create your own form. (If you import the existing form, you can skip to Step 8.) You can create a new form based on your tblContacts. You can add fields and modify field names as necessary in the underlying table (tblContacts), but you'll need to modify the code that follows to match, if you do.
Add the following procedure to the form's module to handle enabling and disabling the two command buttons:
Private Sub HandleEnabling(varEmail As Variant, _ varFirstName As Variant) Me.cmdEmail.Enabled = Len(varEmail & "") > 0 Me.cmdContact.Enabled = Len(varFirstName & "") > 0 End Sub
Add event procedures to call HandleEnabling from the form's Current event and from the two important text boxes' Change events:
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
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 bring up the Outlook email 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 is buried in basAutomateOutlook's code. This section will work through each of the procedures you'll find in that module.
The first block of code in basAutomateOutlook looks like this:
Private ola As Outlook.Application Private nsp As Outlook.NameSpace Public Sub InitOutlook( ) ' Initialize a session in Outlook. Set ola = New Outlook.Application ' Return a reference to the MAPI layer. Set nsp = ola.GetNamespace("MAPI") ' Let the user log into Outlook with the Outlook ' Profile dialog, then create a new session. nsp.Logon , , True, False End Sub Public Sub CleanUp( ) ' Clean up public object references. Set nsp = Nothing Set ola = Nothing End Sub
This code block includes module-level variables that refer to the Outlook Application and Namespace objects. Each example (and any code you write that works with Outlook) will probably need these variables as well, so it made sense to simply make them module-level, available to all procedures in the module.
Each procedure in this example calls the InitOutlook procedure, which instantiates a new copy of Outlook if it's not already running, or grabs onto the existing instance if it is already running. (Outlook does not allow itself to start up multiple times, so you'll never have multiple copies concurrently running in memory.) After this code runs, you can use the variable ola to refer to the running instance of Outlook:
Set ola = New Outlook.Application
Next, the code creates a new Workspace object. You're required to log in whenever you work with data within the Outlook data store, and the Namespace object provides this capability. Since you pass in the parameter MAPI to the GetNameSpace method, it might appear that there are other namespaces you might want to use, but that's not the case; Outlook uses only the MAPI namespace, and you'll always pass that parameter to the GetNameSpace method:
' Return a reference to the MAPI layer. Set nsp = ola.GetNamespace("MAPI")
Finally, the InitOutlook procedure calls the Logon method of the Namespace object, allowing you to log into Outlook. If Outlook is already running, you won't see a dialog. If not, you'll see the standard dialog shown in Figure 12-18.
The portion of the code that handles logon is:
' Let the user log into Outlook with the Outlook ' Profile dialog, then create a new session. nsp.Logon , , True, False
Next in the sample module, the CleanUp procedure releases the module-level variables. If your code started up Outlook (that is, it wasn't already running), releasing those variables should allow Outlook to shut down.
The AddContact method, shown here, simply creates a new Outlook contact, given the information 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 cti As Outlook.ContactItem InitOutlook Set cti = ola.CreateItem(olContactItem) cti.FirstName = varFirstName & "" cti.LastName = varLastName & "" cti.HomeAddressStreet = varAddress & "" cti.HomeAddressCity = varCity & "" cti.HomeAddressState = varState & "" cti.HomeAddressPostalCode = varPostalCode & "" cti.Email1Address = varEmail & "" cti.Display Set cti = Nothing CleanUp End Sub
This procedure accepts parameters containing all the fields 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 initializing Outlook, calling the InitOutlook procedure you've already seen. It then calls the CreateItem method, creating a new Outlook ContactItem object, and sets properties of the contact:
Set cti = ola.CreateItem(olContactItem) cti.FirstName = varFirstName & "" cti.LastName = varLastName & "" cti.HomeAddressStreet = varAddress & "" cti.HomeAddressCity = varCity & "" cti.HomeAddressState = varState & "" cti.HomeAddressPostalCode = varPostalCode & "" cti.Email1Address = varEmail & ""
Finally, the procedure calls the Display method of the ContactItem object to display the unsaved item. (If you want to save the item before displaying it, call the Save method before calling the Display method.) The Display method isn't synchronous?that is, the code continues running, releases the ContactItem object from memory, and cleans up the module-level variables created earlier.
The SendEmail procedure shown here works much like the AddContact procedure:
Public Sub SendEmail(varTo As Variant) Dim mli As Outlook.MailItem InitOutlook Set mli = ola.CreateItem(olMailItem) mli.To = varTo & "" mli.Subject = "Message for Access Contact" mli.Display Set mli = Nothing CleanUp End Sub
SendEmail receives the email address of the recipient and creates a new email message addressed to that recipient in Outlook. (You could, of course, gather and pass more information for the email message, such as the subject, in this procedure call. The sample merely sends the recipient.) SendEmail sets the To field of the new email message, creates a subject for you, and then displays the new, unsent email message in Outlook. It's up to the user to complete and send the email message.
Of course, there's much more to the Outlook object model than we've been able to show here. Start by exploring the data provided by the VBA Object Browser (press F2 from within a VBA module, select Outlook from the list of libraries in the upper-left corner of the window, and start digging). You can find several good books on programming the Outlook object model, and don't forget to check out the online help.
Make sure to try out various versions of Outlook if you're shipping an application to end users. The Outlook security patch and the various versions of security models are sure to hamper your applications if you intend to work with contacts or send email to contacts in the address book programmatically.
If you want to "fake" sending email, using Access only, see Recipe 10.4 in Chapter 10.