In this short article, I will discuss a simple operation that requires using the ActiveX control Microsoft Office Outlook View Control. With this control, we can read, reply, deleted, etc, emails in Excel straight from Outlook.

The first step requires you creating your new project as follows:

1.    File --> New Project
2.    Project Types --> Office 2007
3.    Excel 2007 Workbook

With that created, add an Actions Pane control to the project as follows:

1.  Project menu
2.  Add User Control
3. Actions Pane Control

Change the name of the Actions Pane Control to clsActionsPane.

With that done, right-click somewhere in the VSTO Toolbox and from the popup menu you should select Choose Items… as shown in the image below:

Figure 1: Opening the “Choose Toolbox Items” dialog box

A new window will open where you will be able to select Microsoft Office Outlook View Control from the list.

Figure 2: “Choose Toolbox Items” dialog box

Once you have selected the Microsoft Office Outlook View Control, click OK to continue. The references will be added as well as the control will now be visible in the VSTO Toolbox as shown in the image that follows:

Figure 3: Microsoft Office Outlook View Control

Now, add three control buttons to the Actions Pane as well as the Microsoft Office Outlook View Control. Your project should now look like this:

Figure 4: Actions Pane setup

The command buttons should be named as follows:

1.  btnSentItems
2.  btnInbox
3.  btnDeletedItems

As for the label (text) of the button, use the name for the respective folders in Outlook (as viewed in the previous image).

we are now ready to add the code. First, we will add code to ThisWorkbook class so that we can add the Actions Pane. This is done as follows:

Public Class ThisWorkbook
    Private oActionPane As New clsActionsPane
    Private Sub ThisWorkbook_Startup( _
                    ByVal sender As Object, _
                    ByVal e As System.EventArgs) _
                    Handles Me.Startup
        Me.ActionsPane.Controls.Add(oActionPane)
    End Sub
End Class

Next, add the code to the Actions Pane classe:

Public Class clsActionsPane
    'Change view to "Sent Items"
    Private Sub btnSentItems_Click(ByVal sender _
                                   As System.Object, ByVal e _
                                   As System.EventArgs) _
                                   Handles btnSentItems.Click
        Me.clsViewControl.Folder = Me.btnSentItems.Text
    End Sub

    'Change view to "Inbox"
    Private Sub btnInbox_Click(ByVal sender _
                                   As System.Object, ByVal e _
                                   As System.EventArgs) _
                                   Handles btnInbox.Click
        Me.clsViewControl.Folder = Me.btnInbox.Text
    End Sub

    'Change view to "Deleted Items"
    Private Sub btnDeletedItems_Click(ByVal sender _
                                      As System.Object, ByVal e _
                                      As System.EventArgs) Handles _
                                      btnDeletedItems.Click
        Me.clsViewControl.Folder = Me.btnDeletedItems.Text
    End Sub
End Class

You can now execute your project. Once loaded, you can use the buttons to change the current folder. Right-clicking on an item in any of the boxes will give you the same option you’d get in Outlook’s own window:

Figure 5: Compiled Actions Pane containing the Microsoft Office Outlook View Control

First of all, what is a Smart Tag? “Smart Tags” is a feature of Microsoft Office that recognizes certain kind of text (such as a date) and then offer the users a list of possible actions related to that text.

Excel already comes with a plethora of Smart Tags, but we can also implement our own list of Smart Tags. When Smart Tag is active, when you hover the mouse cursor over the word a little button is displayed. Clicking on it will open the Smart Tag Actions.

You can turn on/off Smart Tags at:

1.    Office Button
2.    Excel Options
3.    Proofing à AutoCorrect Options
4.    Smart Tags

The image below shows the Smart Tag in action when Microsoft’s financial symbol is typed in a cell:


Figure 1: Smart Tag at work

Let us start by creating a new Excel project as follows: 

1.    File à New Project
2.    Project Types à Office 2007
3.    Excel 2007 Workbook (or Add-in, if you prefer an Excel Add-in)

The first thing we will do is to check whether Smart Tags are active in Excel. You should add the following code to the workbook’s Startup event (notice that below I also add the event for the click on the Smart Tag button we will create later. The code must come after Thisworkbook class):

    WithEvents MySmartTagClickAction As _
                       Microsoft.Office.Tools.Excel.Action

    Private Sub ThisWorkbook_Startup(ByVal sender As Object, _
                                     ByVal e As System.EventArgs) _
                                     Handles Me.Startup
        If Application.AutoCorrect.DisplayAutoCorrectOptions = False Then
            Application.AutoCorrect.DisplayAutoCorrectOptions = True
            Me.SmartTagOptions.DisplaySmartTags = _
                Excel.XlSmartTagDisplayMode.xlIndicatorAndButton
            MsgBox("Smart Tags have been enabled...", _
                   MsgBoxStyle.Information, "Smart Tags Enabled...")
        End If
        BuildSmartTags()
    End Sub

When the code is run, if Smart Tags are disabled, they will be enabled and a message will be shown:

Figure 2: Enabling Smart Tags via code

Now create a Smart Tag list in one of your worksheets. You can later change its visibility and hide it away from the user (Home Tab à Cells à Format à Hide & Unhide à Hide Sheet):


Figure 3: Smart Tags list

As you will remember from the Startup code, we had a call to a procedure named BuildSmartTags. This is our next objective:

    Sub BuildSmartTags()
        'Smart Tag object. Define the namespace and the identifier.
        'Here I use the namespace as my web address and identifier as
        'My Smart Tag
        Dim oMySmartTags As New  _
            Microsoft.Office.Tools.Excel.SmartTag( _
            "www.msofficegurus.com/MySmartTags#SmartTags", _
            "My Smart Tag")

        'Worksheet object that holds the list of Smart Tags
        Dim oSmartTagListWorksheet As Excel.Worksheet

        'Counter to loop through Smart Tag list
        Dim lngCount As Long

        'Smart Tag string stored in the worksheet
        'to be added as the Smart Tag Term
        Dim strSmartTagTerm As String

        'Set initial counter equal to 1
        lngCount = 1

        'Set worksheet that contains the Smart Tag list
        oSmartTagListWorksheet = Me.Sheets("Smart Tags List")

        'Set the first Smart Tag term
        strSmartTagTerm = _
            oSmartTagListWorksheet.Range("A" & lngCount).Value

        'Loop through Smart Tag Term List
        Do Until Len(strSmartTagTerm) = 0
            oMySmartTags.Terms.Add(strSmartTagTerm)
            lngCount = lngCount + 1
            strSmartTagTerm = _
              oSmartTagListWorksheet.Range("A" & lngCount).Value
        Loop

        'New Action button to be added to Smart Tag Action list
        'The name in parentheses is the name of the button
        MySmartTagClickAction = New  _
            Microsoft.Office.Tools.Excel.Action( _
            "Go to MS Office Gurus online")

        'Associate the button above with the
        'Smart Tag object (oMySmartTags)
        'that we declared above
        oMySmartTags.Actions = New  _
            Microsoft.Office.Tools.Excel.Action() _
               {MySmartTagClickAction}

        'Add the Smart Tag object (oMySmartTags)
        'the VstoSmartTags collection
        Me.VstoSmartTags.Add(oMySmartTags)
    End Sub

The final piece of the jigsaw is the code to handle the click event:

    'Event that handles the action click
    Private Sub MySmartTagClickAction_Click( _
        ByVal sender As Object, _
        ByVal e As Microsoft.Office.Tools.Excel.ActionEventArgs) _
        Handles MySmartTagClickAction.Click

        Me.FollowHyperlink("http://www.msofficegurus.com", _
           , True, True)
    End Sub

You can now try the code out and see who it works as you type any word from your list:


Figure 4: Custom Smart Tag in action

Picture 1: Creating a new VSTO 2008 Excel Add-in project

With the projectcreated, we will add the following code to it:

Public Class ThisAddIn
    'Events tocontrol the commandbar button click event
    PrivateWithEvents oBtnHandleOrderTabs As Office.CommandBarButton
    PrivateWithEvents oBtnHandleCellDetails As Office.CommandBarButton

    'Commandbarcontrols used in the code
    PrivateoBtn As Office.CommandBarButton
    DimoCmdBarPopup As Office.CommandBar
    DimoEditCtl As Office.CommandBarControl

    'Stringsused in this code
    PrivateConst strconPOPUP_NAME AsString = "INFO_POPUP"
    PrivateConst strconBtn_POPUP_CAPTION As String = "ABOUT THE ACTIVE CELL"
    PrivateConst strconCmdBar_Cell_NAME As String = "Cell"
    PrivateConst strconCmdBar_Ply_NAME As String = "Ply"
    PrivateConst strconBtn_Ply_CAPTION As String = "Order Tabs"
    PrivateConst strconBtn_Cell_CAPTION As String = "View Cell Details"

    PrivateSub ThisAddIn_Startup(ByValsender As Object,_
                                  ByVal e AsSystem.EventArgs) Handles Me.Startup

        Try
            'Uponstarting up this Add-in, try:
            'Controlsare temporary; hence delete/reset is just a "fail-safe"
           Application.CommandBars(strconPOPUP_NAME).Delete()
           Application.CommandBars(strconCmdBar_Cell_NAME).Reset()
           Application.CommandBars(strconCmdBar_Ply_NAME).Reset()
        Catch ex As Exception
            'Catchexception and show to user
            MsgBox(ex.Message)
        EndTry
        'Callthe sub-routine to create menu items
        CallCreateMenu()
    End Sub

    PrivateSub ThisAddIn_Shutdown(ByValsender As Object,_
                                   ByVal e AsSystem.EventArgs) Handles Me.Shutdown
        'Nocode added here
    End Sub

    SubCreateMenu()
        'Author         : Robert Martim
        'Purpose        : Create menu items
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009       

        'Add atemporary button to the Ply menu
        oBtn =Application.CommandBars(strconCmdBar_Ply_NAME) _
           .Controls.Add(Office.MsoControlType.msoControlButton, , , 1, True)

        'Definea few properties for the button
        WithoBtn
            .Caption =strconBtn_Ply_CAPTION

            .FaceId =210

            .Style =Office.MsoButtonStyle.msoButtonIconAndCaption
        EndWith

        'Setthe button so that its click event can be controlled
        oBtnHandleOrderTabs = oBtn

        'Add atemporary button to the Cell menu
        oBtn =Application.CommandBars(strconCmdBar_Cell_NAME) _
         .Controls.Add(Office.MsoControlType.msoControlButton, , , 1, True)

        'Definea few properties for the button
        WithoBtn
            .Caption =strconBtn_Cell_CAPTION
            .FaceId = 326
            .Style =Office.MsoButtonStyle.msoButtonIconAndCaption
        EndWith

        'Setthe button so that its click event can be controlled
        oBtnHandleCellDetails =oBtn

        '************************************************************
        'Adda popup menu to the commandbars collection
        oCmdBarPopup = Application.CommandBars.Add(_
          Name:=strconPOPUP_NAME,Position:=Office.MsoBarPosition.msoBarPopup)

        'Adda fixed button to work as a header label of the popup menu
        oBtn =oCmdBarPopup.Controls.Add( _
          Type:=Office.MsoControlType.msoControlButton)
        WithoBtn
            .Caption =strconBtn_POPUP_CAPTION
            .Width = 40
            .Enabled = False
        EndWith

        'Populatethe popup with 4 Edit controls
        ForI = 1 To 4
            oEditCtl = oCmdBarPopup.Controls.Add(_
             Type:=Office.MsoControlType.msoControlEdit)
            oEditCtl.Width = 200
        NextI

        'Setsome properties for the popup
        WithoCmdBarPopup
            .Width = 200
            .Protection =Microsoft.Office.Core.MsoBarProtection.msoBarNoChangeDock + _
             Microsoft.Office.Core.MsoBarProtection.msoBarNoCustomize + _
             Microsoft.Office.Core.MsoBarProtection.msoBarNoResize
        EndWith

    EndSub

    SubInfo()
        'Author         : Robert Martim
        'Purpose        : Sub to fill Edit control with celldetails
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009  

        DimoActiveCell As Excel.Range

        Try
            'Try:
            'to fill Edit control with cell details
            oActiveCell =Application.ActiveCell
            WithApplication.CommandBars(strconPOPUP_NAME).Controls
                If oActiveCell.Formula = ""Then
                    .Item(2).Text= "This cell has no formula"
                ElseIf oActiveCell.Locked = TrueThen
                    .Item(2).Text= "Formula: " &oActiveCell.Formula
                End If
                .Item(3).Text = "Format: " & oActiveCell.NumberFormat
                .Item(4).Text = "Font name: " &oActiveCell.Font.Name
                .Item(5).Text = "Total of CFs on active cell: " & _
                   oActiveCell.FormatConditions.Count
            End With
        Catchex As Exception
            'Catch exception and show to user
            MsgBox(ex.Message)
        EndTry
        oActiveCell = Nothing
    EndSub


    SubOrderTabs()
        'Author         : Robert Martim
        'Purpose        : Order active workbook sheets
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009  

        DimoWorkbook As Excel.Workbook
        DimiCount1 As Integer
        DimiCount2 As Integer

        Try
            oWorkbook =Application.ActiveWorkbook

            For iCount1 = 2 TooWorkbook.Sheets.Count Step 1
                For iCount2 = 1 ToiCount1 Step 1
                    If oWorkbook.Sheets(iCount2).Name >oWorkbook.Sheets(iCount1).Name Then
                       oWorkbook.Sheets(iCount1).Move(Before:=oWorkbook.Sheets(iCount2))
                    End If
                Next iCount2
            Next iCount1

        Catchex As Exception
            '     MsgBox(ex.Message)
        EndTry

        oWorkbook = Nothing

    End Sub

    Private SuboBtnHandleOrderTabs_Click(ByVal Ctrl As  _
                                         Microsoft.Office.Core.CommandBarButton, _
                                         ByRef CancelDefault As Boolean) _
                                         Handles oBtnHandleOrderTabs.Click
        'Author         : Robert Martim
        'Purpose        : Handles click event on the"Order Tabs" button
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009 

        CallOrderTabs()
    End Sub

    PrivateSub oBtnHandleCellDetails_Click(ByVal Ctrl _
                                           AsMicrosoft.Office.Core.CommandBarButton, _
                                           ByRef CancelDefault As Boolean) _
                                           Handles oBtnHandleCellDetails.Click
        'Author         : Robert Martim
        'Purpose        : Handles click event on the "CellDetails" button
        'Createdon     : 18 April 2009
        'Lastupdated   : 18 April 2009  
        CallInfo()
       Application.CommandBars(strconPOPUP_NAME).ShowPopup()

    End Sub
End Class

Upon right-clicking on any Excel worksheet, the Ply popup menu will be shown,only this time it will have an extra Commandbar button added to it. You can nowclick on this Commandbar button to order the tabs alphabetically:
 

Picture 2: Order Tabs commandbar button

Picture 3: View Cell Details commandbar button
Finally, when we click onthis button our custom Commandbar popup is shown along with the cell’sinformation as viewed in the picture below: 
Picture 4: Cell Details commandbar popup menu

Technologies used
- .NET
- MS-Excel 2003
- VSTO

MS-Excel has been, without a doubt, the best spreadsheet in the market for years. All the available resources within Excel are key in areas such as controlling, finance, accounting, logistics, etc. The integration of Excel with a database has become a functionality which is crucial to manipulate data in a worksheet (Front-End) where the data source can be SQL Server, Access or any other data source available. 

With .NET, the developers’ market has entered a new world of developing languages and developing tools (VS.NET 2003). For those who use Excel, programming in VBA (Visual Basic for Application) will continue to be a valid choice.

If we put the development power of .NET together with Excel what we get is VSTO (Visual Studio Tools for the Microsoft Office System) which is a tool that you need to install separately in VS.NET 2003 (currently on version 1.0)

Nevertheless, in this article I will show you a bit of what can be done with Excel and VSTO 2.0. Version 2.0 can only be installed using VS.NET 2005 and it is worth noting that these are beta versions, at least up to when this article was last updated (Sept/2005). If you have worked with VSTO 1.0, believe me, version 2.0 is way better and easier to develop with.

To get started, open VS.NET 2005 and create a new project with the following characteristics:

Project Types: Visual C# / Office
Templates: Excel Application
Name: ExcelMSDN
Location: C:\MSDN
Solution Name: ExcelMSDN

Refer to pictures below:

 
Picture 1: New Project window

Choose the solution as shown in the picture above. Next, click on OK to define the document name. We can either define a new document or copy an existing document. For this article, we will create a new document:

 
Picture 2: Select a document window

Save your application as follows:

 
Picture 3: Save Project window

Click on the Save button to save your new solution. You should note that the UI (User Interface) is actually Excel’s UI (which includes toolbars, sheets, etc) within VS.NET 2005:

 
Picture 4: VSTO 2.0 with Excel's UI

Now, under the Solution Explorer you should add a UserControl to the project as shown in the picture below:

 
Picture 5: Add New Item window

You now also add the following controls to the project-level control you’ve just added:

Picture 6: Design view of UserControl1

In the Toolbox, drag a bindingSource control to the document and configure the DataSource Property accordingly. At this stage there is no data source; thus, it is necessary that we add one to the project. Click on the Add Project Data Source link and configure the data source. I use the Northwind database as the data source for the project; therefore, you should use the same database and choose the tables Categories and Products.

See picture below:

Picture 7: Adding a data source to the Project

In same instances it is possible to define the Master Details. Go back to the worksheet and drag the controls from the Toolbox onto the active sheet (Sheet1) as shown in the next picture. Note that the controls are embedded in the worksheet (look at the formula bar). Now, you should configure the properties of the controls according to the information in the bindingSource control.

Remember that you do not need to write any code to bind the data to the controls. All you need to do is to configure the controls’ properties.
 

 
Picture 8: Adding controls to Sheet1

We are now ready to add a few lines of code to our project. Change ThisWorkbook view to code mode. By adding a few lines of code we can show the UserControl1 in Excel’s Task Pane (referred to within VS.NET as ActionsPane):

private void ThisWorkbook_Startup(object sender, System.EventArgs e)
      {
            this.ActionsPane.ForeColor = Color.White;
            this.ActionsPane.BackColor = Color.Tomato;
            this.ActionsPane.Controls.Add(new UserControl1());
      }

In order to navigate the Internet using the webBrowser control that we added to Sheet1, we will add a bid of code to the “GO” button:

private void btnGo_Click(object sender, EventArgs e)
     {
        WebBrowser1.Navigate(this.txtURL.Text);
     }

Finally, save your project and press (F5) to execute it. Note that the UserControl1 is shown in the Task Pane (ActionsPane) with the data from the Northwind database. On the worksheet there is also a GridView control that shows the same data shown in the Task Pane. The same goes for the listboxes on the worksheet and Task Pane.

Another new feature is the WebControl which allows you to browse the web within and Excel worksheet:

Picture 9: Up and running project

Conclusion
Excel using VSTO 2.0 will not be the same. All the easy-to-use tools within .NET given to solution developers are reusable within VSTO. What needs to be had in mind is that Excel will become a “Smart Client”, though it has always been one. Thus, it won’t be with VSTO that it will cease being one. It is worth your while to delve deeper into the tools available in the VSTO+VS.NET world and what they will give you.

I wish you good luck on your studies and remember: No Stress, think .NET + VSTO!

References:
www.microsoft.com/windowsmobile
http://msdn.microsoft.com/office/understanding/vsto/