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

A common problem for Outlookusers is handling incoming attachments, especially when they are a norm not theexception. However, there are ways to automate the handling of Outlookattachments by using either VBA or VSTO.

In this article, I look intothe handling of Outlook attachments using VSTO 2008. The objective here is tocreate an AddIn for Outlook 2007 that will automatically handle incomingattachments.

Start by creating a new VSTO2008 AddIn for Outlook. You can do by accessing:
 
1.    File à New Project
2.    Project Types à Office 2007
3.    Outlook 2007 Add-in

You should name the Add-In andsave it. I chose the name OLSaveAttachmentAddIn. The image below shows the NewProject Window: 


Figure 1: Creating a new Outlook Add-in Project

You can now click OK tocontinue to the next step. The next step will show you the ThisAddIn.vb windowcontaining the basic code for the Add-In class.

Our next objective is to create:

1.    the events for handling the Outlook application and afew buttons from our popup menu;
2.    the popup object and buttons;
3.    a file system object that will point to the folderthat will hold the saved attachment

The three steps above are shownbelow. Comments are added to the code so that it can be better understood:

Public Class ThisAddIn
    'Outlook object
    Private WithEventsoOutlook As Outlook.Application

    'Popup button to toggle save attachment on and off
    Private WithEventsoBtnHandleSaveOnOff As Office.CommandBarButton

    'Popup button to handle showing the "About" form
    Private WithEventsoBtnHandleAbout As Office.CommandBarButton

    'Popup button to handle showing the "Help" folderinside Outlook explorer
    Private WithEventsoBtnHandleHelp As Office.CommandBarButton

    'Outlook explorer
    Private oActiveExplorer AsOutlook.Explorer

    'Commandbar Popup object to be added to Outlook's main menu
    Private oPopup AsOffice.CommandBarPopup

    'Commandbar button to be added to the popup object
    Private oBtn AsOffice.CommandBarButton

    'Commandbar button to be used across different buttons
    Private oBtnGeneral AsOffice.CommandBarButton

    'File System object
    Private oFileSystem AsObject

    'Form "About". You must add a form object
    Private ofrmAbout AsfrmAbout

    'String for the location of where the attachments will besaved
    Private ConststrconLocation As String= "C:\OLAttachments\"

    Private SubThisAddIn_Startup(ByVal sender As Object, _
                                  ByVal e AsSystem.EventArgs) Handles Me.Startup

        'Author         :Robert Martim
        'Purpose        : SetOutlook object, set active Outlook explorer, create menu and check toggle"Save Attachment" on/off
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        oOutlook =Application
        oActiveExplorer= oOutlook.ActiveExplorer
        CreateMenu()
        ToggleMe()
    End Sub

    Private SuboOutlook_NewMailEx(ByVal EntryIDCollection _
                                   As String) Handles Application.NewMailEx

        'Author         :Robert Martim
        'Macro Purpose  : Newe-mail event. Check for attachment and process attachment
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Dim iFirst As Integer
        Dim strEntryID As String
        Dim iLength As Integer
        Dim oEmail AsOutlook.MailItem

        Try
            'Try for e-mail attachment and process attachment
            iFirst = 1
            iLength =Len(EntryIDCollection)
            strEntryID= Mid(EntryIDCollection, iFirst, (iLength - iFirst) + 1)

            oEmail =Application.Session.GetItemFromID(strEntryID)
            If HasAttachment(oEmail) ThenCall processAttachment(oEmail)
            oEmail = Nothing

        Catch ex As Exception
            'Catch error and report to user
           MsgBox(Err.Description, vbCritical, Err.Number)
        End Try
    End Sub

    Sub processAttachment(ByValolEmail As Outlook.MailItem)

        'Author         :Robert Martim
        'Macro Purpose  :Process attachment.
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Dim oAttachment AsOutlook.Attachment
        Dim strNewName As String

        Try
            'Try:
            'Get registry value and see whether Save Attachment istoggled to True
            If getRegistry("ToggleSaveAttachment")= True Then
                'Check for sender whose attachment are to be saved
                If olEmail.SenderEmailAddress = "rm@msofficegurus.com" Then
                    'Create File System Object
                   oFileSystem = CreateObject("Scripting.FileSystemObject")
                    'Check if folder exists. If not, create destination folder

                    If NotoFileSystem.FolderExists(strconLocation) ThenoFileSystem.CreateFolder(strconLocation)
                    'Loop through attachments and save them
                    For Each oAttachment In olEmail.Attachments
                       'If file already exists in destination folder,warn user
                       If oFileSystem.FileExists(strconLocation& oAttachment.DisplayName) Then
                           If MsgBox("Thefile '" & oAttachment.DisplayName & _
                                      "' already exists. Do wish to use anothername?", _
                                vbQuestion +vbYesNo) = vbYes Then
                                strNewName =InputBox("Type the new file name with fileextension", _
                                    "New name...", oAttachment.DisplayName)
                               oAttachment.SaveAsFile(strconLocation & strNewName)
                           End If
                       Else
                           'Otherwise, just save it.
                            oAttachment.SaveAsFile(strconLocation& oAttachment.DisplayName)
                       End If
                    Next
                End If
               oFileSystem = Nothing
            End If
        Catch ex As Exception
            'Catch error and report to user
           MsgBox(Err.Description, MsgBoxStyle.Critical, Err.Number)
        End Try
    End Sub

    Function HasAttachment(ByValolEmail As Outlook.MailItem) As Boolean

        'Author         :Robert Martim
        'Purpose        :Function to determine if e-mail object has an attachment
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        If olEmail.Attachments.Count >= 1 Then HasAttachment = True
    End Function

    Sub CreateMenu()

        'Author         :Robert Martim
        'Purpose        :Create menu
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Try
            ResetMenu()
            oPopup =oActiveExplorer.CommandBars("Menu Bar")_
               .Controls.Add(Office.MsoControlType.msoControlPopup, , , , Temporary:=True)
           oPopup.Caption = "Attachments"
            oBtn =oPopup.Controls.Add(Office.MsoControlType.msoControlButton)

            With oBtn
                .Caption = "SaveAttachment?"
                .Style= Office.MsoButtonStyle.msoButtonIconAndCaption
            End With

           oBtnHandleSaveOnOff = oBtn
           oBtnGeneral= oPopup.Controls.Add(Office.MsoControlType.msoControlButton)

            With oBtnGeneral
               .BeginGroup = True
               .Caption = "About"
               .FaceId= 326
               .Style= Office.MsoButtonStyle.msoButtonIconAndCaption
            End With

           oBtnHandleAbout = oBtnGeneral
           oBtnGeneral= oPopup.Controls.Add(Office.MsoControlType.msoControlButton)

            With oBtnGeneral
               .Caption = "Help"
                .FaceId= 984
                .Style= Office.MsoButtonStyle.msoButtonIconAndCaption
            End With

           oBtnHandleHelp = oBtnGeneral

        Catch ex As Exception
           MsgBox(Err.Description, MsgBoxStyle.Critical, Err.Number)
        End Try
    End Sub

    Sub ResetMenu()

        'Author         :Robert Martim
        'Purpose        :Reset the "Menu Bar"
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        On Error Resume Next
       oActiveExplorer.CommandBars.Item("MenuBar").Reset()
    End Sub

    Private SuboBtnHandleSaveOnOff_Click( _
                                            ByVal Ctrl As  _
                                           Microsoft.Office.Core.CommandBarButton, _
                                            ByRef CancelDefault AsBoolean) _
                                            Handles oBtnHandleSaveOnOff.Click

        'Author         :Robert Martim
        'Purpose        :Button event to handle toggling "Save Attachment" on/off
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Try
            oFileSystem= CreateObject("Scripting.FileSystemObject")
            If NotoFileSystem.FolderExists(strconLocation) Then
               oFileSystem.CreateFolder(strconLocation)
                MsgBox("Your attachments will be saved in "& strconLocation, MsgBoxStyle.Information)
            End If
        Catch ex As Exception
            MsgBox("Ensure you have a folder named 'OLAttachments'located at C:\", MsgBoxStyle.Critical)
        End Try

        'Call sub to save toggle value in the Registry for lateruse

        Call saveRegistry("ToggleSaveAttachment",_
                         Not getRegistry("ToggleSaveAttachment"))

        'Call sub to toggle this button according to the savedvalue in the Registry
        Call ToggleMe()
        oFileSystem = Nothing
    End Sub

    Private SubToggleMe()

        'Author         :Robert Martim
        'Purpose        :Toggle "Save Attachment" on/off according to saved Registry value
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        Try
            If Not getRegistry("ToggleSaveAttachment") = True Then
               oBtn.State = Office.MsoButtonState.msoButtonUp
            Else
               oBtn.State = Office.MsoButtonState.msoButtonDown
            End If
        Catch ex As Exception
           MsgBox(Err.Description, MsgBoxStyle.Critical, Err.Number)
        End Try
    End Sub

    Function getRegistry(ByValstrKey As String)As Boolean

        'Author         :Robert Martim
        'Purpose        :Function to return the Registry saved value.
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        On Error Resume Next
        getRegistry =GetSetting("AddInProject", "AddInProjectValues", strKey)
        If Err.Number <> 0 ThengetRegistry = False
    End Function

    Sub saveRegistry(ByValstrKey As String,ByVal blnSetting AsBoolean)

        'Author         :Robert Martim
        'Purpose        : Functionto save the value to the Registry
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        On Error Resume Next
        SaveSetting("AddInProject", "AddInProjectValues",strKey, blnSetting)
    End Sub

    Private SuboBtnHandleAbout_Click(ByVal Ctrl As  _
                                     Microsoft.Office.Core.CommandBarButton, _
                                      ByRef CancelDefault AsBoolean) _
                                      Handles oBtnHandleAbout.Click

        'Author         :Robert Martim
        'Purpose        :Button event to handle showing the "About" form.
        'Created on     : 18April 2009
        'Last updated   : 18April 2009

        ofrmAbout = New frmAbout
       ofrmAbout.ShowDialog()
    End Sub


    Private SuboBtnHandleHelp_Click(ByVal Ctrl As  _
                                      Microsoft.Office.Core.CommandBarButton, _
                                       ByRef CancelDefault AsBoolean) _
                                       HandlesoBtnHandleHelp.Click

        'Author         :Robert Martim
        'Purpose        :Button to handle the "Help". In this case, MS Office Gurus website isopened inside and Outlook folder
        'Created on     : 18 April 2009
        'Last updated   : 18April 2009

        Dim oNS AsOutlook.NameSpace
        Dim oInboxFolder AsOutlook.Folder
        Dim oWebFolder AsOutlook.Folder

        oNS =oOutlook.GetNamespace("MAPI")
        oInboxFolder =oNS.GetDefaultFolder(Outlook.OlDefaultFolders.olFolderInbox)

        Try
            'Try to get the "Web Pages" folder
            oWebFolder= oInboxFolder.Folders("Web Pages")
        Catch ex As Exception
            'If the folder does not exist, add it
            oWebFolder= oInboxFolder.Folders.Add("Web Pages")
        End Try

        'Open MS Office Gurus URL in this folder
        With oWebFolder
           oWebFolder.WebViewURL = "http://msofficegurus.com/"
           oWebFolder.WebViewOn = True
           oActiveExplorer.CurrentFolder = oWebFolder
        End With

        oWebFolder = Nothing
        oInboxFolder = Nothing
        oNS = Nothing
    End Sub
End Class

You can now run the OutlookAdd-in. A new menu will be added to the Menu Bar as shown below: 

: Custom menu added to the "Menu Bar"

The attachments will beautomatically (when the “Save Attachment?” button is toggled to ü) to the destination folder: 

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):

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: