Chapter 24: Generating Reports on Outlook Data

This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.

This article is an excerpt from Microsoft Outlook 2007 Programming: Jumpstart for Power Users and Administrators by Sue Mosher from Elsevier (ISBN 978-1-55558-346-0, copyright Elsevier 2008, all rights reserved). No part of these chapters may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic, electrostatic, mechanical, photocopying, recording, or otherwise—without the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.

If any single area of Microsoft Office Outlook 2007 falls short, it’s printing and reporting. Outlook’s built-in ability to regurgitate its data—either as printed reports or as files in other formats—is very limited. For example, when you use Outlook’s File, Import and Export command, you cannot export user-defined fields, and some useful standard fields don’t export at all! Furthermore, there is no method for printing a legacy custom form or form region in a format that resembles the on-screen form. An individual item always prints in Outlook’s memo style, which produces a simple list of fields. Any custom fields print in alphabetical order.

Because of these major limitations, being able to extract Outlook data into some other format—either a file or a printed report—is an essential skill for Outlook programmers. This chapter discusses how you can export and print from Outlook without programming, using techniques such as Outlook views. You will also see how to push Outlook data into Microsoft Excel or Microsoft Word. These reporting techniques can be adapted not just for printed output, but also to produce files of exported data.

• Highlights of this chapter include discussions of the following:

• Why folder views are the key to simple tabular reports

• How to use a Word mail merge to build a contact report

• How to build tabular reports with Excel

• How to print invoices and other reports that combine data from two different folders

Important
The code marked as Visual Basic in this chapter is written in Visual Basic for Applications (VBA).

Contents

• 24.1 Built-in report techniques

• 24.2 Coding reports with the Outlook object model

• 24.3 Sending output to Microsoft Excel

• 24.4 Sending output to Microsoft Word

• 24.5 Using Word to build an invoice report

• 24.6 Summary

24.1 Built-in report techniques

Users can print individual items or lists from Outlook folders by using the File | Print command. On a custom form’s VBScript code, you can print an item by calling Item.PrintOut.Text here.

Figure 24.1. Printouts of custom forms look little like the form layout

For an individual item, Outlook prints first its standard fields in a preset order (which neither user nor developer can control), then any custom fields in alphabetical order, and finally the contents of the item body. For example, Figure 24.1 shows a custom equipment repair request form and the standard printout you’d get from the Print command on the ribbon or the PrintOut method. The printout doesn’t follow the order of the form layout at all.

Printing from a folder view can be a good way to generate a report on multiple items because the user gets pretty much what is displayed on the screen. You have other built-in reporting options, both programmatic and those involving no code at all, if you have Microsoft Word and Excel.

Another technique is to use Outlook’s File | Import and Export command. However, as noted above, this command cannot export custom fields or even all standard fields. Also, the export feature is not programmable in any way. If you want to export data to another file format, you will need to write code to create the file and push Outlook data into it.

24.1.1 Printing from customized folder views

Why are folder views important to Outlook reports? Because almost anything you can show in a view can be printed out. Table views (such as the default view of your Inbox folder) are particularly useful for quick tabular reports on all kinds of Outlook data, including custom properties. Other types of views include timeline (which cannot be printed), card, icon, and day/week/month.

Table 24.1 Table View Customization Techniques

To make this change…	Do this…
Add columns to show other fields.	Right-click on any column heading in the view, choose Field Chooser, and then drag fields from the Field Chooser to the view.
Remove a field from a table view.	Drag the column heading for the field out of view.
Display fields in a different order.	Drag a column heading to a new position.
Change the width of a column.	Drag the right border of a column heading to the left to make it narrower or to the right to make it wider.
Change the width of a column.	Drag the right border of a column heading to the left to make it narrower or to the right to make it wider.
Adjust the width of a column to the best fit for the data.	Double-click on the right border of the column heading.
Organize related items by values in a particular field.	Right-click the field’s column heading, and then choose Group By This Field.
Sort by particular field(s).	Click the column heading for the field you want to sort by; to sort up to three additional fields, hold down the Ctrl key as you click the column heading.
Show only items that meet particular criteria.	Choose View, Current View, CustomizeCurrent View, click Filter, and set criteria in the Filter dialog box.
Display data that meets particular criteria in a different font.	Choose View, Current View, Customize Current View, and click Automatic Formatting.

To create a new view from scratch, choose View | Current View | Define Views. In the Define Views dialog box, click New or Copy. It is often easier to make a copy of an existing table view and modify it to suit your needs than create a totally new view.

Table 24.1 lists many different ways to customize a table view. These techniques can also be accessed by choosing View | Current View | Customize Current View and using the buttons on the Customize View dialog box, shown in Figure 24.2. Also use the commands in the Customize View dialog to customize non-table views.

Figure 24.2. Modify a view with any of these commands

As we saw in Chapter 22, it is also possible to create and modify views programmatically. The manual and programmatic approaches run up against the same limitations, though. For example, Outlook provides no way to display the entire item body in a view. In a table or timeline view with AutoPreview turned on, users can see only the first 255 characters. The same limitation applies to card views showing the Note field (in other words, the Body property).

Note
The Group By feature functions in a table view to group related items, such as those with the same category, and displays the number of items in each group. However, it cannot perform any subtotals or other calculations. If you need subtotals, a good strategy is to use the Excel method in the next section, then add formulas to do the calculations.

If a column in a table view is too narrow to show the full data in the field, the print output will also be truncated. To adjust columns accurately so that they print at the width you want, you may need to turn off automatic column sizing by following these steps:

1. Right-click on a column heading and then choose Customize Current View.

2. In the Customize View dialog box (see Figure 24.2), click Other Settings.

3. In the Other Settings dialog box (see Figure 24.3), clear the box for Automatic column sizing and then click OK twice to return to the view.

Figure 24.3. The choices in the Other Settings dialog box depend on the type of view you’re modifying

Turning off automatic column sizing also turns off the two-line or “compact” layout, allowing you to adjust the column widths for all fields in a single line.

Printing from an Outlook view is definitely a what-you-see-is-what-you-get operation. Be sure to use the Preview command on the File | Print dialog box to check whether all your columns fit on the page before you print, especially if you turn off automatic column sizing. To change the margins for printing, choose File | Page Setup | Table Style, and then switch to the Paper tab.

24.1.2 Copying data to Excel

One benefit of table views is that you can copy their data to Microsoft Excel with just a few keystrokes and then use Excel for additional formatting or data manipulation. Even custom properties can be exported in this fashion. Follow these steps in any Outlook table view:

1. Add and remove fields from a table view until it shows only the fields you want to copy. (You don’t need to worry about column width.)

2. Choose Edit, Select All, then Edit, Copy.

3. Switch to a blank Excel worksheet, and then choose Edit, Paste.

Once you have the data in Excel, you can use Excel’s formatting, formula, pivot table, and other features to get a good-looking printout with the summary and analysis that you need. For example, you might want to analyze the messages from an Exchange public discussion folder to find out how many messages are being posted by each person every month.

The copy-and-paste technique works, of course, only with standard and hidden properties that are visible in folder views. For hidden properties, you can write code to push data into Excel, a technique we’ll cover in detail a little later in the chapter. We’ve already seen a good example of this, though, in Listing 15.9, which creates an Excel report on message response times using two hidden MAPI properties.

Note that if you try to copy and paste a field that contains carriage returns, the data will get split into different cells in Excel. One solution is to use an Outlook formula field to strip the carriage returns. For example, you could use this formula to extract the first line from the Business Address Street field:

IIf(Left([Business Address Street], InStr([Business Address Street], Chr(10)))="", [Business Address Street], Left([Business Address Street], InStr([Business Address Street], Chr(10)) - 1))

and this one to generate any text on a second line:

IIf(InStr([Business Address Street], Chr(10)) > 0, Mid([Business Address Street], InStr([Business Address Street], Chr(10)) + 1, Len([Business Address Street]) - InStr([Business Address Street], Chr(10))),"")

Use such formula fields instead of Outlook’s built-in fields to “clean up” the data before you copy and paste to Excel.

24.1.3 Performing a Word mail merge

One of the most powerful ways to report on Outlook contact data is to perform a Word mail merge, starting the merge directly from any Outlook contacts folder. You will need Word 2007 installed. You can even include custom fields in the merge document, as long as they are defined in the folder (as discussed in Chapter 21).

Start in the Outlook contacts folder that contains the data you want to print and follow these steps:

1. Select one or more contacts in the folder.

2. Choose Tools | Mail Merge.

3. Make your choices on the Mail Merge Contacts dialog box (Figure 24.4), then click OK.

4. After Word displays the merge document, add merge fields, other text, and formatting as needed. Save the merge document if you think you might use it again.

5. Click the Merge button in Word.

Tip
Mail merge to Word is particularly helpful if you need to export data from a contacts folder in the Public Folders hierarchy. The built-in File | Import and Export command doesn't work with public folders.

Figure 24.4. Perform a mail merge to Microsoft Word from any Outlook contacts folder

Figure 24.5. Outlook fields are available in mail merges from the Insert Merge Field command

In Step 3, if you want to build a list or table of items, select Catalog under Document type, as Figure 24.4 shows. If you want to see custom fields in the Word merge field list, make sure you choose All contact fields under Fields to merge. Any custom fields must be defined in the User-Defined Fields in Folder list in the Field Chooser.

After the Word document opens, to insert any Outlook contact field into the merge document, use the Insert Merge Fields command from the Write & Insert Fields group (see Figure 24.5). To insert the contact body into the merge document, select the Notes field.

24.2 Coding reports with the Outlook object model

Once you know how to loop through all the items in a folder, as discussed in Chapter 15, you are halfway toward the goal of building reports using the Outlook object model. The other half of the project is to lay out the data in an informative format. These are all potential “documents” that can store a report to be printed or saved:

• Outlook email messages—especially good if you need to transmit the report to someone else

• Outlook post items—if you want to keep the report in an Outlook folder

• Text files

• Word documents

• Excel workbooks

Chapter 8 showed us how to create a text file and write data to it using FileScriptingObject techniques. Here are other code samples from earlier chapters that demonstrate different techniques related to report-building:

• Listing 13.10—Create a message listing all folders, with item count, in the current Outlook session.

• Listing 14.2—Create a message listing a spam message’s Internet headers.

• Listing 15.9—Create a report in Microsoft Excel on message response times.

• Listing 17.5—Create a reply from a tokenized boilerplate message, inserting property values from another message into the reply.

• Listing 18.1—Create a message enumerating the user’s address lists.

• Listing 18.2—Create a post filled with the names, email addresses, and phone numbers of Exchange users in the Global Address List (GAL).

• Listing 22.1—Create a message reporting on a user’s rules.

Listings 18.1, 18.2, and 22.1 are particularly valuable as examples of how to use an HTML <table> element and related tags to organize information in a tabular format inside an Outlook message or post. Word and Excel can hold tables, too, and we’ll spend the rest of the chapter examining how to get data from Outlook into documents in those two applications.

If you code a report in the form of an Outlook message or post, you can use the PrintOut method of the MailItem or PostItem object to print the report to the default printer. Outlook exposes no objects, however, to control printing. Therefore, if you need to change printers, print multiple copies, or otherwise customize the printing operation, consider generating the printable output in Word or Excel and using those applications’ objects related to printing.

24.3 Sending output to Microsoft Excel

For reports that require more formatting than Outlook views can provide or in which you want to perform complex data manipulation, Microsoft Excel is a good tool. The row and column layout of an Excel worksheet is very similar to a table view in Outlook and is easy to handle in code.

The feature that makes it possible to write code in Outlook to produce reports in Excel is called Office automation. You can start an instance of any other Microsoft Office program (or use an existing copy if it’s already running), create a new document, and add data to it. Conversely, you can design an Excel workbook with VBA code that instantiates an Outlook.Application object and uses Outlook objects and properties to fill the worksheet cells. If you want to take that approach, Chapter 7 has information on how to start an Outlook session programmatically from external code.

To create an Excel report using Outlook VBA code, you first need to learn the basics of opening a worksheet in Excel and adding data to it. Then, we’ll look at a specific example that extracts the names and addresses from an Outlook distribution list.

24.3.1 Understanding Excel report basics

To work with Excel objects in VBA, choose Tools, References, and then check the box for Microsoft Excel 12.0 Object Library. In Outlook VBA, use the Insert | Module command to insert a new code module for your report samples and add the code in Listing 24.1.

The GetExcelWS() function in Listing 24.1 not only returns an Excel.Worksheet object that you can later fill with Outlook data, but it also sets the module-level variable m_blnWeOpenedExcel to True or False depending on whether Excel was already open. Knowing the state of Excel before your Outlook code runs is crucial to being able to return Excel to the same state when your Outlook code finishes. The Set objExcel = GetObject(, "Excel.Application") statement checks whether Excel is already running. If Excel is not running, the code creates a new instance of Excel with the Set objExcel = CreateObject("Excel.Application") statement.

The RestoreExcel subroutine uses the value of m_blnWeOpenedExcel to put Excel back in its original state after your code runs. A standard framework for populating and printing an Excel worksheet, therefore, looks like this:

(Visual Basic for Applications)

Sub PrintGenericExcelReport()
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Set objWB = GetExcelWB()
    If Not objWB Is Nothing Then
        Set objWS = objWB.Sheets(1)
        ' code to fill a worksheet with data
        objWS.Cells(1, 1) = "My First Excel Report"
        objWS.Application.Visible = True
        objWS.Activate
        objWS.PrintOut
        objWB.Close SaveChanges:=False
        Call RestoreExcel
    End If
    Set objWS = Nothing
    Set objWB = Nothing
End Sub

Listing 24.1. Create a new workbook and set a variable to track the status of Excel

(Visual Basic for Applications)

Private m_blnWeOpenedExcel As Boolean
Function GetExcelWB() As Excel.Workbook
    Dim objExcel As Excel.Application
    On Error Resume Next
    m_blnWeOpenedExcel = False
    Set objExcel = GetObject(, "Excel.Application")
    If objExcel Is Nothing Then
        Set objExcel = CreateObject("Excel.Application")
        m_blnWeOpenedExcel = True
    End If
    Set GetExcelWB = objExcel.Workbooks.Add
    Set objExcel = Nothing
End Function

Sub RestoreExcel()
    Dim objExcel As Excel.Application
    On Error Resume Next
    Set objExcel = GetObject(, "Excel.Application")
    If Not objExcel Is Nothing Then
        If m_blnWeOpenedExcel Then
            objExcel.Quit
        End If
    End If
    m_blnWeOpenedExcel = False
    Set objExcel = Nothing
End Sub

If you want to print the worksheet without showing it to the user, omit the Application.Visible and Activate statements. If you want to display the worksheet but not print it, omit the Printout, Close, and CallRestoreExcel statements. You don’t need to restore Excel to its former state if you’re displaying a worksheet.

Tip
You can make Excel or Word print to a specific printer by changing the value of the ActivePrinter property of the Word.Application or Excel.Application object to the name of the printer you want to use. For example, for a worksheet object objWS: objWS.Application.ActivePrinter = "OKI C5500". That’s something you can’t do with Outlook.

The next step is to put data into the worksheet. Within a worksheet, use the Cells object to specify a particular cell and put data into it. The Cells object takes row and column numbers as parameters using the syntax Cells(row, col). This code fragment puts the text “My First Excel Report” into cell A1 (or row 1, column 1) and the text “End of Report” into cell E4 (or row 4, column 5) of a Worksheet object (objWS).

(Visual Basic for Applications)

objWS.Cells(1, 1) = "My First Excel Report"
objWS.Cells(4, 5) = "End of Report"

Listing 24.2. Apply formatting to Excel worksheets with range objects

(Visual Basic for Applications)

Sub MyFirstExcelReport()
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objRange As Excel.Range
    ' GetExcelWB from Listing 24.1
    Set objWB = GetExcelWB()
    If Not objWB Is Nothing Then
        Set objWS = objWB.Sheets(1)
        objWS.Cells(1, 1) = "My First Excel Report"
        objWS.Cells(4, 5) = "End of Report"
        Set objRange = objWS.Range _
            (objWS.Cells(1, 1), objWS.Cells(4, 5))
        objRange.Font.Bold = True
        objWS.Application.Visible = True
        objWS.Activate
    End If
    Set objRange = Nothing
    Set objWS = Nothing
    Set objWB = Nothing
End Sub

Tip

Unlike Outlook, Excel includes a macro recorder that turns your keystrokes into VBA code. To start the Excel macro recorder, click the macro recorder button on the status bar at the bottom of the Excel window. Perform various operations in Excel and click the macro recorder button again. Press Alt+F11 to enter Excel’s VBA environment, where you will find the recorded macro in one of the modules under Modules. You can copy code from Excel’s VBA window into your Outlook project, editing it as necessary to change the variable names. This can be a useful technique for discovering formatting properties and other methods in the Excel object model.

Another useful Excel object is the Range object, which can cover an area that includes more than one cell, even nonadjacent areas. For simple rectangular ranges, you can use the Cells object to define a Range by its upper-left and lower-right corners. The MyFirstExcelReport procedure in Listing 24.2 adds the text from the code snippet above, then gives it bold formatting and displays it to the user. It uses the GetExcelWS() function from Listing 24.1.

To find out more about Excel objects, properties, and methods, you can use the object browser in either Outlook or Excel VBA.

24.3.2 Building a distribution list report

Back in Chapter 8, Listing 8.21 demonstrated how to create a distribution list by reading data from a text file. Let’s go the other way and export an existing Outlook personal distribution list to an Excel workbook. The DLToExcel() subroutine retrieves each member of the distribution list and puts its display name and email address into an Excel worksheet. It takes a DistListItem object as its parameter. To test it, open any personal distribution list from a contacts folder and run this statement in the Outlook VBA Immediate window.

(Visual Basic for Applications)

Call DLToExcel(Application.ActiveInspector.CurrentItem)

Listing 24.3. Extract the members of a distribution list to an Excel worksheet

(Visual Basic for Applications)

Sub DLToExcel(dl As Outlook.DistListItem)
    Dim objOL As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objRecip As Outlook.Recipient
    Dim objTempRecip As Outlook.Recipient
    Dim objAddrEntry As Outlook.AddressEntry
    Dim objRange As Excel.Range
    Dim strAddress As String
    Dim strExType As String
    Dim strType As String
    Dim i As Integer
    Dim intRow As Integer
    Dim intCol As Integer
    On Error Resume Next
    Set objOL = Application
    Set objNS = objOL.Session
    ' GetExcelWB from Listing 24.1
    Set objWB = GetExcelWB()
    Set objWS = objWB.Sheets(1)
    objWS.Cells(1, 1) = dl.Subject
    intRow = 3
    For i = 1 To dl.MemberCount
        strExType = ""
        Set objRecip = dl.GetMember(i)
        If objRecip.AddressEntry.Type = "MAPIPDL" Then
            strExType = "Personal DL"
        Else
            Set objTempRecip = objNS.CreateRecipient _
                               (objRecip.Address)
            If objTempRecip.Resolve Then
                Set objAddrEntry = objTempRecip.AddressEntry
                Select Case objAddrEntry.AddressEntryUserType
                    Case olExchangeUserAddressEntry
                        strExType = "Exchange User"
                    Case olExchangeDistributionListAddressEntry
                        strExType = "Exchange DL"
                    Case olExchangePublicFolderAddressEntry
                        strExType = "Exchange Public Folder"
                    Case olExchangeRemoteUserAddressEntry
                        strExType = "Exchange Contact"
                End Select
            End If
        End If
        objWS.Cells(intRow, 1) = objRecip.Name
        objWS.Cells(intRow, 2) = objRecip.Address
        objWS.Cells(intRow, 3) = objRecip.AddressEntry.Type
        objWS.Cells(intRow, 4) = strExType
        intRow = intRow + 1
    Next
    intRow = intRow - 1
    Set objRange = objWS.Range _
        (objWS.Cells(3, 1), objWS.Cells(intRow, 4))
    For i = 1 To 4
        objRange.Columns(i).EntireColumn.AutoFit
    Next
    objWB.Names.Add _
        Name:=Replace(dl.Subject, " ", ""), _
        RefersTo:="=" & objWS.Name & _
        "!" & objRange.Address & ""
    objWS.Application.Visible = True
    objWS.Activate
    Set objOL = Nothing
    Set objNS = Nothing
    Set objRecip = Nothing
    Set objTempRecip = Nothing
    Set objAddrEntry = Nothing
    Set objWS = Nothing
    Set objWB = Nothing
    Set objRange = Nothing
End Sub

Here are a few notes on the code in Listing 24.3:

• The DLToExcel procedure uses the GetExcelWB() function from Listing 24.1.

• The address for each entry in the personal distribution list (DL) is in SMTP format, except for nested DLs, which have no address.

• Even though the DistListItem.GetMember method returns a Recipient object, the AddressEntry from that recipient doesn’t have as much information as it would if the Recipient were coming from a message. To determine the type of entry, the code must create a new Recipient using the Namespace.CreateRecipient method and use the AddressEntry details from that new Recipient object. The code in the Select Case block uses the new AddressEntry.AddressEntryUserType property to find out what kind of recipient we’re dealing with.

• The Autofit method adjusts the width of each column in the Range object for the DL member list to make sure that the user can see the complete name and address.

• The procedure also assigns a name to the range that contains the data. Named ranges are an important Excel feature that make it easy to use a particular set of cells for mail merge, import into Outlook, and other functions.

The For ... Next block is the heart of the DLToExcel subroutine. You can use this type of block to put any kind of Outlook data into Excel cells. For example, if you wanted to copy data from all items in an Outlook Folder object (objFolder) into an Excel worksheet (objWS), you would set the value for the starting row and use a For Each ... Next loop to pick up properties from all items in the folder.

(Visual Basic for Applications)

intRow = 1
For Each objItem in objFolder.Items
    objWS.Cells(intRow, 1) = objItem.property1
    objWS.Cells(intRow, 2) = objItem.property2
    objWS.Cells(intRow, 3) = _
           objItem.UserProperties("property3")
      intRow = intRow + 1
Next

The last line in the For Each ... Next loop increments intRow so that data input for the next item takes place on a blank new row. After the loop finishes with the last item, the worksheet will contain a block of intRow – 1 rows, containing data from intRow – 1 items from the folder.

This type of loop is a valuable technique for exporting information from both standard and custom fields for use in Excel or in another program that can read Excel data files (or a comma-delimited file saved from an Excel worksheet). However, some data values from Outlook need a bit of formatting to work properly in Excel.

24.3.3 Formatting Outlook data for Excel

Because Outlook and Excel display data in different ways, you may need some small helper functions to make Outlook data look good in Excel. The functions in this section will help you format date/time, Boolean, and text property values properly.

Use the DateToExcel() function in Listing 24.4 to handle the #1/1/4501# date that Outlook displays as “None” when the user has not selected a date.

Listing 24.4. Convert "None" date values to null

(Visual Basic for Applications)

Function DateToExcel(propVal)
    Dim dteDate     ' As Date
    If IsDate(propVal) Then 
        dteDate = CDate(propVal)
        If dteDate = #1/1/4501# Then
            DateToExcel = Null
        Else 
            DateToExcel = dteDate
        End If 
    Else
        DateToExcel = propVal
    End If
End Function

Listing 24.5. Convert true and false values to strings

(Visual Basic for Applications)

Function YesNoToString(propVal)
    Select Case propVal
        Case True
            YesNoToString = "Yes"
        Case False
            YesNoToString = "No"
        Case Else
            YesNoToString = CStr(propVal)
    End Select
End Function

Use the YesNoToString() function in Listing 24.5 to convert data in yes/no properties to the strings "Yes" and "No". Otherwise, Excel will use the values -1 and 0 for values that Outlook stores as True and False.

Use the TextToExcel() function in Listing 24.6 to remove carriage returns and tabs from text and truncate the last string to the maximum number of characters an Excel cell can hold. (Excel displays these control characters with an ugly little rectangle.)

The functions in this section do not use data types for the arguments and function declarations for two reasons. First, omitting the data typing makes them suitable for use with VBScript behind Outlook forms, as well as in VBA. Second, all the functions are designed so that you can pass through data of any type. If the data does not need to be changed to work well in a worksheet, the function returns the data unchanged.

To put those functions to work, let’s build a rather generic procedure for exporting Outlook messages to Excel.

The InboxToExcel procedure in Listing 24.7 uses a Table object to read the data from different properties. But instead of writing code for each property, just one code statement does all the work of telling the procedure which properties to export.

(Visual Basic for Applications)

strProps = _
      "SenderName,To, Subject, SentOn,ReadReceiptRequested"

From this one statement, the code creates an array that controls the column headings for the worksheet and columns added to the Table.Columns collection to expose the data. Not all properties can be handled this way, but most standard properties can. The Help topic on the Table object provides details on those that can’t, such as Body and HTMLBody. You can also add custom and hidden properties to the list of processed properties by adding their MAPI schema property names to the strProps list.

Listing 24.6. Clean up text to fit into Excel cells

(Visual Basic for Applications)

Function TextToExcel(propVal)
    If VarType(propVal) = vbString Then
        propVal = Replace(propVal, vbCr, " ")
        propVal = Replace(propVal, vbTab, " ")
        propVal = Left(propVal, 32767)
    End If
    TextToExcel = propVal
End Function

Listing 24.7. Export message properties from the Inbox to Excel

(Visual Basic for Applications)

Sub InboxToExcel()
    Dim objOL As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objInbox As Outlook.Folder
    Dim objTable As Outlook.Table
    Dim objRow As Outlook.Row
    Dim objMsg As Outlook.MailItem
    Dim objWB As Excel.Workbook
    Dim objWS As Excel.Worksheet
    Dim objRange As Excel.Range
    Dim strFind As String
    Dim strProps As String
    Dim arr() As String
    Dim val As Variant
    Dim i As Integer
    Dim intRow As Integer
    Const PR_MESSAGE_CLASS = _
      "http://schemas.microsoft.com/mapi/proptag/0x001a001e"
    On Error Resume Next  
    ' ### USER OPTION ###
    strProps = _
      "SenderName,To,Subject,SentOn,ReadReceiptRequested"
    Set objOL = Application
    Set objNS = objOL.Session
    Set objInbox = objNS.GetDefaultFolder(olFolderInbox)
    strFind = Quote(PR_MESSAGE_CLASS) & " LIKE 'IPM.Note%'"
    Set objTable = objInbox.GetTable("@SQL=" & strFind)
    ' GetExcelWB function from Listing 24.1
    Set objWB = GetExcelWB()
    Set objWS = objWB.Sheets(1)
    objWS.Name = "Inbox"
    arr = Split(strProps, ",")
    intRow = 1
    For i = 0 To UBound(arr)
        objWS.Cells(intRow, i + 1) = arr(i)
        objTable.Columns.Add arr(i)
    Next
    Set objRange = objWS.Range _
                   (objWS.Cells(1, 1), objWS.Cells(1, i + 1))
    objRange.Font.Bold = True
    Do Until objTable.EndOfTable
        intRow = intRow + 1
        Set objRow = objTable.GetNextRow
        For i = 0 To UBound(arr)
            val = objRow(arr(i))
            Select Case VarType(val)
                Case vbDate
                    ' DateToExcel from Listing 24.3
                    val = DateToExcel(val)
                Case vbBoolean
                    ' YesNoToString from Listing 24.4
                    val = YesNoToString(val)
                Case vbString
                    ' TextToExcel from Listing 24.5
                    val = TextToExcel(val)
            End Select
            objWS.Cells(intRow, i + 1) = val
        Next
    Loop
    For i = 1 To (UBound(arr) + 1)
        objWS.Columns(i).EntireColumn.AutoFit
    Next
    objWS.Application.Visible = True
    objWS.Activate
    Set objOL = Nothing
    Set objNS = Nothing
    Set objRow = Nothing
    Set objWB = Nothing
    Set objWS = Nothing
    Set objRange = Nothing
End Sub

Private Function Quote(val) As String
    Quote = Chr(34) & val & Chr(34)
End Function

A few notes on the code in Listing 24.6:

• The Table object (objTable) is filtered to show only messages by searching for items whose MessageClass property begins with "IPM.Note". This VBA statement constructs the filter string:

  strFind = Quote(PR_MESSAGE_CLASS) & " LIKE 'IPM.Note%'"
  

• The Select Case VarType(val) block adds a little efficiency by checking the type of value before running it through one of the converters. VarType() is a built-in function that returns a constant value that indicates the type of value—such as vbBoolean for a Boolean value.

• The code uses the same techniques as in Listings 24.2 and 24.3 to define ranges to give bold formatting to the column headings and autofit the columns.

• Some cells will probably be blank if no value has ever been set for that property on the item.

To change the list of properties exported from the Inbox, all you need to do is change the strProps = assignment statement to include more or fewer properties.

24.4 Sending output to Microsoft Word

Microsoft Word is an even more flexible reporting tool than Excel. Not only can it handle data in rows and columns like Excel, but it can also reproduce the look of a custom Outlook form, complete with checkboxes. Word reports are also ideal for combining information from different types of Outlook items. For example, you might need an invoice that totals the time you spent working on a particular contact’s projects.

Two of the basic techniques involved are parallel to those in Excel:

• Create a new Word document, tracking whether Word was already open.

• Convert data in Outlook to text that looks good in Word.

In most cases, your Word report will start with a document template—an existing .dot file that contains text, formatting, and other components you want to include in the report. The Outlook code creates a new document based on that template, and then populates it with the Outlook data. One of the advantages of that approach is that the report “consumer”—someone from the department that will generate or use it—can design the report template file using Word, without any need to know Outlook code.

24.4.1 Understanding Word report basics

The basics of building reports with Word are very similar to the Excel techniques discussed earlier in the chapter. For VBA code, you must first use Tools, References to add a reference to the Microsoft Word 12.0 Library that is installed on your system.

Listing 24.8. Create a new document and set a variable to track the status of Word (VBA)

(Visual Basic for Applications)

Private m_blnWeOpenedWord As Boolean
Function GetWordDoc(Optional templatePath As String) _
  As Word.Document
    Dim objWord As Word.Application
    On Error Resume Next
    m_blnWeOpenedWord = False
    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
        m_blnWeOpenedWord = True
    End If
    If templatePath = "" Then
        templatePath = "Normal.dotm"
    End If
    Set GetWordDoc = objWord.Documents.Add(templatePath)
    Set objWord = Nothing
End Function

Sub RestoreWord()
    Dim objWord As Word.Application
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If Not objWord Is Nothing Then
        If m_blnWeOpenedWord Then
            objWord.Quit
        End If
    End If
    m_blnWeOpenedWord = False
    Set objWord = Nothing
End Sub

Compare the basic GetWordDoc() and RestoreWord procedures for VBA in Listing 24.8 with the corresponding Excel procedures in Listing 24.1. Since Word is often used to print the details of a single Outlook custom form item, the procedures for VBScript code behind an Outlook form are in Listing 24.9.

Listing 24.9. Create a new document and set a variable to track the status of Word (VBScript)

Private m_blnWeOpenedWord   ' As Boolean

Function GetWordDoc(templatePath)   ' As Word.Document
    Dim objWord     ' As Word.Application
    On Error Resume Next
    m_blnWeOpenedWord = False
    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
        m_blnWeOpenedWord = True
    End If
    If templatePath = "" Then
        templatePath = "Normal.dotm"
    End If
    GetWordDoc = objWord.Documents.Add(templatePath)
    Set objWord = Nothing
End Function

Sub RestoreWord()
    Dim objWord     ' As Word.Application
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If Not objWord Is Nothing Then
        If m_blnWeOpenedWord Then
            objWord.Quit
        End If
    End If
    m_blnWeOpenedWord = False
    Set objWord = Nothing
End Sub

One difference from the Excel code is the addition of a parameter—optional in the VBA version—to create a Word document using a specific template. As you will see, this is the key to making Word printouts that duplicate the look of Outlook form pages.

A Word template can contain boilerplate text, plus marked areas that you can use to place Outlook data in the text. To create a template from a new or existing Word document, click the Office button, and then choose Save As | Word Template. If you specify only a file name and no path for the template argument for the GetWordDoc() function, Word looks in the user’s default templates folder.

A standard framework for populating and displaying or printing a Word document in VBA looks like the code in Listing 24.10, which adds one sentence at the top of the document, using the objDoc.Content.InsertBefore method. Compare with the Excel report in Listing 24.2.

Listing 24.10. Create and print a simple Word document with VBA

(Visual Basic for Applications)

Sub GenericWordReport()
    Dim objDoc As Word.Document
    Dim objSel As Word.Selection
    ' Const wdDoNotSaveChanges = 0
    Set objDoc = GetWordDoc("Normal.dotm")
    If Not objDoc Is Nothing Then
        'fill the document with data
        objDoc.Content.InsertBefore "Today is " & _
           FormatDateTime(Date, vbLongDate)
        objDoc.Application.Visible = True
        objDoc.Activate
        objDoc.PrintOut False
        objDoc.Close wdDoNotSaveChanges
        Call RestoreWord
    End If
    Set objDoc = Nothing
    Set objSel = Nothing
End Sub

To print the document without showing it to the user, omit the Application.Visible and Activate statements. To display the document without printing it, omit the Printout, Close, and Call RestoreWord statements. There is no need to restore Word to its former state if you’re displaying a document, because you will leave Word open.

To use the code in VBScript, remove the apostrophe to uncomment the Const statement and remove the data typing from the Dim statements.

This statement is important to code-driven printouts:

(Visual Basic for Applications)

objDoc.PrintOut False

The False argument tells Word not to print the document in the background. Instead, code execution waits for the document to print. Don’t use background printing if your code closes the document immediately after printing. That will avoid the user seeing a prompt about the document trying to close while it’s still printing.

As with Excel, you can use the macro recorder to investigate Word’s methods and properties, then adapt at least some of the resulting VBA code to your Outlook projects. Many Word macros use the Selection object, which represents the currently selected text in a document, and Chapter 17 has other examples of moving around in and adding text to a Word Document object using the Selection object.

However, you will gain flexibility if you use the Range object instead, as it allows you to manipulate the document independently of the user’s selection. The Content property of a Word document, which Listing 24.10 uses, is a Range object that represents the entire content of the document. The Range.InsertBefore method inserts text before the Range. Other examples in this chapter use the Range.InsertAfter method, which adds text after the area of the document that the Range object represents.

24.4.2 Formatting Outlook data for Word

It would be tedious to write code to insert text into a Word document, write more code to add data from an Outlook field, then write code to add more text, and then another field, and so on. In addition, it would be very difficult to revise such a report if you wanted to change some of the standard text in the report. And it would be a nightmare to create versions in different languages.

Listing 24.11. Convert date values to a string

(Visual Basic for Applications)

Function DateToString(propVal)  ' As String
    Dim dteDate     ' As Date
    If IsDate(propVal) Then
        dteDate = CDate(propVal)
        If dteDate = #1/1/4501# Then
            DateToString = "None"
        Else
            DateToString = _
              FormatDateTime(dteDate, vbLongDate)
        End If
    Else
        DateToString = CStr(propVal)
    End If
End Function

A better approach is to lay out the report as a Word document template with content controls acting as placeholders for the Outlook data. If you include all the boilerplate text in the template itself, the only code you need to write is that to place the data into the content controls. As an added benefit, this approach makes it easy to divide up the work. For example, a user who is going to use the printouts could design the template, while you write the code.

You probably will want to perform the same kind of data conversion for Outlook date/time and yes/no properties that we did earlier for Excel. The YesNoToString() function in Listing 24.4 would work just as well in Word automation code as in Excel code. Listing 24.11 is a DateToString() function to convert Outlook dates to strings. For dates that Outlook shows as “None” and stores as #1/1/4501#, it returns the string "None".

So that the DateToString() function can be used in VBScript, it uses FormatDateTime() instead of Format() to format the returned date string. Also, it returns just the date. If you want time values as well as date values, you might want to build a similar function of your own to return a date string with both date and time values. Other useful functions for formatting that work in both VBScript and VBA are FormatCurrency() and FormatNumber(). You’ll see both of those in the sample project in the next section.

24.5 Using Word to build an invoice report

To complete your Outlook report skills, you need to be able to put data from a single Outlook item into a Word document and also generate a report on multiple items. This final example combines those two operations by reporting on an Outlook item—and on its related items contained in a different folder.

This application depends on the Links collection covered in Chapter 20 to build an invoice with a contact’s details and a table listing the hours spent working for that contact, as recorded by the linked journal entries in the Journal folder. It introduces techniques for working with Word tables and with the new content controls in Word 2007. You’ll see how to write data into content controls and then lock them, as well as how to insert data into cells and create new table rows.

Figure 24.6. Use Word 2007’s new content controls to design reports on Outlook data

Note
To build this template, you’ll need to display the Developer tab in the ribbon. If you haven’t done that already for Outlook custom forms design, in Outlook, choose Tools | Options, switch to the Mail Format tab, click Editor Options, check the box for Show Developer tab in the Ribbon, and then click OK.

Content controls are a new feature in Word 2007, providing an improved alternative to the form fields that you might have used in earlier Word versions. They provide a more modern look that gives better visual feedback to the user. In a document using content controls, you don’t need to activate any kind of document protection. Only the controls are available for input. The user can press the Tab key to jump from one control to the next. Any control can be locked.

Look for the content controls in the left half of the Developer tab’s Controls group, as shown in Figure 24.6. Besides a plain text input box and a combo box, there are content controls that support rich-text input, images, insertion of building blocks (another new Word 2007 feature), and a date picker. There is, however, no check box content control.

The invoice sample application consists of a Word template and Outlook code—in this case, VBA code to work with the currently selected contact. No custom form is involved, although it would certainly be possible to write the code that generates the invoice as VBScript code for a custom form, rather than as VBA. After presenting the complete example, we’ll go over what would be necessary to convert it to VBScript.

24.5.1 Building the invoice template

The steps involved in designing the invoice template are as follows:

1. Lay out a table at the top of the document with a content control for each Outlook contact property the invoice should display and for the invoice details like the date, invoice number, and comments.

2. Lay out the first two rows of a table at the bottom of the document with column headings for the data that will be pulled from the Journal folder. Leave the second row blank.

3. In design mode, set the Tag and other properties for each content control.

4. Add three rich-text content controls to encompass the contact information at the top of the form, the invoice detail in the table at the bottom, and the entire template content. These help lock the content.

5. Save the template file in the default templates folder (%appdata%\Microsoft\Templates) or in any other folder.

To design the Word template, you can start from a blank Word document or from an existing document that already has a layout you want to use for your invoice. The area at the top of the template will hold the contact details. A table at the bottom will contain a list of the related journal entries. Let’s walk through the five steps in detail.

**Step 1   **At the top of the page, type in and format the static text that you want to appear on every invoice. Add a company logo if you like. I recommend that you use a table to help align the different pieces of information you want to list. Figure 24.7 shows the invoice with a company logo, some static text, and a table with no borders that is ready to receive the first content controls. To turn off the table borders, we used the Borders command in the Table Styles group on the Design tab. To turn on the table gridlines to make it easier to adjust column widths, switch to the Layout tab, and click View Gridlines.

Figure 24.7. Start a Word template with static text and perhaps a table to help create a tidy layout

To insert content controls, display the Developer tab in the ribbon.

Where the contact name and address should appear—in the blank table cell to the right of the Invoice submitted to: text, click the Text button in the Controls group (second from left in top row) to add a text content control for each Outlook property. This invoice needs text controls for the user’s name, street address, city, state, and postal code—five controls in all—with the usual punctuation for the address elements.

Tip
To move the insertion point to the right of a content control so that you can type text on the same line, press End after you insert the control.

For the date, insert a date picker content control (second from left in bottom row).

Add a blank table row and then another table row in the top section so that you can insert static text and additional content controls for the invoice number and hourly rate. Adjust the table cell widths as needed.

Figure 24.8. Content controls act as placeholders for data to be entered either manually or through code

To complete the top section, in the paragraph below the table, click Rich Text in the controls group to insert a control to hold the comments that the user will type in after your code generates the invoice. At this stage in the design process, with step 1 of 5 completed, the template should look similar to Figure 24.8.

Step 2   For the bottom section that will hold the invoice information, insert a second table with two rows and four columns. Type column headers (Date, Activity, Hours, Total) into the cells in the first row. Leave the second row empty. You may want to experiment with the different table styles found on the Design tab.

Make sure there is at least one paragraph below the second table.

Step 3   Once you’re happy with the basic layout of the invoice, click the Design Mode button in the Controls group on the Developer tab so that you can modify the controls’ properties. Click on each content control to select it. Then, display its Properties dialog box (see Figure 24.9) by clicking the Properties button in the Controls group.

You must enter a value for the Tag property, which the code will use to identify each control. Use the values in Table 24.2, which also lists the ContactItem property that the code will use to fill in the content control, where applicable. Some controls may have more options in the Properties dialog than others. For example, as Figure 24.9 shows, you can choose a date format for the date picker.

Figure 24.9. Set a value for the Tag property of each content control you use in an Outlook report template

Tip
The Title property for a content control is optional, but may be helpful if you want the user to see an identifying name above the control when it has the focus.

Table 24.2 Tag Values for Invoice Content Controls

Control Description	Tag	Will hold Outlook Property
Customer name	Name	FullName
Company name	Company	CompanyName
Street address	Address	BusinessAddressStreet
City	City	BusinessAddressCity
State	State	BusinessAddressState
Postal code	Postal Code	BusinessAddressPostalCode
Invoice number	Invoice Number	n/a—generated by code
Invoice date	Invoice Date	n/a—generated by code
Comments	Comments	n/a—user types in
Hourly rate	Hourly Rate	BillingInformation
Top portion of template, including upper table	Contact Info	n/a—container for other controls
Bottom portion of template, including lower table	Invoice Info	n/a—container for other controls
Entire document	Invoice	n/a—container for other controls

After you have entered a value for the Tag property for each control, the template should look like Figure 24.10. See how the Tag values from Table 24.2 bracket each control. The Comments control has a Title as well as a Tag.

Figure 24.10. Tags on content controls provide the information that code needs to access them programmatically

Step 4   Select the document content from the top of the page down through the table in the first section, leaving out the Comments control. Click the Rich Text button (top, left of the Controls group) to put the entire selection inside a new control. Select this control, click Properties, and give this new control a Tag named “Contact Info.”

Select the document content from the bottom of the page, up through the second table, again leaving out the Comments control. Click Rich Text to create a new control containing the table, select the new control, click Properties, and give this control a Tag named “Invoice Info.”

Now press Ctrl+A to select the entire document, and click Rich Text again to insert another rich-text control around all the template content. Set the Tag property to “Invoice” and check the boxes for Content control cannot be deleted and Contents cannot be edited. By grouping the content controls, you can give the template behavior like that in a protected form. The outermost (or parent) control can be locked to prevent the user from changing the text, allowing input only in the content controls. A “container control” can also be used to identify a range in a similar manner as a bookmark, but can be protected against deletion. The code will use both the locking and identification features of these controls.

Caution
Make sure that the rich-text controls that enclose the tables at the top and the bottom of the document, plus the third control that encloses all the content, have their anchors completely outside the tables. If an anchor is in a table cell, the code will not be able to put a value into that cell.

Step 5   You’ve completed the template design and can now save and close the template. Click the Office button, click the arrow next to Save As, and then choose Word Template. To store it in your default templates folder, give the file the path and name %appdata%\Microsoft\Templates\Invoice.dotx. If you use a different path or file name, make a note of it, as you’ll need it in the code.

24.5.2 Coding the invoice report

Now that you have prepared the template, it’s time to start looking at the code to enter data into the content controls and fill in the invoice details table. Listing 24.12 at the end of this section contains the entire code for the invoice application. You may want to put all the code in a separate Outlook VBA module to make it easier to manage. Don’t forget to add references to the Microsoft Word 12.0 Library and Microsoft Scripting Runtime libraries, if you have not done so already.

The invoice report code makes several assumptions:

• The hourly billing rate for the contact item is stored in the BillingInformation property. You can use the All Fields page of the contact to enter this rate.

• The Journal folder contains only items that have not yet been invoiced.

• Several procedures are available either as private procedures in the same module or as public procedures in other Outlook VBA modules: GetCurrentItem() from Listing 15.5, and DateID() and AddLeadingZeroUnderTen() from Listing 20.6. These are not repeated in Listing 24.12, but the code will fail if they are not available in your VBA project.

Which procedure actually runs the invoice application? It’s the GenerateWordInvoice routine, the only procedure not declared Private. The GenerateWordInvoice routine calls four other routines in turn:

1. GetCurrentItem() from Listing 15.5 to get the currently selected or open contact and assign it to the objItem variable.

2. GetNewDocument() to create a new document from the Invoice.dotx template and assign it to the objDocument variable. Note that this procedure will only be called if GetCurrentItem() returns a contact item type.

3. FillContactInfo() to fill the Word content controls in the new document with corresponding data from the selected contact.

4. FillInvoiceInfo() to filter the Journal folder for entries matching the contact, post entries linked to the contact to the Word table, calculate the charge for each entry, and keep a running total of the charges to post in the last row of the table.

If you store the invoice template using a file name other than Invoice.dotx or in a location other than the user’s default Templates folder, change the GetNewDocument statement in the GenerateWordInvoice procedure to match your file name and path. For example, if you store the template as MyInvoice.dotx in a folder named C:\Samples, use this statement:

(Visual Basic for Applications)

Set objDoc = GetNewDocument("C:\Samples\MyInvoice.dotx")

Figure 24.11 shows the finished product: an invoice produced entirely from Outlook items, but laid out with a Word template. The Comments content control is the only one where the user can type any text. The rest of the document’s content is locked.

Figure 24.11. This invoice demonstrates how well Word works as a reporting tool that can combine data from items in different Outlook folders

Let’s review the Word techniques involved and then examine the Outlook techniques.

The challenge of writing code for Word content controls is that they can’t be addressed by name like many other objects in Outlook and Word collections can. For example, even though the template should have a control whose Tag property has the string value "Company", you can’t return that control with an expression like this:

(Visual Basic for Applications)

objDoc.ContentControls("Company")

Instead, to return a specific control, you need to know either a control’s position in the document or the value of its ID property. Word assigns the ID property value when the control is generated; a typical value is 72569880. Since obtaining and noting ID values then using them in your code would involve a lot of work and is not intuitive, we’ll use a helper procedure named GetContentControls() to create our own index of the controls, using the Tag property value for each one. This procedure loops through the entire document and adds each content control to a global Scripting.Dictionary variable (m_ContentControls) with the Tag property value as the key (index) value. (Go back to Chapter 8 if you don’t recall how the Dictionary object works.) Subsequent procedures use this collection to access the content controls, looking up each with the value assigned to the Tag property. The following code snippet, for example, returns the content control that has a Tag property with the value "Invoice Info", in other words, the control containing the table that will hold the Journal folder information.

(Visual Basic for Applications)

Set objInvoiceControl = objDoc.ContentControls( _
  m_ContentControls("Invoice Info"))

To add text to a plain text content control, which is the type this report uses most, assign a string value to its Range.Text property, as in this example from the FillContentControl procedure.

(Visual Basic for Applications)

objContentControl.Range.Text = strContent 

That technique works for the date picker content control as well, assuming that strContent is a string representing a date.

Content controls can be locked so that the user cannot edit the content. A locked control also blocks programmatic data entry. So that the code can enter data programmatically, each content control is locked not at design time, but at run time, only after its value has been filled in. To lock a content control, set its LockContents property to True.

(Visual Basic for Applications)

objContentControl.LockContents = True

The steps of obtaining a content control object from the m_ContentControls collection, assigning the contact information to each control, then locking the content control, are consolidated in the procedure FillContentControl. This procedure is used by the FillContactInfo procedure, which fills in all the content in the top half of the report, calling FillContentControl once for each control that needs to be filled.

Word tables do not support any property that allows them to be assigned a name with which to easily identify them. The only way to pick up a Word table is by its index number within a specified range of text. The range can be the entire document, or you can narrow it down by various means, such as assigning a bookmark to the table. Since content controls can be nested, and a content control has a Range property, we used a content control (“Invoice Info”) to act as a container for the table that will hold the journal entry data. Since it is the only table inside that control, we can access it like this:

(Visual Basic for Applications)

Set objInvoiceControl = objDoc.ContentControls( _
  m_ContentControls("Invoice Info"))
Set objTable = objInvoiceControl.Range.Tables(1)

The cells in a Word table can be addressed in a similar manner as cells in Excel by their row and column positions using the syntax objTable.Cell(row, col), or by using objRow.Cells(index) within a specified range. Since the data for the invoice is filled row by row, this sample code works through each row’s cells in turn, inserting the text at the end of the cell’s current content. For example, to insert the Activity information for the second column of the first data row, the row you left blank at design time.

(Visual Basic for Applications)

Set objRow = objTable.Rows.Last
objRow.Cells(2).Range.InsertAfter expr

where expr is an expression returning the text to insert. As you’ll see below, the code generates that expression from an Outlook Table object.

Appending a new row to an existing Word table is quite straightforward.

(Visual Basic for Applications)

Set objRow = objTable.Rows.Add

To apply formatting to text in a Word document, first identify the range, and then specify the format. In this sample, the current table row is formatted as bold.

(Visual Basic for Applications)

objRow.Range.Font.Bold = True

The most interesting Outlook technique used in the invoice report is a filtered Table object. The filter matches the FullName of the contact that we’re invoicing against the names in the Contacts box on the journal entry form (which, as you should recall from Chapter 20, exposes the contents of the Links collection).

Note

In taking this approach—searching for a name rather than confirming a Link—we’re making a significant assumption: that the name of the contact has not changed since the journal entries were created. If the contact name has changed, then the search won’t return all the linked items, because the field we’re searching will still have the old names. The performance tradeoff, though, is considerable. By searching just on the name, we can get back a filtered, read-only Table very quickly. If the code had to depend on confirming an actual Link in the Links collection, it would be necessary to examine the Links collection of each item in the Journal folder.

These code statements in the FillInvoiceInfo procedure filter the Journal folder to return a Table with journal entries related to the contact being invoiced.

(Visual Basic for Applications)

SEARCH_LINKS = "http://schemas.microsoft.com/" & _
  "mapi/id/{00062008-0000-0000-C000-000000000046}/" & _
  "853A101E"
Set objOL = objContact.Application
Set objNS = objOL.Session
Set objJournal = objNS.GetDefaultFolder(olFolderJournal)
strFind = Quote(SEARCH_LINKS) & " = '" & _
          objContact.FullName & "'"
Set objOLTable = objJournal.GetTable("@SQL=" & strFind)

Since the default Columns collection for a journal folder does not contain all the properties that the invoice needs, the code adds a few properties and sorts the Table.

(Visual Basic for Applications)

With objOLTable
    intItemsCount = objOLTable.GetRowCount
    .Columns.Add "Start"
    .Columns.Add "Duration"
    .Columns.Add "Type"
    .Sort "Start"

To complete the With objOLTable section, a Do loop processes each row in the Outlook.Table object, adding a new Word.Table row on each pass.

(Visual Basic for Applications)

    Do Until .EndOfTable
        Set objOLRow = .GetNextRow
        ' code to fill in the Word table
        If objRow.Index <= intItemsCount Then
            Set objRow = objTable.Rows.Add
        End If
    Loop
End With

Inside the Do loop, the code to fill in the Word table combines the Cells(index).Range.InsertAfter method discussed above with the very simple objOLRow("property") syntax to return a property value from an Outlook table Row object. For example, this statement combines the Type and Subject property values from the journal entry and inserts them into the second column of the current row in the Word table.

(Visual Basic for Applications)

objRow.Cells(2).Range.InsertAfter _
  objOLRow("Type") & " -" & objOLRow("Subject")

Listing 24.12. Collate data from two folders into one Word document

(Visual Basic for Applications)

Option Explicit

' requires reference to Microsoft Scripting Runtime
Private m_objContentControls As Scripting.Dictionary

Sub GenerateWordInvoice()
    ' requires reference to Microsoft Word 12.0 library
    Dim objDoc As Word.Document
    Dim objContentControl As Word.ContentControl
    Dim objItem As Object
    On Error Resume Next
    ' GetCurrentItem() from Listing 15.5
    Set objItem = GetCurrentItem()
    If objItem.Class = olContact Then
        Set objDoc = GetNewDocument("Invoice.dotx")
        Call FillContactInfo(objDoc, objItem)
        Call FillInvoiceInfo(objDoc, objItem)
        objDoc.Application.Visible = True
        objDoc.Activate
        Set objContentControl = _
          objDoc.ContentControls( _
          m_objContentControls.Item("Comments"))
        objContentControl.SetPlaceholderText , , _
          "Type comments on this invoice here."
        objContentControl.Range.Select
        objDoc.ActiveWindow.View.TableGridlines = False
    End If
    Set objDoc = Nothing
    Set objContentControl = Nothing
    Set objItem = Nothing
    Set m_objContentControls = Nothing
End Sub

Private Function GetNewDocument(strTemplate As String) _
  As Word.Document
    ' requires reference to Microsoft Word 12.0 library
    Dim objWord As Word.Application
    Dim objDoc As Word.Document
    On Error Resume Next
    Set objWord = GetObject(, "Word.Application")
    If objWord Is Nothing Then
        Set objWord = CreateObject("Word.Application")
    End If
    Set objDoc = objWord.Documents.Add(strTemplate)
    Call GetContentControls(objDoc)
    Set GetNewDocument = objDoc
    Set objWord = Nothing
    Set objDoc = Nothing
End Function

Private Sub GetContentControls(objDoc As Word.Document)
    Dim objContentControl As Word.ContentControl
    ' requires reference to Microsoft Scripting Runtime
    Set m_objContentControls = _
      CreateObject("Scripting.Dictionary")
    For Each objContentControl In objDoc.ContentControls
        m_objContentControls.Add objContentControl.Tag, _
                                 objContentControl.ID
    Next
    Set objContentControl = Nothing
End Sub

Private Sub FillContactInfo(objDoc As Word.Document, _
                            objItem As Object)
    Dim objContentControl As Word.ContentControl
    Call FillContentControl("Name", objItem.FullName, objDoc)
    Call FillContentControl( _
      "Company", objItem.CompanyName, objDoc)
    Call FillContentControl( _
      "Address", objItem.BusinessAddressStreet, objDoc)
    Call FillContentControl( _
      "City", objItem.BusinessAddressCity, objDoc)
    Call FillContentControl( _
      "State", objItem.BusinessAddressState, objDoc)
    Call FillContentControl("Postal Code", _
      objItem.BusinessAddressPostalCode, objDoc)
    ' DateID() from Listing 20.6
    Call FillContentControl( _
      "Invoice Number", DateID(), objDoc)
    Call FillContentControl("Hourly Rate", _
      FormatCurrency(objItem.BillingInformation), objDoc)
    Call FillContentControl("Invoice Date", _
      FormatDateTime(Date, vbLongDate), objDoc)
    Set objContentControl = _
       objDoc.ContentControls( _
       m_objContentControls.Item("Contact Info"))
    objContentControl.LockContents = True
    Set objContentControl = Nothing
End Sub

Private Sub FillContentControl(strControl As String, _
    strContent As String, objDoc As Word.Document)
    Dim objContentControl As Word.ContentControl
    Dim strControlID As String
    strControlID = m_objContentControls.Item(strControl)
    Set objContentControl = _
       objDoc.ContentControls(strControlID)
    If Len(strContent) > 0 Then
        objContentControl.Range.Text = strContent
        objContentControl.LockContents = True
    Else
        objContentControl.Delete
    End If
    Set objContentControl = Nothing
End Sub
Private Sub FillInvoiceInfo(objDoc As Word.Document, _
    objContact As Object)
    Dim objOL As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim objJournal As Outlook.Folder
    Dim objOLTable As Outlook.Table
    Dim objOLRow As Outlook.Row
    Dim objTable As Word.Table
    Dim objRow As Word.Row
    Dim objInvoiceControl As Word.ContentControl
    Dim curHourly As Currency
    Dim curItem As Currency
    Dim curTotal As Currency
    Dim intItemsCount As Integer
    Dim strFind As String
    Dim SEARCH_LINKS As String
    On Error Resume Next
    Set objInvoiceControl = objDoc.ContentControls( _
    m_objContentControls("Invoice Info"))
    Set objTable = objInvoiceControl.Range.Tables(1)
    Set objRow = objTable.Rows.Last
    curHourly = CCur(objContact.BillingInformation)
    Set objOL = objContact.Application
    Set objNS = objOL.Session
    Set objJournal = objNS.GetDefaultFolder(olFolderJournal)
    SEARCH_LINKS = "http://schemas.microsoft.com/" & _
      "mapi/id/{00062008-0000-0000-C000-000000000046}/" & _
      "853A101E"
    strFind = Quote(SEARCH_LINKS) & _
              " = '" & objContact.FullName & "'"
    Set objOLTable = objJournal.GetTable("@SQL=" & strFind)
    With objOLTable
        intItemsCount = objOLTable.GetRowCount
        .Columns.Add "Start"
        .Columns.Add "Duration"
        .Columns.Add "Type"
        .Sort "Start"
        Do Until .EndOfTable
            Set objOLRow = .GetNextRow
            objRow.Cells(1).Range.InsertAfter _
              FormatDateTime(objOLRow("Start"), vbShortDate)
            objRow.Cells(2).Range.InsertAfter _
              objOLRow("Type") & " - " & objOLRow("Subject")
            objRow.Cells(3).Range.InsertAfter _
              FormatNumber(objOLRow("Duration") / 60, 2)
            curItem = objOLRow("Duration") / 60 * curHourly
            objRow.Cells(4).Range.InsertAfter _
              FormatCurrency(curItem)
            curTotal = curTotal + curItem
            If objRow.Index <= intItemsCount Then
                Set objRow = objTable.Rows.Add
            End If
        Loop
    End With
    Set objRow = objTable.Rows.Add
    objRow.Range.Font.Bold = True
    objRow.Cells(3).Range.InsertAfter "TOTAL"
    objRow.Cells(4).Range.InsertAfter _
      FormatCurrency(curTotal)
    objInvoiceControl.LockContents = True
    Set objInvoiceControl = Nothing
    Set objTable = Nothing
    Set objRow = Nothing
    Set objOL = Nothing
    Set objNS = Nothing
    Set objJournal = Nothing
    Set objOLTable = Nothing
    Set objOLRow = Nothing
End Sub

Private Function Quote(val) 
    Quote = Chr(34) & CStr(val) & Chr(34)
End Function

24.5.3 Possible enhancements for the invoice report

Even though Listing 24.12 contains a lot of code, converting it to VBScript so that it could be incorporated into an Outlook custom form is not as hard as it looks. You’d need to make these five changes:

• Remove or comment all As clauses from Dim statements and procedure declarations.

• Remove the GetCurrentItem() function.

• Replace this statement in the GenerateWordInvoice procedure: Set objItem = GetCurrentItem() with this statement: Set objItem = Item.

• In the declarations section, after the Option Explicit statement, add this Outlook constant declaration: Const olFolderJournal = 11

• Add a command button to the contact form and a Click event handler to run the GenerateWordInvoice procedure when the user clicks the button:

  Sub CommandButton1_Click()
      Call GenerateWordInvoice
  End Sub
  

Porting the invoice report code to VBScript behind an Outlook form is certainly one way to extend its usefulness. What other ways to enhance the report can you think of? Here are a couple of ideas.

The filter in the FillInvoiceInfo procedure assumes that the Journal folder contains only uninvoiced entries. If you want to keep all entries, even after they’re invoiced, you’ll need some way to tell them apart. The BillingInformation property is a good candidate to hold this information. You could update each journal entry with the date of the invoice or the invoice number and save it. You’d also need to modify the filter string so that it looks for items with an empty or null BillingInformation property.

Another possible enhancement would be to save the invoice and create a new link to it in the journal folder. Surely you can think of other ways to apply the Word and Outlook techniques in this chapter, which comprise a fitting end to your initial excursion into the world of Outlook 2007 programming.

24.6 Summary

Making Outlook data available through reports is an important programming technique, because not everyone who needs to see your data will have Outlook. Outlook supports several techniques for generating reports—printing from customized folder views, mail merge to Word, even copy and paste to Excel. However, no single built-in technique has the flexibility to handle all Outlook data, including custom fields and “large” properties like item bodies.

By writing code to automate Outlook, Excel, and Word, developers can produce custom reports with every imaginable feature. Reports can be generated as email messages, printed output, or files where the user can fill in additional information, such as comments on an invoice. Word is a particularly fine reporting tool, especially if you need to print from a custom form or combine information from two types of folders. The search techniques covered in Chapter 16 can help you speed up report generation, particularly if you use the read-only Table object that is new to Outlook 2007.

Additional Resources

• Chapter 5: Introducing Form Regions

• Chapter 17: Working with Item Bodies

• Outlook Developer Portal

• Microsoft Office Developer Center