Techniques Used in the Account Tracking Application

The Account Tracking application demonstrates many techniques that you can use in your Outlook applications. It shows how to connect databases to Outlook, for example, how to automate other applications from your Outlook application, and how to use the Outlook object library to modify the controls on your form at run time. Let's review some of the interesting techniques used in the Account Tracking application.

Setting Global Variables

The Account Tracking application uses global variables in VBScript to keep objects and variables in memory throughout the lifetime of the application. Global variables are also used to set the preferences for the application ”for example, whether to use a database for the product sales information. The following code shows the global variables and global initializations:

 '********************************************** 'Global Declarations '********************************************** Dim oRestrictedContactItems Dim oRestrictedTaskItems Dim oExcel Dim oSheet Dim ComposeMode Dim bWebExists Dim oDefaultPage Dim oWebBrowser Dim oCurrentFolder Dim olstAssignTaskName Dim oNameSpace Dim oDatabase Dim oDatabaseEngine Dim bUseDatabase    'Used to tell the application to use an                     'external DB Dim txtAccountName Dim txtOriginalStreet, txtOriginalCity, txtOriginalState Dim txtOriginalPostalCode, txtOriginalCountry Dim oExcelChart bUseDatabase = 0    'Tells the application whether to use an Access                     'database                     'Set this to 1 to use a database, otherwise                     'set it to 0. ComposeMode = True  'Used to determine whether the application is in                     'compose or read mode bWebExists = False  'Used to determine whether the WebBrowser control                     'was successfully created 

Determining the Mode: The Item_Read Event

The Item_Read event is used to determine whether the user is creating a new account or reading an existing account from the folder. Determining the mode ”compose mode or read mode ”is important because during compose mode, the code for reading the database and updating the contact address should not be run. After the mode is determined, the code sets a global variable, ComposeMode , that is used throughout the application. Because the VBScript in an Outlook form runs whether an item is being composed or read, you can use the Read event and global variable approach to identify the application mode and have your application behave appropriately. The following code shows the Item_Read subroutine, which runs only when an item is being read:

 '************************************************ 'Sub Item_Read ' 'This is the standard Read event for an Outlook form. 'It checks to see whether the user is in read or compose mode 'on the form. '************************************************ Sub Item_Read     'Check to see if the application is in compose mode     ComposeMode = False End Sub 

Initializing the Application: The Item_Open Event

The Item_Open event in the Account Tracking form is used to perform some application initialization, in this order:

1. It initializes the global variables used most commonly throughout the application, including the Page object for the default page of the form and the NameSpace object in Outlook.

2. It checks whether the WebBrowser control on the Company Website tab has been successfully created. If so, Item_Open enables a number of controls on the form by using the Controls collection.

3. It checks whether the user has filled in the internal account team. If so, it adds these users to the list box on the Account Tasks tab to make it easy for users to assign tasks to the account team members .

4. It stores the original information for the address of the company. This information is used later in the Item_Close event.

5. It initializes and opens the database using helper functions in the script.

The entire Item_Open subroutine is shown here:

 '************************************************ 'Sub Item_Open ' 'This is the standard Outlook Open event. This subroutine 'sets some objects for use later in the app. Checks whether 'the WebBrowser control was successfully created and also checks 'to see whether there are names for the account team in the form. 'If the form is in compose mode, the subroutine selects the name of 'the account at the top of the form to draw the user's attention to 'that field. '************************************************ Sub Item_Open     'Get the default page of the application to use later     Set oDefaultPage = GetInspector.ModifiedFormPages( _         "Account Tracking")     Set oNameSpace = Application.GetNameSpace("MAPI")     'Initialize the WebBrowser control     Set oWebBrowser = GetInspector.ModifiedFormPages( _         "Company Website").Controls("oWebBrowser")     'Check to see if the browser was successfully created; if so,     'enable the Go button for the company Web site and the     'NetMeeting option     If Err.Number = 0 Then         bWebExists = True         oDefaultPage.Controls("cmdGo").enabled = True         oDefaultPage.Controls("cmdNetMeetingContact").Visible = True         oDefaultPage.Controls("lblNetMeetingContact").Visible = True         oDefaultPage.Controls("cmdNetMeetingContact").Enabled = True         oDefaultPage.Controls("lblNetMeetingContact").Enabled = True     End If     'Get Current Folder     Set oCurrentFolder = Application.ActiveExplorer.CurrentFolder     Call cmdRefreshContactsList_Click     Call cmdRefreshTasks_Click     'Check to see if any users are assigned to the account team and     'add them to assign task list     Set olstAssignTaskName = oDefaultPage.Controls( _         "lstAssignTaskName")     CheckFor "txtAccountSalesRep"     CheckFor "txtAccountSE"     CheckFor "txtAccountConsultant"     CheckFor "txtAccountSupportEngineer"     CheckFor "txtAccountExecutive"          If not(ComposeMode) Then         txtOriginalStreet = _             Item.UserProperties.Find("Account Street")         txtOriginalCity = _             Item.UserProperties.Find("Account City")         txtOriginalState = _             Item.UserProperties.Find("Account State")         txtOriginalPostalCode = _             Item.UserProperties.Find("Account Postal Code")         txtOriginalCountry = _             Item.UserProperties.Find("Account Country")         oDefaultPage.Controls("lblDistrict").visible = True         Set oDistrict = oDefaultPage.Controls("lstDistrict")         oDistrict.visible = True     End If          If not(ComposeMode) and bUseDatabase Then         txtAccountName = item.Subject         'Initialize DB         InitializeDatabase "c:\sales.mdb"         GetDatabaseInfo "[1998 Actual]", "cur1998ActualProd1", _             "cur1998ActualProd2","cur1998ActualProd3"         GetDatabaseInfo "[1999 Actual]", "cur1999ActualProd1", _             "cur1999ActualProd2","cur1999ActualProd3"         GetDatabaseInfo "[1998 Quota]", "cur1998QuotaProd1", _             "cur1998QuotaProd2","cur1998QuotaProd3"     End If          If ComposeMode Then         oDefaultPage.txtName.SetFocus         oDefaultPage.txtName.SelStart = 0         oDefaultPage.txtName.SelLength = 11     End If End Sub 

Connecting to the Sales Database: The GetDatabaseInfo Subroutine

If you have enabled a database for the sales information, the GetDatabaseInfo subroutine will be called to retrieve the sales information from the database and place this information into Outlook fields. This subroutine uses ADO to query the database and retrieve the sales information associated with accounts previously entered in the database. Once this information is placed in the form, Outlook formula fields determine whether the current sales of the product are exceeding the quota for the product. Outlook then displays how much the account team needs to sell to reach its quota or how much over quota the account team is. The following code shows the GetDatabaseInfo subroutine:

 '************************************************ 'Sub GetDatabaseInfo ' 'This subroutine retrieves the Product revenue information 'from the database using the passed in tablename as well as 'field names and the current accountname from the open item. 'You can customize this subroutine to meet your specific needs '************************************************ Sub GetDatabaseInfo(TableName, FieldName1, FieldName2, FieldName3)     On Error Resume Next     strSQL = "Select Product1, Product2, Product3 FROM " & TableName & _             " WHERE AccountName = '" & txtAccountName & "';"     Set oRS = item.application.createobject("ADODB.Recordset")     oRS.Open strSQL, oConnection     If Err.Number <> 0 Then         MsgBox Err.Description &  Err.Number & Chr(13) & _             "OpenRecordset failed"         Exit Sub     End If     oRS.MoveFirst          Item.UserProperties.Find(FieldName1).Value = oRS.Fields(0)     Item.UserProperties.Find(FieldName2).Value = oRS.Fields(1)     Item.UserProperties.Find(FieldName3).Value = oRS.Fields(2) End Sub 

Displaying an Address Book Using CDO: The FindAddress Subroutine

Because Outlook does not natively support displaying an address book in its object library, you must extend the application by using the Collaboration Data Objects (CDO) library, which displays address books and returns the values selected by the user. To use CDO in the Account Tracking application, the VBScript code in the form has to create a CDO object by using the CreateObject method of the Outlook Application object. When the object is created, a subroutine starts a session using the CDO methods and displays an address book using the caption and button text, which are passed in as parameters. Then the subroutine stores the results selected by the user in a specific Outlook field, which is also passed in as a parameter. Finally, the subroutine logs out of the CDO session and destroys the CDO object. Figure 8-8 shows how the address book looks after you click one of the address book buttons on the Account Team tab.

Figure 8-8: Displaying the address book in an Outlook form by using CDO

The following code shows how the address book is displayed using CDO:

 '************************************************ 'Sub FindAddress ' 'This subroutine takes the Outlook field that stores 'the returned value and the caption for the dialog box as 'well as the button text for the dialog box, and then it 'displays the AddressBook dialog box by using CDO ' '************************************************ Sub FindAddress(FieldName, Caption, ButtonText)          On Error Resume Next     Set oCDOSession = application.CreateObject("MAPI.Session")     oCDOSession.Logon "", "", False, False, 0     txtCaption = Caption     If Not err Then         Set orecip = oCDOSession.addressbook (Nothing, txtCaption, _             True, True, 1, ButtonText, "", "", 0)     End If     If Not err Then         item.userproperties.find(FieldName).value = orecip(1).Name     End If     oCDOSession.logoff     oCDOSession = Nothing End Sub 

Advanced CDO: Changing an Appointment Color

One topic that people always ask about is programming to more objects than the Outlook Object Model has. For example, the Outlook Object Model does not have a way to query or set calendar color coding. However, you can use CDO (which you will learn about in more detail in Chapter 11), to query and set the calendar color coding. You use the Appointment property set ( 0220060000000000C000000000000046 ); the property for the color is 0x8214 . The following quick sample shows how to query this property using both CDO 1.21 and CDO for Exchange. CDO for Exchange is covered in Chapter 15.

 Dim oSession As New MAPI.Session 'CDO 1.21 Version oSession.Logon Dim oFolder As MAPI.Folder Set oFolder = oSession.GetDefaultFolder(0) Dim oItem As MAPI.AppointmentItem Dim oMessages As MAPI.Messages      Set oItem = oFolder.Messages.GetFirst MsgBox oItem.Fields.Item("0x8214", "0220060000000000C000000000000046") 'Values here will mimic the Outlook user interface '0 = No color '1 = 1st label (Important) '2 = 2nd label (Business) '3 = 3rd label (Personal) 'etc oSession.Logoff      'CDO for Exchange version Dim oAppt As New CDO.Appointment strURL = "file://./backofficestorage/thomriznt5dom2.extest." & _          "microsoft.com/mbx/thomriz/calendar/appt.eml" oAppt.DataSource.Open strURL      'Propset is 00062002-0000-0000-C000-000000000046 'For CDO 1.21, this is transposed to 0220060000000000C000000000000046 'Property ID is 0x8214 MsgBox oAppt.Fields.Item("http://schemas.microsoft.com/mapi/id/" & _     "{00062002-0000-0000-C000-000000000046}/0x00008214").Value 

Creating Account Contacts: The cmdAddAccountContact Subroutine

After assigning personnel to the account team, the user can add new account contacts for the company. The application has a custom action that creates a reply in the folder by using the custom Account Contact form. Because you are using an action, the command for the action, Create New Account Contact, appears on shortcut menus . For example, if you right-click on an account item in Outlook, Create New Account Contact will be one of the choices. Using an action also makes it easy for Outlook to create a conversation thread for the account contact. Finally, using an action allows the application to attach the original item, in this case the account item, to the contact as a shortcut without any coding. The cmdAddAccountContact subroutine, shown in the following code snippet, executes the custom action by using the Actions collection on the account form. This code is similar to the cmdAddTasks subroutine, but instead of displaying an Account Contact form, it displays an Account Task form for the user to fill in.

 '********************************************** 'Sub cmdAddAccountContact_Click ' 'This subroutine creates a new contact and displays 'the form for the new contact as a modal dialog box '********************************************** Sub cmdAddAccountContact_Click     Item.Save     Set AccountContactForm = item.Actions( _         "Create New Account Contact").Execute     AccountContactForm.Display(True)     Call cmdRefreshContactsList_Click End Sub 

Refreshing the Contact List Box: The cmdRefreshContactsList Subroutine

When the form initially opens, or when a user adds or deletes contacts or tasks in the folder, the ListBox control that contains these items must be refreshed and filled with the most recent information from the folder. To do this, the application calls subroutines that restrict the folder based on the item type and on the account the item belongs to. The application then programmatically fills the list box with the correct information for the account. The list box is shown in Figure 8-9.

Figure 8-9: The Account Contacts list box for the Account Tracking application

The following code shows the cmdRefreshContactsList subroutine at work:

 '************************************************ 'Sub cmdRefreshContactsList_Click ' 'This subroutine refreshes the list box of contacts by 'applying a restriction on the folder ' '************************************************ Sub cmdRefreshContactsList_Click     'Initialize ListBox     Set oListBox = oDefaultPage.Controls("lstContacts")     oListBox.Clear     oListBox.ColumnWidths = "0;172;140;80;120"          'Create search criteria     RestrictString = ""     RestrictString = "[Message Class] = " & _         """IPM.Contact.Account contact"" and [Conversation] = """ & _         item.ConversationTopic & """"     Set oRestrictedContactItems = _         oCurrentFolder.Items.Restrict(RestrictString)     For i = 0 to oRestrictedContactItems.Count - 1         oListBox.AddItem         oListBox.Column(1,i) = oRestrictedContactItems(i+1).FullName         oListBox.Column(2,i) = oRestrictedContactItems(i+1).JobTitle          oListBox.Column(3,i) = _             oRestrictedContactItems(i+1).BusinessTelephoneNumber         oListBox.Column(4,i) = _             oRestrictedContactItems(i+1).Email1Address     Next End Sub 

Performing Default Contact Actions: E-Mail, Letters, and NetMeeting

Because users will want to perform many actions for the account contacts they create, the Account Tracking application provides the most common actions as default controls on the Account Contacts tab. The user can click on the Email Contact link to e-mail a contact. This action uses the CreateItem method on the Application object to create an e-mail message, and then it uses the name of the selected contact to fill in the address information for the e-mail.

If Word is installed, the user can also send a letter to the contact by clicking on the Send Letter To Contact link. This action uses the CommandBars collection on Outlook forms to trigger toolbar actions. Then, by using the FindControl method and the Execute method of the CommandBar object, the application launches the New Letter To Contact command from the Actions menu for a contact. This, in turn , launches the Microsoft Word Letter Wizard, which uses the contact information to automatically populate the address information for the letter.

Finally, the user can start a Microsoft NetMeeting with the contact by clicking on the NetMeeting This Contact link. This action uses the WebBrowser control. If the WebBrowser control is available and the user launches the action, the application will use VBScript in Outlook to automate the WebBrowser control, which will start NetMeeting and connect the user to the Account contact using the NetMeeting client.

The following code shows the subroutines that enable the user to send an e-mail or a letter and to set up a NetMeeting:

 '************************************************ 'Sub cmdEmailContact_Click ' 'This subroutine creates an e-mail message for the selected 'account contact. If no contact is selected, it displays an error. '************************************************ Sub cmdEmailContact_Click     Set oListBox = oDefaultPage.Controls("lstContacts")     If oListBox.ListIndex = -1 Then         MsgBox "No selected account contact. Please select one.", _             48, "Email Account Contact"     Else         Set oItem = oRestrictedContactItems(oListBox.ListIndex + 1)         'Create an e-mail message         Set oNewMessage = Application.CreateItem(0)         oNewMessage.Recipients.Add oItem.Email1Address         oNewMessage.Recipients.ResolveAll         oNewMessage.Display     End If End Sub      '************************************************ 'Sub cmdSendLettertoContact_Click ' 'The following subroutine uses the commandbars 'property to automate the Contact form in Outlook 'to select the Send Letter To A Contact menu 'command. This in turn launches the Word Letter 'Wizard. '************************************************ Sub cmdSendLettertoContact_Click     Set oListBox = oDefaultPage.Controls("lstContacts")     If oListBox.ListIndex = -1 Then         MsgBox "No selected account contact. Please select one.", _             48, "Send letter to Account Contact"     Else         Set oItem = oRestrictedContactItems(oListBox.ListIndex + 1)         oItem.Display         oItem.GetInspector.CommandBars.FindControl(,2498).Execute     End If End Sub      '************************************************ 'Sub cmdNetMeetingContact_Click ' 'This subroutine checks the contact to see whether the 'NetMeeting information is filled in and, if so, it 'automates the WebBrowser control to use the NetMeeting 'callto: syntax to start a NetMeeting '************************************************ Sub cmdNetMeetingContact_Click     Set oListBox = oDefaultPage.Controls("lstContacts")     If oListBox.ListIndex = -1 Then         MsgBox "No selected account contact.  Please select one.", _             48, "NetMeeting Account Contact"     Else         Set oItem = oRestrictedContactItems(oListBox.ListIndex + 1)         If oItem.NetMeetingAlias = "" Then             MsgBox "The NetMeeting information is not filled" & _                  " in for this contact.", 48, _                 "NetMeeting Account Contact"             Exit Sub         End If         If oItem.NetMeetingServer = "" Then             MsgBox "The NetMeeting information is not filled" & _                 " in for this contact.", 48, _                 "NetMeeting Account Contact"             Exit Sub         End If         On Error Resume Next         txtNetMeetingAddress = "callto:" & oItem.NetMeetingServer _             & "/" & oItem.NetMeetingAlias         oWebBrowser.Navigate txtNetMeetingAddress         If err.number <> 0 Then             MsgBox "NetMeeting is either not installed or not" & _                 " configured correctly.", 48, _                 "NetMeeting Account Contact"             Exit Sub         End If     End If End Sub 

Automating Excel: The cmdCreateSalesChart and cmdPrintAccountSummary Subroutines

If a user has Excel installed, the Account Tracking application can automate Excel to create charts, as shown earlier in Figure 8-7. One way to begin creating the charts is to click on the Create Sales Chart control on the Revenue tab of the application. An even easier way to start this process is to use the shortcut menu in the Outlook window. For certain item types, you can right-click on an item and choose Create Account Sales Charts without opening the item. The application uses a custom action, the Item_CustomAction event, which it captures when the user selects the Create Account Sales Charts action. The application calls its own subroutine to handle the action rather than displaying a response form. The subroutine then creates sales charts by using VBScript to automate Excel. (Notice in the Item_CustomAction event procedure that I also try to create an action for printing an Excel account summary. Unfortunately, this action did not work from the shortcut menu.)

 '************************************************* 'Function Item_CustomAction ' 'This is the standard CustomAction event for an Outlook form. 'This event is captured so that the Create Account Sales Chart 'as well as the Print Account Summary actions can appear on the menu. 'However, these actions actually call VBScript functions. This 'is why these actions are canceled after the VBScript functions 'automate Excel to create the reports. Otherwise, a reply form 'would appear to the user. '************************************************* Function Item_CustomAction(ByVal Action, ByVal ResponseItem)    Select Case Action       Case "Create Account Sales Charts"          cmdCreateSalesChart_Click()          'Disable the action so that a response form does not appear          Item_CustomAction = False       Case "Print Account Summary"          cmdPrintAccountSummary_Click()          Item_CustomAction = False    End Select End Function      '************************************************ 'Sub cmdCreateSalesChart_Click ' 'This subroutine responds to the Click event of the 'Create Sales Charts control. It automates Excel 'to create both a worksheet and embedded charts on that worksheet. 'You can modify this subroutine to meet your specific needs. '************************************************ Sub cmdCreateSalesChart_Click     Set oExcel = Item.Application.CreateObject("Excel.Application")     oExcel.Visible = True     oExcel.Workbooks.Add     Set oSheet = oExcel.Workbooks(1).Worksheets("Sheet1")     'Set the title for the worksheet     oSheet.Activate     Set oSheetTitle = oSheet.Range("A1")          oSheetTitle.Value = item.Subject & " Sales Summary"     oSheetTitle.Font.Bold = -1     oSheetTitle.Font.Size = 18     oSheetTitle.Font.Name = "Arial"          oExcel.Application.ActiveCell.Offset(2,0).Select     oExcel.Application.ActiveCell.Value = "Revenue Information"     oExcel.Application.ActiveCell.Font.Bold = -1     oExcel.Application.ActiveCell.Font.Name= "Arial"     oExcel.Application.ActiveCell.Font.Size = 11     oExcel.Application.ActiveCell.Font.Underline = 2     oExcel.Application.ActiveCell.Offset(1,0).Select          oSheet.Range("A6").Value = "Product 1"     oSheet.Range("A7").Value = "Product 2"     oSheet.Range("A8").Value = "Product 3"          oSheet.Range("B5").Value = "1998 Actual"     oSheet.Range("B6").Value = item.userproperties( _         "cur1998ActualProd1")     oSheet.Range("B7").Value = item.userproperties( _         "cur1998ActualProd2")     oSheet.Range("B8").Value = item.userproperties( _         "cur1998ActualProd3")          oSheet.Range("C5").Value = "1998 Quota"     oSheet.Range("C6").Value = item.userproperties( _         "cur1998QuotaProd1")     oSheet.Range("C7").Value = item.userproperties( _         "cur1998QuotaProd2")     oSheet.Range("C8").Value = item.userproperties( _         "cur1998QuotaProd3")          oSheet.Range("D5").Value = "1999 Actual"     oSheet.Range("D6").Value = item.userproperties( _         "cur1999ActualProd1")     oSheet.Range("D7").Value = item.userproperties( _         "cur1999ActualProd2")     oSheet.Range("D8").Value = item.userproperties( _         "cur1999ActualProd3")          'Create charts     Set oChart = oSheet.ChartObjects.Add(250, 20, 200, 200)     oChart.Chart.ChartWizard oSheet.Range( _         "a6:B8"),5,,2,1,,,"Actual Product 1998"     Set oChart = oSheet.ChartObjects.Add(0, 150, 200, 200)     oChart.Chart.ChartWizard oSheet.Range( _         "a6:A8, D6:D8"),5,,2,1,,,"Actual Product 1999"     Set oChart = oSheet.ChartObjects.Add(250, 250, 200, 200)     oChart.Chart.ChartWizard oSheet.Range( _         "a6:A8, C6:C8"),5,,2,1,,,"Quota Product 1998"     Set oChart = oSheet.ChartObjects.Add(500, 20, 200, 200)     oChart.Chart.ChartWizard oSheet.Range( _         "a6:c8"),3,,2,1,,,"Quota vs Actual 1998"     oSheet.ChartObjects(4).Chart.ChartType = 54 End Sub 

When the user clicks on the Print Account Summary control on the Account Tracking tab, an account summary is created in Excel. The Excel Account Summary sheet is shown in Figure 8-10.

Figure 8-10: The Excel Account Summary sheet, which is programmatically created by the Account Tracking application

The code to create the Account Summary is shown here:

 '********************************************** 'Sub cmdPrintAccountSummary_Click ' 'This subroutine calls the helper subroutine to 'print the Account Summary. You can replace the 'helper subroutine without having to replace the controls 'on the form. '********************************************** Sub cmdPrintAccountSummary_Click()     CreateExcelSheet End Sub      '************************************************ 'Sub ExcelPrintProductRevenue ' 'This subroutine is a helper subroutine that prints 'the passed-in product name as well as the current 'sales numbers. You can replace this subroutine 'with your own. '************************************************ Sub ExcelPrintProductRevenue(ByVal txtType, txtProd1, txtProd2, _     txtProd3,curProd1,curProd2,curProd3)     oExcel.Application.ActiveCell.Value = txtType     oExcel.Application.ActiveCell.Font.Italic = -1     oExcel.Application.ActiveCell.Offset(1,1).Value = txtProd1     oExcel.Application.ActiveCell.Offset(1,1).Font.Bold = -1     oExcel.Application.ActiveCell(2,3).Value = curProd1     oExcel.Application.ActiveCell.Offset(2,1).Value = txtProd2     oExcel.Application.ActiveCell.Offset(2,1).Font.Bold = -1     oExcel.Application.ActiveCell(3,3).Value = curProd2     oExcel.Application.ActiveCell.Offset(3,1).Value = txtProd3     oExcel.Application.ActiveCell.Offset(3,1).Font.Bold = -1     oExcel.Application.ActiveCell(4,3).Value = curProd3 End Sub      '************************************************ 'Sub CreateExcelSheet ' 'This subroutine automates Excel to create an Account 'Summary report. You can replace this subroutine 'with your own. '************************************************ Sub CreateExcelSheet     Set oExcel = Item.Application.CreateObject("Excel.Application")     oExcel.Visible = True     oExcel.Workbooks.Add     Set oSheet = oExcel.Workbooks(1).Worksheets("Sheet1")     'Set the title for the worksheet     oSheet.Activate     Set oSheetTitle = oSheet.Range("A1")          oSheetTitle.Value = item.Subject & " Account Summary"     oSheetTitle.Font.Bold = -1     oSheetTitle.Font.Size = 18     oSheetTitle.Font.Name = "Arial"          'Put in the printout date     oSheet.Range("A3").Value = "Printed on: " & Date     oSheet.Range("A3").Font.Bold = -1     oSheet.Range("A3").Font.Name = "Arial"     oSheet.Range("A3").Font.Size = 12     oSheet.Range("A3").Font.Color = RGB(0,0,255)          'Put in the date the item was created     oSheet.Range("A4").Value = "Account created on: " & _         item.CreationTime     oSheet.Range("A4").Font.Bold = -1     oSheet.Range("A4").Font.Name = "Arial"     oSheet.Range("A4").Font.Size = 12     oSheet.Range("A4").Font.Color = RGB(0,0,255)          'Put in the date the item was last modified     oSheet.Range("A5").Value = "Account modified on: " & _         item.LastModificationTime     oSheet.Range("A5").Font.Bold = -1     oSheet.Range("A5").Font.Name = "Arial"     oSheet.Range("A5").Font.Size = 12     oSheet.Range("A5").Font.Color = RGB(0,0,255)     oSheet.Range("A7").Activate          'Retrieve contact information     oExcel.Application.ActiveCell.Offset(1,0).Select     oExcel.Application.ActiveCell.Value = "Account Contacts"     oExcel.Application.ActiveCell.Font.Bold = -1     oExcel.Application.ActiveCell.Font.Name= "Arial"     oExcel.Application.ActiveCell.Font.Size = 11     oExcel.Application.ActiveCell.Font.Underline = 2     oExcel.Application.ActiveCell.Offset(1,0).Select          'Refresh the contact listbox     cmdRefreshContactsList_Click     'Retrieve the data from the listbox     Set oPage = GetInspector.ModifiedFormPages("Account Tracking")     Set oListBox = oPage.lstContacts     If oListBox.ListCount > 0 Then         oExcel.Application.ActiveCell.Value = "Contact Name"         oExcel.Application.ActiveCell.Font.Bold = -1         oExcel.Application.ActiveCell.Offset(0,1).Value = _             "Job Title"         oExcel.Application.ActiveCell.Offset(0,1).Font.Bold = -1         oExcel.Application.ActiveCell.Offset(0,2).Value = _             "Business Phone"         oExcel.Application.ActiveCell.Offset(0,2).Font.Bold = -1         oExcel.Application.ActiveCell.Offset(0,3).Value = _             "Email Address"         oExcel.Application.ActiveCell.Offset(0,3).Font.Bold = -1         oExcel.Application.ActiveCell.Offset(1,0).Activate         For intLB = 0 to oListBox.ListCount -1             oExcel.Application.ActiveCell.Value = _                 oListBox.Column(1,intLB)             oExcel.Application.ActiveCell.Offset(0,1).Value = _                 oListBox.Column(2,intLB)             oExcel.Application.ActiveCell.Offset(0,2).Value = _                 oListBox.Column(3,intLB)             oExcel.Application.ActiveCell.Offset(0,3).Value = _                 oListBox.Column(4,intLB)             oExcel.Application.ActiveCell.Offset(1,0).Activate         Next     Else         oExcel.Application.ActiveCell.Value = _             "No contacts for this account"     End If          'Retrieve revenue information     oExcel.Application.ActiveCell.Offset(2,0).Select     oExcel.Application.ActiveCell.Value = "Revenue Information"     oExcel.Application.ActiveCell.Font.Bold = -1     oExcel.Application.ActiveCell.Font.Name= "Arial"     oExcel.Application.ActiveCell.Font.Size = 11     oExcel.Application.ActiveCell.Font.Underline = 2     oExcel.Application.ActiveCell.Offset(1,0).Select     'Retrieve the user properties for the revenue information     Set ouserprop = item.userproperties          ExcelPrintProductRevenue "1998 Actual","Product1","Product2", _         "Product3",ouserprop("cur1998ActualProd1"), _         ouserprop("cur1998ActualProd2"), _         ouserprop("cur1998ActualProd3")     oExcel.Application.ActiveCell.Offset(5,0).Select     ExcelPrintProductRevenue "1999 Actual","Product1","Product2", _         "Product3",ouserprop("cur1999ActualProd1"), _         ouserprop("cur1999ActualProd2"), _         ouserprop("cur1999ActualProd3")     oExcel.Application.ActiveCell.Offset(5,0).Select     ExcelPrintProductRevenue "1998 Quota","Product1","Product2", _         "Product3",ouserprop("cur1998QuotaProd1"), _         ouserprop("cur1998QuotaProd2"), _         ouserprop("cur1998QuotaProd3")          'Format the output     oSheet.Columns("A:B").EntireColumn.AutoFit     oSheet.Columns("B:B").HorizontalAlignment = -4152     oSheet.Range("A1:F1").Select     oSheet.Range("A1:F1").HorizontalAlignment=7 End Sub 

Unloading the Application: The Item_Close Event

When the user is finished using the application, the Item_Close event for the application is invoked. In the event handler, the application checks to see whether the user has updated any account address information. If so, the application asks the user whether she wants to update all the contacts for that specific account in the folder. If the user answers yes, all the accounts are updated by using the properties of the standard Outlook contact. Figure 8-11 shows the message box that is displayed when the user changes the address in the Account Tracking form.

Figure 8-11: If the user wants to change the default address for each contact, this message box appears.

In the code that follows , notice that the Save method of the account contact is called only once after all the properties are changed. Outlook automatically parses the individual address properties, such as BusinessAddressStreet , BusinessAddressCity , and BusinessAddressPostalCode , to create the overall BusinessAddress property. If the code were to save the item after making a change in each property, Outlook would overwrite the previous changes when it parsed the individual properties to create the BusinessAddress property. Instead, a temporary variable, boolSaveItem , is used to notify the code at the end of the If statements whether the contact items being modified need to be saved. The Item_Close event handler also contains the code to destroy any database objects used in the application so they are not left in memory after the application closes . The following code shows the Item_Close event procedure:

 '************************************************ 'Function Item_Close ' 'This function fires on the standard Outlook close 'event and prompts the user whether to update 'all contacts for the company if the user changed the 'master address for the company. This routine will 'update only the contacts that have the same text in 'the address fields as the original since users can 'change the address fields to reflect different 'locations or addresses for customers. This function 'also cleans up any open database objects that are left. '************************************************ Function Item_Close()    boolSomethingDirty = 0        'False    If not(ComposeMode) Then       'Divided into multiple ifs to pinpoint changed property on       'exit for faster performance when updating       If oDefaultPage.Controls("txtStreet").Value <> _       txtOriginalStreet Then          boolStreetIsDirty = 1          boolSomethingDirty = 1        End If       If oDefaultPage.Controls("txtCity").Value <> _       txtOriginalCity Then          boolCityIsDirty = 1          boolSomethingDirty = 1       End If       If oDefaultPage.Controls("txtState").Value <> _       txtOriginalState Then          boolStateIsDirty = 1          boolSomethingDirty = 1       End If       If oDefaultPage.Controls("txtPostalCode").Value <> _       txtOriginalPostalCode Then          boolPostalCodeIsDirty = 1          boolSomethingDirty = 1       End If       If oDefaultPage.Controls("lstCountry").Value <> _       txtOriginalCountry Then          boolCountryIsDirty=1          boolSomethingDirty = 1       End If       If boolSomethingDirty Then          'Make sure the user wants to update all the          'contact addresses          intResponse = MsgBox("The account address " & _             "information has changed. Outlook can update " & _             "all the contacts for this account with " & _             "the new address information automatically. " & _             "However, if there are any changes to the " & _             "address information in the contact that do " & _             "not match the original address for the " & _             "account, Outlook will skip these messages. Do " & _             "you want Outlook to update your accounts now?", _             292, "Update Account Contacts")          If intResponse = 6 Then     'Yes             For counter = 1 To oRestrictedContactItems.Count                boolSaveItem = 0                Set oItem = _                   oRestrictedContactItems.Item(counter)                If boolStreetIsDirty Then                   If oItem.BusinessAddressStreet = _                   txtOriginalStreet Then                      oItem.BusinessAddressStreet = _                      oDefaultPage.Controls("txtStreet").Value                      boolSaveItem = 1                   End If                End If                If boolCityIsDirty Then                   If oItem.BusinessAddressCity = _                   txtOriginalCity Then                      oItem.BusinessAddressCity = _                      oDefaultPage.Controls("txtCity").Value                      boolSaveItem = 1                   End If                End If                If boolStateIsDirty Then                   If oItem.BusinessAddressState = _                   txtOriginalState Then                      oItem.BusinessAddressState = _                      oDefaultPage.Controls("txtState").Value                      boolSaveItem = 1                   End If                End If                If boolPostalCodeIsDirty Then                   If oItem.BusinessAddressPostalCode = _                   txtOriginalPostalCode Then                      oItem.BusinessAddressPostalCode = _                         oDefaultPage.Controls( _                         "txtPostalCode").Value                      boolSaveItem = 1                   End If                End If                If boolCountryIsDirty Then                   If oItem.BusinessAddressCountry = _                   txtOriginalCountry Then                      oItem.BusinessAddressCountry = _                      oDefaultPage.Controls("lstCountry").Value                      boolSaveItem = 1                   End If                End If                If boolSaveItem Then                   'Make sure address information is only                   'parsed once by Outlook                   oItem.Save                End If             Next          End If       End If    End If    'Close the database If enabled    If ComposeMode=False and bUseDatabase Then       oDatabase.Close       Set oDatabaseEngine = Nothing    End If End Function