Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 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 Type Library. (Select the most current version of
   the library, or the version you're intending to
   target.)

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

12.8.4 See Also

If you want to "fake" sending
email, using Access only, see Recipe 10.4 in Chapter 10.