Programming MicrosoftВ® OutlookВ® and Microsoft Exchange 2003, Third Edition (Pro-Developer)

2017-07-07 02:10:07

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:

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.

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.

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 .

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

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