OL2000: (CW) Displaying Excel Custom Properties in an Outlook View (197905)

MORE INFORMATION

The example below allows you to display a list of 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 an 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 Excel worksheet into the MAPI folder. Fourth, you create user-defined fields in the MAPI folder that match your Excel worksheet custom properties.

Part 1 - Define Named Cell Ranges in an Excel Worksheet

1. In 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. Click to select cell A2.
3. On the Insert menu, point to Name, and then click Define 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. Click to select cell F2. Type =SUM(B2:E2), and press ENTER.
6. Click to select cell F2. On the Insert menu, point to Name, and then click Define 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. In the Name box, type Department, and then click Add.
4. In the Source list, click Total. 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. Start Outlook and on the Outlook Bar, click Other Shortcuts to make it the active group.
2. On the File menu, point to New, and then click Folder to open the Create New Folder dialog box.
3. In the Name box, type Testlink and in the Folder Contains list, click to select Mail Items. Click OK.
4. Click Yes to create a shortcut to this folder in the Outlook Bar. The shortcut will appear under the group, My Shortcuts.
5. In Other Shortcuts, click My Documents, and then find and click to select the Sales.xls file.
6. Drag the Sales.xls file to the Testlink shortcut on the Outlook Bar in the My Shortcuts group.

Part 4 - Creating User Fields in the MAPI Folder

1. Click the Testlink shortcut on the Outlook Bar. The Sales.xls file is now an item in the Testlink MAPI folder. You should see Sales.xls in the Messages view of the Testlink folder.
2. Right-click on the on a message header, such as From or Subject. Click Field Chooser on the shortcut menu.
3. From the list box at the top of the Field Chooser dialog box, click to select "User-defined fields in folder."
4. Drag the Department and Total fields to the column headers of the current view to create new columns. You should now see the Department name and the Total fields with the information from the Excel Worksheet.
5. In the Messages view, double-click Sales.xls to open it in Excel.
6. With Sales.xls open, type 50000 in the cell E2.
7. On the File menu, click Exit, and when prompted to save changes, click Yes.
   
   The updated value in the Total field of the Outlook messages view should read "125,000."