Programming Microsoft Outlook and Microsoft Exchange, Second Edition (DV-MPS Programming)
The Account Tracking application demonstrates many techniques that you can employ in your Outlook applications. For example, it shows you how to connect databases to Outlook, automate applications from your Outlook application, and 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 first technique used by the Account Tracking application is employing global variables in VBScript to keep objects and variables in memory throughout the lifetime of the application. This technique also uses global variables to set the preferences for the application, such as 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 Compose or Read 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 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, which 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 is successfully created. If it is, 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 is called to retrieve the sales information from the database and place this information into Outlook fields. This subroutine uses DAO 3.5 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 table name as well as 'field names and the current account name from the open item. 'You can customize this subroutine to meet your specific needs. '************************************************ Sub GetDatabaseInfo(TableName, FieldName1, FieldName2, FieldName3) strSQL = "Select Product1, Product2, Product3 FROM " & _ TableName & " WHERE AccountName = '" & txtAccountName & "';" Set oRS = oDatabase.OpenRecordset(strSQL) 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, the application needs to be extended with the CDO library, which will display address books and return 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 6-8 shows how the address book looks when you click one of the address book buttons on the Account Team tab.
Figure 6-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 |
Creating Account Contacts: The cmdAddAccountContact Subroutine
After assigning internal people 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, will appear on context 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 automatically create a conversation thread for the account contact. Finally, using an action allows the application to attach the original item to the contact, in this case the account item, 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 users add or delete 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 6-9.
Figure 6-9. The Account Contacts list box for the Account Tracking application. It is dynamically filled in using the contact items contained in the folder.
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 of this application 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 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 uses the name of the selected contact to fill in the address information for the e-mail.
If Word 2000 is installed, the user can also send a letter to the contact by clicking the Send Letter To Contact link. This action takes advantage of Outlook by using 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 option 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 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 uses VBScript in Outlook to automate the WebBrowser control, which starts NetMeeting and connects 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 there is no selected contact, 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 if 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 the user has Excel 2000 installed, the Account Tracking application can automate Excel to create charts, as was shown earlier in Figure 6-7. One way to start the chart creation process is to click the Create Sales Chart control on the Revenue tab of the application. An even easier way to start this process is to use the context menu in the Outlook window. Depending on the item type, you can right-click on an item and select Create Account Sales Charts without opening the item. The application does this through a custom action. The application captures the Item_CustomAction event when the user selects the Create Account Sales Charts action, and it 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 tried to create an action for printing an Excel account summary. Unfortunately, this action did not work from the context 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 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 6-10.
Figure 6-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 the user has updated information, the application prompts the user about 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 6-11 shows the message box that is displayed when the user changes the address in the Account Tracking form.
Figure 6-11. If the user wants to change the default address for each contact, this message box prompts the user about whether to change the addresses of all associated contacts for the account.
In the code that follows, notice how 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 saved the item after making a change in each property, Outlook would automatically 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 about whether the contact items that are being modified need to be saved or not. The Item_Close event handler also contains the code to destroy any database objects used in the application so that 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 about 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 |