9.8 Create an Expanding Dialog

12.8 Add a Contact and Send Email Through Outlook

12.8.1 Problem

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?

12.8.2 Solution

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.

 

Because of the serious threat of email viruses, the Outlook team has "locked down" the programmability features of Microsoft Outlook. The level of the virus support may be different depending on which version of Outlook you have installed and what service release you've added. In testing this demonstration, you may see an alert warning you that someone is attempting to modify your address book. You can safely dismiss that dialog for this demonstration, but you should never take it lightly in real 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.

Figure 12-17. frmContacts allows you to work with Outlook contacts and send email

Follow these steps to create a form like frmContacts:

1. Import the module basAutomateOutlook from 12-08.MDB.

2. Open basAutomateOutlook and use the Tools References... menu item to add a reference to the Microsoft Outlook 10.0 Type Library. (If you're using Outlook 2000, choose the Microsoft Outlook 9.0 Type Library instead.)

3. Import tblContacts from 12-08.MDB.

4. 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.

5. 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

6. 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

    

   

   Note that in the Email text box's Change event, you must use the Text property (not the default Value property) if you want to refer to the current value in the control. This is a confusing area in Access forms: while in the middle of editing a control on an Access form, the Text property contains the actual, current text. The Value property (which is the default property, so you needn't explicitly specify it) contains the original text in the control, before you began editing it. In this example, the Change event procedures must refer to the Text property of the current control (the one being changed) but the Value property of the other control.

    

7. 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

8. 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.

12.8.3 Discussion

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.

 

Although this section gives you a good start working with Outlook programmatically, you'll find that Outlook has an extremely rich and powerful object model, allowing you to work with contacts, mail items, and schedule items, as well as the entire Outlook user interface.

 

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.

Figure 12-18. This familiar dialog appears when you log into Outlook

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

 

You might want to investigate the Namespace object's Logon method in Outlook's online help it has several options that allow you to pass authentication information within the method call. You can control whether to show the dialog, as well.

 

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.

 

You may wonder why releasing the cti, ola, and nsp variables doesn't close the contact editor and shut down Outlook. That would happen only if you never displayed the contact within an editor for the user to see. Once you do that, though, Outlook is effectively "owned" by the user, and unless you explicitly call the Quit method of the Outlook Application object, it's now up the user to close the contact editor. When that happens, Outlook will shut down because no other references to it exist. Of course, if Outlook had been running before you ran the code, it would continue to run afterwards, since the variables used within the procedures here are simply additional references to the running copy of Outlook.

 

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.

 

If you wanted to actually send a message programmatically, you could supply the Subject and Body fields (and any others you'd like to supply) in your code, and then call the Send method of the MailItem object. For this example, we've simply created the message and dumped you into the email editor in Outlook.

 

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.