More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

Using the Microsoft Office Spreadsheet Control on an Outlook Form

If you need to have users enter several lines of data and do calculations on it -- as in an order form, for example -- consider using the Microsoft Office Spreadsheet control on your Outlook form. This control works just like an Excel spreadsheet and can be manipulated programmatically from VBScript code on the form.

Basics

The spreadsheet control requires that Microsoft Excel be installed. The control should be installed automatically with the Professional edition of Office 2003  if you install Excel. We have not checked on its availability in other Office 2003 packages.

It is not available in Office 2007. However, if you installed Office 2003 first and then upgraded to Office 2007, the spreadsheet control may be work OK.

To add the spreadsheet control to the Outlook custom form control toolbox:

  1. Open any Outlook form in design mode, and click the toolbox button on the toolbar.
  2. Right-click the blank space on the toolbox, and choose Additional Controls.
  3. If you don't see Additional Controls, open the Outlook VBA environment, create a new form, bring up its toolbox, then repeat Step 2.
  4. From the Available Controls list, choose Microsoft Office Spreadsheet.
  5. The control should now appear in your toolbox.

To add the spreadsheet control to a form:

  1. Select the control in the toolbox.
  2. On the form, drag the mouse to lay out a rectangle where you want the form to appear.
Spreadsheet Property Toolbox image
Customizing the Control

As with most controls, you can resize the spreadsheet.

You can also copy cells from an existing Excel worksheet into the spreadsheet control.

However, some of the most important customizations take place on the property toolbox for this control. To display the property toolbox:

  1. Click twice (not a double-click, but two separate clicks) on the control to select it with a dark border.
  2. Click the Property Toolbox button .
  3. Make your choices on the various sections of the property toolbox. For example, under the Show/Hide section, you might want to turn off the title bar, toolbar, and column and row headers. Under Protection, you might want to disable the user's ability to access the property tool box when the form is running. 
  4. To format cells, select the cells, then make your choices on the Formatting section of the property toolbox.

Another way to customize the toolbox is by writing code in the form events using standard Excel Spreadsheet properties and methods. This code fragment, for example, might be included in the Item_Open event.

The above code makes the following changes on the spreadsheet:

  • Limits the range of cells that users can scroll across, so that they always stay in the area the developer wants
  • Adds a formula to column F to multiply the values in columns C and E
  • Sets up protection on the spreadsheet so that the user cannot type into the cells in column F, cannot resize columns, etc.
Handling Data

You cannot bind the spreadsheet control to an Outlook property. This means that Outlook will not automatically save the data that users enter into the control. Therefore, you must provide a way to save the data and restore it when the user reopens the form. You can use the HTMLData property for this. The following code is for a form where the Message or Notes control (which displays the Body property) has been removed. It saves the data to the item's Body property (which is not visible to the user because there is no control to display it) when the user saves the form and restores it when the user opens the form:

Setting Item.Subject equal to itself ensures that users will get a "Save changes?" prompt when they close the form. (Since the spreadsheet control is not bound to a property, changing the data in the spreadsheet does not "dirty" the form.)

If you need to use the Body property for an actual message, then you'll need to use some other property to store the spreadsheet data. All Outlook items contain built-in BillingInformation and Mileage properties, or you can add your own custom text property. However, the amount of data that can be stored in standard and custom properties, other than the item body and attachments, is limited to 32kb.

Limitations

The spreadsheet control does not fire a Click event (or any other event) when used on an Outlook form.

You cannot place another control in front of the spreadsheet control on an Outlook form.

More Information

Another way to get spreadsheet data into an Outlook item is to copy and paste from an Excel worksheet into an Outlook message that is using Word as the email editor.