Automating Chart Importation from Excel to an Email Message in Outlook 2010
Summary: Learn how to automate copying a chart from Microsoft Excel 2010 into an email message in Microsoft Outlook 2010.
Last modified: September 12, 2012
Applies to: Excel 2010 | Office 2010 | Outlook 2010 | Visual Studio | Word 2010
Published: June 2011
Provided by: Angela Chu-Hatoun, Microsoft Corporation
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:
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
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.
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"> <ribbon> <tabs> <tab id="MyTab" getVisible="MyTabInspector_GetVisible" label="MyTab"> <group label="MyGroup" id="MyGroup" autoScale="true"> <button id="MyButton" size="large" label="Copy Excel Chart" imageMso="HappyFace" onAction="OnMyButtonClick"/> </group> </tab> </tabs> </ribbon> </customUI>
There are two points of interest in the XML:
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:
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(XlPictureAppearance, XlCopyPictureFormat, XlPictureAppearance) 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:
For more information about the techniques discussed in this Visual How To, see the Explore It section.