Microsoft Office 2010 new features include "cancel on load". This is a little "Cancel" button on the right-hand bottom corner of the application splash screen which can be used to cancel the loading of the application:

Figure 1: Cancel on load

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: 

If you use e-mails, you have certainly received loads of junk in the past and will continue to get more in the future. There are many ways you can deal with junk mail such as using a specialized tools (antispam tools). Alternatively you can use the Rules and Alerts function of Outlook to filter unwelcome e-mail.

Rules have many uses. You could simply delete a message for good or upon arriving you could move it to another folder. Or you could forward it to your smartphone or any other e-mail address you like.

Figure 1: Outlook 2007 Rules and Alerts dialog box

Up until Office 2007, you could create rules for Outlook using VBA. The best you could do was to create a class that after being initialized, it would process e-mails upon arrival and get them sorted accordingly. The problem here was that we could received different object such as a task (which is not an e-mail object), which could generate an error.

Another issue with classes was that we would need to certify the code in order to avoid the security message Office displays when there is code. Also, accessing information such as "sender" would trigger Outlook's security message. With Rules, we can bypass all these issues:

Figure 2 : Outlook 2007 security is trigger when accessing e-mail information

Obviously, this warning is not what we want. We really want something being done behind the scenes with minimal intrusion in our workflow. We could use tools such as Redemption, but it would still require a lot from us in terms of coding. By creating a Rule we bypass all these issues.

The project that follows will create an Outlook Rule from Excel (this will trigger a security message from Outlook, but only when the code is run for the first time).

The first thing you need to do is to add a reference to Outlook's Object Library. You do so by opening the VBE window (press Alt+F11 to do so). The click on Tools --> References ... as shown in the image below:

Figure 3 : Adding Outlook Object Library reference to an Excel project

When you click on References... the references dialog box will open as shown below:

After you've added the reference, we're ready to start writing our code.Add a new standard module to the project where the following code will be inserted:

Sub createOutlookRule()
Dim appOutlook As Outlook.Application
Dim olRules As Outlook.Rules
Dim myRule As Outlook.Rule
Dim moveToAction As Outlook.MoveOrCopyRuleAction
Dim fromAction As Outlook.ToOrFromRuleCondition
Dim myInbox As Outlook.Folder
Dim moveToFolder As Outlook.Folder

Set appOutlook = New Outlook.Application
Set myInbox = appOutlook.Session.GetDefaultFolder(olFolderInbox)
Set moveToFolder = myInbox.Folders("MyWorkFlow")
Set olRules = appOutlook.Session.DefaultStore.GetRules()
Set myRule = olRules.Create("My Test Rule", olRuleReceive)
Set fromAction = myRule.Conditions.From

' With the "from" action
With fromAction
' Enable the condition

.Enabled = True

' Add the e-mail sender
' Here, I use the user-friendly name in my address book
.Recipients.Add ("Robert Martim")

' Resolve the recipients so that we know they're valid
.Recipients.ResolveAll
End With

' Determine the action type (moveToFolder, in this case)
Set moveToAction = myRule.Actions.moveToFolder

' With the moveToAction
With moveToAction
' Enable the action
.Enabled = True

' Determines the destination folder
.Folder = moveToFolder
End With

' Save the rule in Outlook's Rules list
olRules.Save

End Sub

As pointed out, when you run this code for the first time you will get Outlook's security message. You should allow the access so that the code can do its job. After the code has done its trick you can open Outlook's Rules and Alerts dialog box and it will show the new rule as per image below:

CONCLUSION

In this short article you have learned how to work with some of the new objects of Outlook 2007 using Excel 2007 so that Outlook rules can be created. This example is applicable to other Office tools.