devxlogo

Write a Custom Outlook Add-In with Visual Studio Tools for Office

Write a Custom Outlook Add-In with Visual Studio Tools for Office

isual Studio Tools for Office (VSTO) is an add-on to Visual Studio 2005 that gives developers much more direct IDE support and easier access to the rich programming model provided by the Microsoft Office platform than did previous versions of Visual Studio. VSTO simplifies building applications that use Office’s functionality and building add-ins to existing Office applications, such as Outlook or Excel. The ubiquity of the Office platform offers swathes of powerful functionality already on your users’ desktops; so you can take advantage of it by reusing the components.

Consider, for example an alerting application. Many companies buy or build alerting applications that send alerts to their users when conditions meet particular criteria?likely through a custom channel to a custom application. If the alert doesn’t require an immediate response, users may simply put a notification in their Outlook calendars to handle the alert at a future point. So, for example, when the stock market closes, you may want to be alerted if the stocks you are tracking close above or below your target threshold values. The alert would pop up on your desktop and you would cut and paste the valuable information into an Outlook calendar entry telling you to call your broker before the market opens the following day. After creating the calendar entry, Outlook will throw up a second alert notification the next morning. But when you think about it, that’s the only time that you really wanted to be alerted all along, but to get that alert, you had to deal with two alerts for the same event!

Wouldn’t it be nice if you didn’t need a custom alerting platform to do this? And wouldn’t it be even nicer if you didn’t have to manually move the metadata associated with the alert into an Outlook calendar item?

This article shows you how to do both using Visual Studio, Office, and VSTO. You’ll build an add-in to Outlook that calls a Web service to check stock prices, and compares them against your list of targets (stored in a SQL Server Database). If the price is outside preset thresholds, the add-in creates a calendar entry in Outlook for 8:30 AM on the following day, containing the metadata for the stocks that have fallen outside your desired thresholds. In other words, this application replaces the custom alerting platform (Outlook calendar alerts will handle it for you), you will have a reduced set of alerts (one instead of two), and you don’t have to cut and paste to get the alert at the right time.

?
Figure 1. New Project Templates: After installing VSTO, when you create a new project and select the “Office” project type, you’ll see several new project templates.

Getting Started
You’ll find the VSTO home page, where you can download a trial edition if you don’t already have VSTO installed. When installing VSTO, you’ll need to have your Office install disks handy, because you’ll need the primary interop assemblies for Office, which are on the Office disks.

After installing VSTO and launch Visual Studio 2005, you’ll see several new project templates when you issue a File->New Project command and select “Office” as the project type. See Figure 1.

The available templates allow you to programmatically create a new Excel workbook or Word document, generate a new Excel or Word Template, or create a new Outlook add-in.

In this case, you’ll be building an Outlook Add-in so select that template.

Visual Studio will now create a solution containing two projects, one for the Add-in, named OutlookAddIn1 by default, and one for the setup project that installs the add-in on client machines, called OutlookAddIn1Setup by default.

Get Data from a Web Service
In the Add-in project, you need to add a Web reference to a Web service that will provide the stock quotes to the application.

To do this, right click on the ‘References’ node in the solution and select ‘Add Web Reference’. In the ensuing dialog, use the following WSDL declaration:

  http://services.xmethods.net/soap/      urn:xmethods-delayed-quotes.wsdl

Name the service quoteservice. Visual Studio will generate a proxy to this Web service for you.

Next you’ll need a database that contains your portfolio of stocks that the Web service will use to check closing prices. If you are using SQL Server here’s a generate script that you can use to create an appropriate database:

   USE [Portfolio]   GO   /****** Object:  Table [dbo].[stocktriggers]    Script Date: 06/19/2006 06:51:34 ******/   SET ANSI_NULLS ON   GO   SET QUOTED_IDENTIFIER ON   GO   CREATE TABLE [dbo].[stocktriggers](      [id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,      [stockticker] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,      [hightrigger] [decimal](18, 0) NULL,      [lowtrigger] [decimal](18, 0) NULL   ) ON [PRIMARY]

The preceding script creates a simple database table containing three fields where you can enter a stock ticker and your high and low triggers. So, for example, MSFT at time of writing was quoted at 22.20, so you might set a low trigger at 15 and a high trigger at 30. For this application, if MSFT falls below 15 or rises above 30, Outlook will set an appointment alert in your calendar to call your broker, because you may want to buy low or sell high.

When you’ve set up the database and entered some stock ticker symbols and triggers, you’re ready to start programming.

Building the Application
The first step is to add a new DataSet to your application and connect this to the table you created in the previous section. Of course, you don’t have to use SQL Server; you could, for example, use an XML file. However, the code in this section is based on using a database, and using a Dataset object to connect to it.

Your new Outlook add-in project will contain a class called “ThisApplication” by default, and it will have two event handlers set up by default: ThisApplication_Startup and ThisApplication_Shutdown. You’ll be coding in the ThisApplication_Startup event handler.

First, you’ll need to connect to the data set using the TableAdapter that Visual Studio created when you added the DataSet to the designer:

   folioTableAdapters.stocktriggersTableAdapter da = new      folioTableAdapters.stocktriggersTableAdapter();   folio.stocktriggersDataTable dt = da.GetData();

At this point, you have a DataTable object that you can iterate through to get your data. I’ll get back to that in a moment. Before you get to that point, you’ll need to create an instance of the Web service proxy:

   quoteservice.netxmethodsservicesstockquoteStockQuoteService       quote = new quoteservice.      netxmethodsservicesstockquoteStockQuoteService();

Next, you need to iterate through the records in the DataTable, getting the current quote for each stock symbol. If the current quote is above or below the trigger criteria, you will add text to a StringBuilder that will form the body of your calendar entry.

   foreach(folio.stocktriggersRow theRow in dt.Rows)   {     float q = quote.getQuote(theRow.stockticker.Trim());     Decimal d = (Decimal)q;     if (d > theRow.hightrigger)     {       strMessage.Append(theRow.stockticker.Trim());       strMessage.Append(" has crossed over your high " +           "trigger for it.");       ...     }     if (d 

Author's Note: The string aggregation has been considerably abridged in the preceding code, but you can see the full listing in the downloadable code for this article.

A DataTable object contains a collection of rows. The preceding code iterates through the Rows collection, assigning a folio.stocktriggersRow object to the current row. That object has public members corresponding to the columns in the database. So, to get the stock ticker for the current row you just use:

   theRow.stockticker

The string you get will be padded with spaces, so before you call the Web service you'll need to remove this using the Trim() method. You retrieve the hightrigger and lowtrigger values for the current row in a similar manner. If the current quote falls outside either of these values, the code appends text to the strMessage StringBuilder instance.

Creating Outlook Objects
After you complete the loop through the table, if the trigger conditions were exceeded, the strMessage StringBuilder will not be empty, in which case, you'll want to add a new calendar item to Outlook for the following day at 8:30 AM.

Here's the code that does this:

   if (strMessage.Length > 0)   {      Outlook.AppointmentItem ai = (Outlook.AppointmentItem)         this.CreateItem(Microsoft.Office.Interop.Outlook.         OlItemType.olAppointmentItem);      ai.Subject = "STOCK ALERT: CALL BROKER";      ai.Body = strMessage.ToString();         DateTime dDay = DateTime.Now.AddDays(1).Date;      DateTime dCalEntry =          new DateTime(dDay.Year, dDay.Month, dDay.Day,          8, 30, 0);      ai.Start = dCalEntry;      ai.Duration = 15;      ai.Display(false);      ai.Close(Microsoft.Office.Interop.Outlook.         OlInspectorClose.olSave);   } 

When using an Outlook add-in project type, you can use the this.CreateItem method to create Outlook objects. VSTO IntelliSense allows you to specify the constructor that defines the type to use. In this case you are creating an appointment item, and thus use the olAppointmentItem to construct it. You also have to do the appropriate cast to an Outlook.AppointmentItem to get access to the relevant methods and properties at design time.

You then set the Subject property of the appointment item to an appropriate subject, and the Body to the string you built earlier during the loop through the table.

To set up the appointment for a fixed time one day hence, you can use the AddDays function on the date returned by the DateTime.Now method to get the appropriate day as a DateTime object.

   DateTime dDay = DateTime.Now.AddDays(1).Date;

Then you create a new DateTime object with the year, month and day values for that date, and fix the hours, minutes and seconds to the specific time that you want.

   DateTime dCalEntry = new DateTime(      dDay.Year, dDay.Month, dDay.Day, 8, 30, 0);

You can then assign this DateTime as the start point for your appointment. The Duration property allows you to specify the appointment duration (in minutes); in this case the code above sets it to 15 minutes.

You can choose to display the appointment using the Display method, but here it's set to false, because no user interaction is required to put the appointment in your calendar. Finally the code calls the AppointmentItem.Close method, setting the close type argument to the olSave constant, which closes and saves the appointment to your calendar.

Now, whenever you launch Outlook, the add-in checks your stocks and if necessary, sets up an appointment to call your broker in your Outlook Calendar. You can see this in Figure 2, which shows the day view, and Figure 3, which shows the appointment details, including the metadata around the event itself, which you'll need when you call your broker.

?
Figure 2. Outlook Calendar with Appointment Added: Here's an appointment added automatically by the new Outlook add-in.
?
Figure 3. Appointment Details: The new appointment contains specific details about the alert, set by the add-in when it created the appointment.

In this article you built a prototype for a real-world scenario that demonstrates the power of the Office platform for building and running applications. It makes far more sense to capitalize on the untapped power of Office already on many users' desktops instead of replicating it in your own applications. As an example, you built an alerting system using the Visual Studio Tools for Office for non critical alerts (i.e. ones that do not need to be immediately addressed). By using the Outlook's calendar's "Appointment Item" functionality, you were able to reduce the amount of work a user needs to do to process an alert.

Of course, you've barely scratched the surface of what Office and VSTO can do, but as you can see, they put very powerful tools at your disposal. The only limit is your imagination!

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist