PROGRAMMING OUTLOOK 463

to the cell, and if you read back the value of the same cell, it will be a number and not the actual expression you supplied.

Dim expression As String = “1/cos(0.335)*cos(12.45) “ wSheet.Cells(1, 1).Value = “=” & expression wSheet.Calculate

result = wSheet.Cells(1, 1).Value

MsgBox(“The value of the expression “ & expression & vbCrLf & “ is “ & result)

Note Using Excel to evaluate simple expressions may seem like overkill, but if you consider that Visual Basic doesn’t provide the tools for evaluating expressions at runtime, automating Excel is not such a bad idea. This is especially true if you want to evaluate complicated expressions and calculate the statistics of large data sets.

Programming Outlook

Incorporating e-mail capabilities into your applications is a common feature in today’s applications. To make your applications e-mail–aware, you can program Outlook’s objects. In this section, you’ll learn how to mail-enable your Visual Basic applications by manipulating the object model of Outlook. Outlook isn’t a simple mail client. It maintains a list of contacts organized in folders; the contacts may contain a lot of information (from physical addresses to anniversary dates), even information about meetings. You will also learn how to write applications that automatically process, and even reply to, messages. Many corporations use Outlook to automate common tasks like appointment scheduling and routing e-mail. Because of the variety of tasks that can be performed from within Outlook’s environment, you should learn the basics of programming its objects.

To contact Outlook and program the objects it exposes, you must first create a variable that represents the application itself, such as the OLApp variable:

Dim OLApp As New Outlook.Application

Unlike Word and Excel, Outlook doesn’t expose a single object like a Document or Worksheet that gives you access to the information it can handle. Outlook contains several objects including mail messages, contacts, and tasks. The most likely candidate to use as the basic unit of information in Outlook is a folder. Depending on the operation you want to perform with Outlook, you must first select the appropriate folder in the Shortcuts bar. For example, to view the incoming e-mail messages, you must select the Inbox folder; to add a contact, you must first select the Contacts folder. You can’t expect to find information about your contacts in the Inbox folder or the unread messages in the Calendar folder. Since every operation in Outlook is initiated with the selection of the proper folder, the various folders of the application are the top-level objects.

To access the folder objects, you must create a MAPI message store. A MAPI message store is a data source that provides all types of information that can be stored by Outlook. If you’ve used Outlook before, you know that it’s essentially a front end for a database that can store many different types of information. To access this information, you must create a Namespace object variable with the following statements:

Dim OLApp As New Outlook.Application()

Dim OLNameSpace As Outlook.Namespace

OLNameSpace = OLApp.GetNamespace(“MAPI”)

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

464 Chapter 10 AUTOMATING MICROSOFT OFFICE APPLICATIONS

The first statement that declares the OLApp variable should appear outside any procedure, so it can be used by all the procedures in your project. Through the OLNameSpace variable, you can access the various folders and other objects of Outlook. The method for accessing a folder is the GetDefaultFolder method, which accepts the name of the folder as argument and returns an object variable. The object variable returned by GetDefaultFolder method provides properties and methods that give your application access to the items stored in the folder.

The various folders maintained by Outlook can be accessed with the following constants (their names are self-explanatory, and they’re all members of the OLDefaultFolders enumeration):

olFolderCalendar	olFolderInbox	olFolderOutbox
olFolderContacts	olFolderJournal	olFolderSentMail
olFolderDeletedItems	olFolderNotes	olFolderTask
olFolderDrafts

To retrieve all the items in the Contacts folder, use the following statement:

Set allContacts = OLNameSpace.GetDefaultFolder(olFolderContacts).Items

The Items property returns a collection that contains all the items in the specified folder. The allContacts variable must be declared as:

Dim allContacts As Outlook.MAPIFolder

Each folder contains different types of information. The Contacts folder is made up of ContactItem objects, the Inbox and Outbox folders contain MailItem objects, and the Calendar folder contains a collection of AppointmentItem objects. Each one of these objects provides numerous properties, which are the attributes of the item it represents. For example, a ContactItem object provides properties for setting just about any attribute of a contact (name, address, e-mail, and so on).

To see the properties of the ContactItem object, open the Object Browser, expand the Interop

.Outlook item and locate the entry ContactItem as shown in Figure 10.6. The properties of the selected object will appear in the right pane, and the specific object provides a large number of properties. The properties you’ll use most often in your applications are LastName, FirstName, Email1Address, Title, and the properties that begin with HomeAddress and BusinessAddress. These are the fields you can set in the Contact dialog box when you add or edit a contact with Outlook. If you need additional fields, you can create your own custom properties. (These are also accessed by name, but I’m not going to discuss them here. You should see Outlook’s Help files for more information on adding custom properties.)

A property that’s common to all items is the EntryID property, which is a string value that uniquely identifies each item. EntryID values are similar to IDs you assign to the various records in a database (they identify the record, but they have no other apparent meaning). Of course, you can’t have the user select a contact or message based on its EntryID—it makes much more sense to present a list of names or companies to select from—but you can use them to bookmark items. You’ll see how the EntryID property is used in the examples of the following sections. Basically, we use a meaningful field to display information (like an e-mail address or sender’s name), and we keep track of the current contact or message by its EntryID property. The Namespace object exposes the GetFolderByID and GetItemByID methods; you will see shortly how these two methods are used along with EntryID.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PROGRAMMING OUTLOOK 465

Figure 10.6

The properties of the ContactItem object

Retrieving Information

Outlook stores different types of information in different folders. Outlook’s folders do not correspond to physical folders on the disk; they’re just the basic organizational units of Outlook. Contact information is stored in the Contacts folder, incoming messages are stored in the Inbox folder, and so on. Most users, however, customize Outlook’s folder structure by adding subfolders to the default folders. To organize your contacts, for instance, you can create the Business and Personal subfolders under the Contacts folder. Likewise, you can create Business, Personal, and Junk folders under the Inbox folder.

In the following sections, you’ll learn how to extract contacts and messages from the corresponding folders. The process of extracting information stored in Outlook’s folders is straightforward: we retrieve the contents of the appropriate folder, and then we extract the information we want by calling their properties. If the item is a message, you can retrieve its subject with the Subject property. If the item is a contact, you can retrieve the company of the contact with the CompanyName name property. The properties supported by each item are listed automatically in the code editor when needed, so I won’t repeat them here.

The examples of the following sections deal with the Inbox and Contacts folders. Outlook supports other folders as well, which are not discussed in this chapter.

VB.NET at Work: The Contacts Project

The first example of programming Outlook’s objects is the Contacts application, whose form is shown in Figure 10.7. The Contacts application assumes that all contact items are stored in the Contacts folder. If you’ve organized your contacts differently—perhaps in subfolders under the Contacts folder—copy a few contacts temporarily to the Contacts folder so that you

can test the application. Later, in the section “Recursive Scanning of the Contacts Folder,” you’ll see how you can scan the entire Contacts folder recursively, including its subfolders.

The Contact project’s main form contains two lists. The first list is populated with company names, which are read from the contact items. This list doesn’t contain any duplicate entries, even though a typical Contacts folder contains multiple contacts from the same company. To view the

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

466 Chapter 10 AUTOMATING MICROSOFT OFFICE APPLICATIONS

Figure 10.7

Demonstrating how to retrieve contact items from Outlook’s Contacts folder

contacts in a company, double-click the company’s name and the corresponding contacts will appear in the second ListBox control Then, each time you click a contact name, more information about the selected contact will be displayed in the lower half of the form.

First, you must declare a variable that represents the Outlook application, as well as a Namespace variable, with the following statements. All other objects can be accessed through these two variables.

Dim OutlookApp As New Outlook.Application()

Dim OLNameSpace As Outlook.Namespace

In addition, we need one more variable to store the contacts. This variable is also declared on the form, with the following statement:

Dim allContacts As Outlook.MAPIFolder

When the Show Company Names button is clicked, the program creates a collection with all the items in the Contacts folder, the allContacts collection, and then sorts it according to the company name. The list doesn’t contains duplicate names (we use the Contains method of the Items collection to find out whether a company name exists in the list before adding it). The code of the Show Company Names button is detailed in Listing 10.13.

Listing 10.13: Populating the Companies List

Private Sub Button1_Click(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles Button1.Click OLNameSpace = OutlookAPP.GetNamespace(“MAPI”)

allContacts = OLNameSpace.GetDefaultFolder _ (Outlook.OLDefaultFolders.olFolderContacts)

allContacts.Items.Sort(“CompanyName”) Dim contact As Outlook.ContactItem Dim cnt As Integer ContactKeys.Clear() ListBox2.Items.Clear()

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PROGRAMMING OUTLOOK 467

For cnt = 1 To allContacts.Items.Count contact = allContacts.Items.Item(cnt) If contact.CompanyName <> “” Then

If Not ListBox1.Items.Contains(contact.CompanyName) Then ListBox1.Items.Add(contact.CompanyName)

End If End If

Next End Sub

The code that retrieves the contacts for the company selected on the left must be placed in the ListBox control’s SelectedIndexChanged event. This event handler (Listing 10.14) applies the Restrict method to the allContacts collection, which selects only the items meeting the specified criteria. In our case, the criterion is that the contact’s CompanyName field is the same as the selected item on the list. For more information on specifying selection criteria, see the section “Filtering Messages,” later in this chapter.

Listing 10.14: Selecting Contacts from a Company

Private Sub ListBox1_SelectedIndexChanged(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _

Handles ListofCompanies.SelectedIndexChanged Dim contacts As Outlook._Items

contacts = allContacts.Items.Restrict(“[CompanyName]=’” & _ ListofCompanies.Text & “‘“)

Dim contact As Outlook.ContactItem Dim cnt As Integer ListofContacts.Items.Clear() ContactKeys.Clear()

For cnt = 1 To contacts.Count contact = contacts.Item(cnt)

If contact.Email1Address <> “” Then

If (Not ListofContacts.Items.Contains(contact.Email1Address)) Then ListofContacts.Items.Add(contact.Email1Address) ContactKeys.Add(contact.EntryID)

End If End If

Next End Sub

This subroutine makes use of the Restrict method, which accepts a filter expression as argument. The filter is applied to the items of a specific folder and selects the items that meet the criteria. The items are then returned as a collection—in our example, a collection of ContactItem items. The items in the folder are not affected; if you apply another filter, you’ll get back the items that create a new collection. It simply hides the items that don’t meet the specified criteria.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

468 Chapter 10 AUTOMATING MICROSOFT OFFICE APPLICATIONS

To display additional information about a contact, the code shown in Listing 10.15 needs to be executed from within the second ListBox control’s Click event. This code retrieves the selected contact by its ID and displays selected fields in the TextBoxes at the bottom of the form.

Listing 10.15: Displaying Contact Information

Private Sub ListBox2_SelectedIndexChanged(ByVal sender As Object, _

ByVal e As System.EventArgs) Handles ListBox2.SelectedIndexChanged Dim contact As Outlook.ContactItem

contact = OLNameSpace.GetItemFromID (ContactKeys(ListBox2.SelectedIndex)) txtFullName.Text = contact.FullName

txtTel.Text = contact.BusinessTelephoneNumber txtFAX.Text = contact.BusinessFaxNumber

End Sub

The Contacts project has a serious drawback: it assumes that all the contacts are in the Contacts folder. If they’re organized in folders under the Contacts folder, then only the contacts in the toplevel folder will be processed. Later in this chapter, you will see how to scan the Contacts folder, including its subfolders.

VB.NET at Work: The Messages Project

The Messages project demonstrates some techniques for retrieving mail items. Messages are stored in the Inbox and Outbox folders, as well as any custom folders created under these by the user. The Messages example retrieves the messages from the Inbox folder only. If you don’t have any messages in this folder, temporarily move some incoming messages from your custom folders to the Inbox folder to test the application. Later in the chapter, you’ll see how to retrieve all the messages under Inbox, including its subfolders nested to any depth.

The Messages application (shown in Figure 10.8) lets you select messages based on their sender or the date they were sent. The user can specify the criteria with the controls on the top-right section of the form and then click the Show Selected Messages button to display the messages that meet the criteria in the Selected Messages ListBox. The program displays only each message’s sender and subject on the ListBox control to the right. Then, when the user clicks a message on this list, more information is displayed in the controls in the lower half of the form (including the message’s body). If the message contains attachments, the names of the attached files are displayed in a message box. Run the project and experiment with it.

There are two issues you should be aware of. First, the Messages application can see only the messages in the Inbox folder. If you’ve organized your messages into subfolders under the Inbox folder, you must temporarily move a few messages to the Inbox folder. The second issue is that the sender names are read from the Contacts folder. If the names in the Contacts folder don’t match the names that appear in the messages, then you won’t see the messages sent by the selected contact.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PROGRAMMING OUTLOOK 469

Figure 10.8

Demonstrating how to read Outlook’s incoming messages from within VB applications

The Application’s Code

In the form’s Load event, we create two object variables: OLApp references the Outlook application and OLObjects references Outlook’s folders. These variables are declared on the Form level with the following statements:

Dim OLApp As Application

Dim OLObjects As Outlook.NameSpace

Then the code sets up the InBox variable, where the messages in the Inbox folder will be stored. This variable is also declared outside any procedure, with the following statement:

Dim InBox As outlook.MAPIFolder

The code scans all the messages in the Inbox folder and adds the sender’s name to the ComboBox control on the right. The Sorted property of the ComboBox control is set to True, and the code doesn’t add duplicate entries (it uses the Contains method of the Items collection to find out whether the contact exists in the list or not). The bulk of the statements of Listing 10.16 are executed from within the form’s Load event handler.

Listing 10.16: Initializing the Messages Project

Dim OutlookApp As New outlook.Application()

Dim InBox As outlook.MAPIFolder

Dim OLObjects As Outlook.Namespace

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) _ Handles MyBase.Load

OLObjects = OutlookApp.GetNamespace(“MAPI”)

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

470 Chapter 10 AUTOMATING MICROSOFT OFFICE APPLICATIONS

InBox = OLObjects.GetDefaultFolder(Outlook.OLDefaultFolders.olFolderInbox) Dim mssg As Outlook.MailItem

Dim imssg As Integer

For imssg = 1 To InBox.Items.Count mssg = InBox.Items.Item(imssg)

If Not mssg.SenderName Is Nothing Then

If Not ComboBox1.Items.Contains(mssg.SenderName) Then ComboBox1.Items.Add(mssg.SenderName)

End If End If

Next

ComboBox1.Sorted = True ComboBox1.SelectedIndex = 0

End Sub

Filtering Messages

The user can select a name and/or a date range to limit the selected messages. If the check boxes “From this sender” and “Between these dates” are cleared, then clicking the Show Selected Messages button will display all the messages in the Inbox folder on the ListView control on the left. We haven’t discussed yet the ListView control, but you can think of it as ListBox control with multiple columns. Each item has a Text property, which is the string of the first column, and a SubItems property, which is a collection. If you check either or both check boxes, then the program will display only the messages that meet the specified criteria.

To filter the messages, use the Restrict method of the Items collection. This method accepts an expression that filters some messages and returns only the messages you’re interested in. The syntax of the Restrict method is Restrict(filterstring), where filterstring is an expression that specifies the desired criteria. The Restrict method returns a collection of items: the items of the original collection that meet the criteria. The original collection doesn’t change, and you can retrieve a different collection from it by applying another filter expression.

The filterstring argument is a string expression that combines field names, logical operators, and values. To retrieve the messages sent by “Site Builder Network”, use the following string:

“[SenderName] = “ ‘Site Builder Network’ “

To retrieve all messages sent in October 2000, use the following string:

“[SentOn] => “ ‘10/01/00’ “ And [SentOn] <= “ ‘10/31/00’ “

(The single quotes are used to embed quotes within quotes. You can also use two consecutive double quotes in the string to indicate an embedded double quote.)

You can combine as many fields as needed with the usual comparison and logical operators. The field names for each item type can be found in the Object Browser. Select the desired item (e.g., MailItem, ContactItem) and look up its properties in the Members pane.

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com

PROGRAMMING OUTLOOK 471

In the Messages project, you use the values of various controls on the form to build the filter string as follows. First, you validate the dates, then you build the filter string with the following statements:

If chkSender.Checked Then

filter = “[SenderName]=’” & ComboBox1.Text & “‘“ End If

If chkDates.Checked Then

If filter <> “” Then filter = filter & “ And “

filter = filter & “[SentOn] > ‘“ & DateTimePicker1.Value.ToShortDateString & _ “‘ And [SentOn] <= ‘“ & DateTimePicker2.Value.ToShortDateString & “‘“

End If

If filter <> “” Then

selMessages = InBox.Items.Restrict(filter) Else

selMessages = InBox.Items End If

Notice the placement of the single quotes in the expressions. If the selected string on the ComboBox1 control is “Sybex”, then the statement:

“[SenderName] = ‘“ & ContactName & “‘“

will produce the following string:

[SenderName] = ‘Sybex’

The filter variable is built slowly, according to the values entered by the user on the Form. If the user specifies a name, the SenderName property is set to the appropriate value. If the user specifies dates, the SentOn property is set accordingly.

The filter variable is then passed to the Restrict method of the InBox.Items collection. Then the program loops through the selected messages, which are the items of the selMessages collection. At each iteration, another message’s sender and subject are displayed on a ListView control. I have used the ListView control to store the subjects of the messages and their sender, because I could also store the ID of the messages in a hidden column (a column with a width of 0 pixels). Here are the statements that display the filtered messages.

Dim mssg As Outlook.MailItem Dim imssg As Integer

Dim itm As ListViewItem

For imssg = 1 To selMessages.Count mssg = selMessages.Item(imssg) itm = New ListViewItem() itm.Text = mssg.SenderName itm.SubItems.Add(mssg.Subject) itm.SubItems.Add(mssg.EntryID) ListView1.Items.Add(itm)

itm = Nothing Next

Copyright ©2002 SYBEX, Inc., Alameda, CA

www.sybex.com