OL97: Displaying Excel Custom Properties in an Outlook View

MORE INFORMATION

The example below allows you to display a list of Microsoft Excel worksheets in such a manner that contents of selected cells are also displayed. For example, in Outlook your worksheets may be displayed as follows, where Department and Total are cell ranges within each worksheet:

   From          Subject          Department      Total
   ----          -------          ----------      -----
   John Doe      Sales.xls        Sales          100,000
   Jane Doe      Expenses.xls     Expenses           500
				

The example below is a four-part process. First, you create a Microsoft Excel worksheet that includes named cell ranges. Second, you define the named cell ranges as custom properties of the worksheet. Third, you create a MAPI folder in Outlook and copy the Microsoft Excel worksheet into the MAPI folder. Fourth, you create user-defined fields in the MAPI folder that match your Microsoft Excel worksheet custom properties.

Part 1 - Define Named Cell Ranges in a Microsoft Excel Worksheet

1. In Microsoft Excel, create a new worksheet as follows and format cell A2 as text and cells B2:F2 as numbers:

             A          B        C        D        E        F
       1 Department     Q1       Q2       Q3       Q4     Total
       2 Sales        25,000   25,000   25,000   25,000   100,000
   						

2. Select cell A2.
3. On the Insert menu, point to Name, and then click Define on the menu that appears to open the Define Name dialog box.
4. Under Names In Workbook, type Department over the word "Sales," click Add, and then click OK.
5. Select cell F2.
6. On the Insert menu, point to Name, and then click Define on the menu that appears to open the Define Name dialog box.
7. Under Names In Workbook, type Total, click Add, and then click OK.

Part 2 - Set Custom Worksheet Properties

1. On the File menu, click Properties to open the File Properties dialog box.
2. Click the Custom tab and click to select the Link To Content check box.
3. In the Source list, click Department, and in the Name box, type Department, and then click Add.
4. In the Source list, click Total, and in the Name box, type Total. Click Add, and then click OK.
5. Save the worksheet as Sales.xls in your My Documents folder, and then close it.

Part 3 - Create a MAPI Folder and Copy the Worksheet

1. In Outlook, on the View menu, click Folder List.
2. Click Personal Folders.
3. On the File Menu, point to New, and then click Folder to open the Create New Folder dialog box.
4. In the Name box, type Excel Folder.
5. Make sure the Folder Contains list is set to Mail Items and that the "Create a shortcut to this folder in the Outlook Bar" check box is selected.
6. Click OK. You should now see an Excel Folder shortcut under Mail on the Outlook Bar.
7. On the Outlook Bar, click Other and My Documents. You should see Sales.xls in the Details view.
8. On the Outlook Bar, click Mail and scroll so that you can see the Excel Folder shortcut.
9. Drag Sales.xls from the Details view to the Excel Folder shortcut.
10. On the Outlook Bar, click the Excel Folder shortcut to open the MAPI Excel Folder. You should see Sales.xls in the Messages view.

Part 4 - Creating User Fields in the MAPI Folder

1. On the View menu, click Show Fields to open the Show Fields dialog box.
2. In the "Select available fields from" list, click "User-defined fields in folder."
3. In the Available Fields list, click Department, and then click Add.
4. In the Available fields list, click Total, and then click Add.
5. Click OK.

In your Excel Folder's Message view you should see Sales.xls with the contents of the Department and Total custom properties displayed.