An advantage of using Microsoft Office is the ability to automate a business process that involves multiple Office applications. The core scenario in this Visual How To is to automate copying a chart from Microsoft Excel to an email in Microsoft Outlook. This scenario is common in business workflows, such as producing a weekly status report email that imports charts from Excel workbooks. This Visual How To shows how to use an Outlook add-in to access the Microsoft Excel and Word object models, to automate opening an Excel workbook, copying a chart from the workbook, and pasting the chart into an Outlook email message.
This Visual How To includes a C# Outlook 2010 add-in written in Microsoft Visual Studio 2010. The add-in provides a custom user interface in Outlook for you to select and copy a chart from an Excel workbook to an email message in compose mode. This Visual How To assumes you are familiar with C# and creating add-ins for Outlook.
The Outlook add-in solution is named ImportExcelChartToOutlook_AddIn_CS. The solution contains the following noteworthy classes:
ThisAddIn—Initializes the add-in, declares instance variables, and adds a custom event handler for a new inspector. Also cleans up instance variables for the add-in upon shutdown.
Ribbon1—Provides a custom ribbon for mail inspectors in compose mode. Specifies the ribbon XML in Ribbon1.xml, and implements various methods to load the custom ribbon and display it in the appropriate context. Also implements the button callback method to open a workbook and copy a chart to a message.
This add-in assumes that there is a chart on the first worksheet of the Excel workbook you specify. Complete the following procedure to run this add-in.
To run the add-in
Close Outlook if it is already running.
In Visual Studio 2010, open and run the ImportExcelChartToOutlook_AddIn_CS solution by clicking Start Without Debugging. This starts Outlook.
In Outlook, create a new email message.
On the custom ribbon tab, MyTab, click Copy Excel Chart.
In the Open Workbook dialog box, specify a workbook that has either an .xlsx or .xls extension.
If the workbook is already open, the add-in opens the workbook as read-only. The add-in assumes there is at least one chart on the first worksheet, and copies the first chart to the email message in compose mode. Note that the add-in leaves the workbook open, so that you can decide to save changes that you may have made before closing it.
Exposing a Custom Ribbon
On the mail inspector ribbon, the add-in creates a custom tab that has a custom button, as shown in Figure 1, that enables the user to copy a chart.
Figure 1. Custom ribbon for a mail message in compose mode
Programmatically, the add-in specifies the custom user interface in XML, as supported by the Microsoft Office Fluent XML schema, in Ribbon1.xml. The following example shows the XML.
<?xml version="1.0" encoding="UTF-8"?>
<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="Ribbon_Load">
<group label="MyGroup" id="MyGroup" autoScale="true">
label="Copy Excel Chart"
There are two points of interest in the XML:
Assigning the callback method
MyTabInspector_GetVisible to the getVisible attribute of the custom tab. This callback method determines when the "MyTab" tab control should be visible.
Assigning the callback method
OnMyButtonClick to the onAction attribute of the custom button.
These two callback methods are described in more detail later in this Visual How To.
To display the custom tab only in the mail inspector in compose mode, the add-in does the following:
Implements the GetCustomUI method so that Outlook loads the XML for the custom ribbon only when the context is the mail inspector in compose mode. Identifying the context uses a ribbonID input parameter. When the ribbonID matches the value "Microsoft.Outlook.Mail.Compose", the parameter indicates to Outlook that it should load the custom ribbon in Ribbon1.xml. For more information about Outlook ribbon identifiers that the GetCustomUI method supports, see the section Ribbon Identifiers in Extending the User Interface in Outlook 2010.
MyTabInspector_GetVisible callback method so that the custom tab and custom button are indeed displayed only if the current window is a mail inspector in compose mode. To confirm these conditions, the add-in checks the following:
Whether the Context property of the tab control, which represents the current window, is an Outlook Inspector object.
Whether the item in the current inspector is a MailItem object.
Whether the Sent property of the MailItem object is false, which indicates that the mail message is being composed and has not yet been sent.
Implements a custom event handler,
m_Inspectors_NewInspector, to refresh the inspector ribbon before a new inspector is opened. In turn,
m_Inspectors_NewInspector calls the Invalidate method to ensure that the custom ribbon is displayed only when an inspector is opened to compose an email message.
Opening an Excel Workbook
One of the first things that this add-in does in the custom button click callback method,
OnMyButtonClick, is to check whether Excel is already running on the client computer. If Excel is already running, the add-in uses that Excel process, and calls the GetActiveObject method to obtain an Excel Application object. Otherwise, the add-in creates a new instance and starts Excel. To ensure that Excel is displayed, the add-in sets the Visible property to true.
Copying an Excel Chart
Continuing in the
OnMyButtonClick method, the add-in then prompts the user to select a workbook that contains a chart and opens the workbook. For simplicity, the add-in assumes there is at least one chart on the first worksheet. The add-in uses the CopyPicture method in the Excel object model to copy the chart to the Clipboard.
Using Word to Paste the Chart into an Email Message
Since Outlook 2007, composing an email message in Outlook uses Word as the only mail editor. Programmatically, the message that is displayed in compose mode in the mail inspector is represented by the Document object of the Word object model. Continuing in the
OnMyButtonClick method, the add-in uses the WordEditor property in the Outlook object model to access this Document object. Through the Document object, the add-in can obtain a Range object that supports further manipulation of content in the document, including pasting an object. Lastly, the add-in uses the Paste method to paste the chart from the Clipboard into the email message.
This Visual How To shows the core scenario of automating chart importation from Excel to Outlook. The accompanying add-in implements a custom button in the ribbon to initiate the opening of a user-specified Excel workbook, and copying of a chart from that workbook to an email message in compose mode in Outlook. The following list shows a few aspects that can be further automated to extend this common core business scenario:
Instead of implementing a custom button in the mail inspector in compose mode, the add-in can implement a custom button in the mail explorer. This custom button can initiate the business process by first creating a new email message in the inspector, followed by opening Excel and importing the chart.
This Visual How To assumes that the user-specified Excel workbook has the chart that is ready to be copied to Outlook. Alternatively, using the Excel object model, the add-in can first connect to a data source, update the data in the workbook, and update the chart, before copying the chart to Outlook. Filtering Data Programmatically in Excel 2007 by Using PivotTables and Creating a PivotTable Programmatically show how to connect to an Online Analytical Processing (OLAP) and SQL data source, respectively, pass the connection string to a PivotCache object, create a PivotTable, and apply appropriate filtering.
To create a PivotChart from a PivotTable, you can do the following:
Access the worksheet where the PivotTable is located.
Use the Cells property of the Worksheet object and the Select method of the Excel Range object to select a cell in the PivotTable.
Use the Shapes property of the Worksheet object and the AddChart method of the Shapes object to create a chart on the same worksheet as the PivotTable.
Use the SetSourceData method of the Chart object to set the chart’s source of data to be the same as the range of the PivotTable.
Use the ChartType property of the Chart object to set an appropriate type for the chart.
Subsequently when you want to update the data and the PivotChart, you can use the MakeConnection and Refresh methods of the PivotCache object. You can then copy the chart and paste it in an Outlook email message, as shown in the core scenario of this Visual How To.
The add-in of this Visual How To pastes a chart at the beginning of the email message. By using the Start and End properties of the Word Range object, you can more precisely specify where to copy each chart into the message.
You can use the Word object model to further automate the creation and formatting of content in the Outlook email message in compose mode.
For more information about the techniques discussed in this Visual How To, see the Explore It section.
Watch the video
Grab the Code