VBA Excel: Highlight Current Row and Column

Description:	This macro automatically highlights the row and column for the current active cell using an event procedure. Note that any fill colors on the sheet will be removed.
Discussion:	Suppose you have a table of tons of data. This macro will highlight the current row from column A to the active cell, and the current column from 1 to the active cell. This will easily identify what the active cell is, and make it easier to locate the data.
Code:
Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim RngRow As Range Dim RngCol As Range Dim RngFinal As Range Dim Row As Long Dim Col As Long Cells.Interior.ColorIndex = xlNone Row = Target.Row Col = Target.Column Set RngRow = Range("A" & Row, Target) Set RngCol = Range(Cells(1, Col), Target) Set RngFinal = Union(RngRow, RngCol) RngFinal.Interior.ColorIndex = 6 End Sub
How to use:	Open Excel. Press Alt+F11 to open the VBE. From the Project Explorer double click on one of the Sheets (i.e. Sheet1). Paste the code in the window that opens up. Close the VBE (Alt + Q or press the X in the top-right corner).
Test the code:	Follow the instructions in the "How to use" section. In Excel goto the sheet you put the code in. Select a cell and see the area get highlighted. Note that with this macro as is, all other cell fill colors will be removed. Download the attachment for a working example.
Sample File:	SelectionChange.ZIP 7.02KB

Get Attachment file, read XML on Outlook VBA

Function GetAttachments()
   

Dim ns As NameSpace
 Dim Inbox As MAPIFolder
 Dim Item As Object
 Dim Atmt As Attachment
 Dim FileName As String
 Dim i As Integer


 Set ns = GetNamespace("MAPI")
 Set Inbox = ns.Folders("NDucanh@agilitylogistics.com").Folders("Inbox")
 ''ns.GetDefaultFolder(olFolderInbox)
 i = 0
 If Inbox.Items.Count = 0 Then
    MsgBox "There are no messages in the Inbox.", vbInformation, _
           "Nothing Found"
    Exit Function
 End If


  For Each Item In Inbox.Items
 
    For Each Atmt In Item.Attachments
    
    If (GetExtName(Atmt.FileName) = "msg") Then
       FileName = "C:\ATT\" & Atmt.FileName
       Atmt.SaveAsFile FileName
       i = i + 1
    End If
    Next Atmt
    Next Item


GetAttachments_exit:
   Set Atmt = Nothing
   Set Item = Nothing
   Set ns = Nothing
   Exit Function
On Error GoTo GetAttachments_err
GetAttachments_err:
   MsgBox "An unexpected error has occurred." _
      & vbCrLf & "Please note and report the following information." _
      & vbCrLf & "Macro Name: GetAttachments" _
      & vbCrLf & "Error Number: " & Err.Number _
      & vbCrLf & "Error Description: " & Err.Description _
      , vbCritical, "Error!"
   Resume GetAttachments_exit
End Function

Function GetExtName(ScanString As String) As String
      
'*******************************************************
'     Retrieves File Extension Name from full
'       directory path
'   File Extension Only
'          
'   Public
'     FullPath:
'           Full Filepath incl. Filename
'              
'    If GetExtName("c:\autoexec.bat")
'              
'*******************************************************
   
    Dim intPos As String
    Dim intPosSave As String
   
    If InStr(ScanString, ".") = 0 Then
        GetExtName = ""
        Exit Function
    End If
   
    intPos = 1
    Do
        intPos = InStr(intPos, ScanString, ".")
        If intPos = 0 Then
            Exit Do
        Else
            intPos = intPos + 1
            intPosSave = intPos - 1
        End If
    Loop

    GetExtName = Trim$(Mid$(ScanString, intPosSave + 1))

End Function

Sub SaveXML1()
    
    Dim i As Integer
    Dim strFile As String
    i = 1
    Dim OL As Outlook.Application
    Dim Msg As Outlook.MailItem
    
    Set OL = New Outlook.Application
    
    strFile = Dir$("C:\ATT\" & "*.MSG")
    
    Do While strFile <> vbNullString
        
        Set Msg = OL.CreateItemFromTemplate("C:\ATT\" & strFile)
        
         'for all attachments do...
        For Each at In Msg.Attachments
            at.SaveAsFile "C:\ATT\MSG\" & Replace(at.FileName, "." & GetExtName(at.FileName), "") & "-" & CStr(i) & "." & GetExtName(at.FileName)
            i = i + 1
        Next at
        
        Set Msg = Nothing
        strFile = Dir
        
    Loop
    
    Set OL = Nothing
    SaveXML12
    
End Sub

Function SaveXML12()
    
    Dim i As Integer
    Dim strFile As String
    i = 21
    Dim OL As Outlook.Application
    Dim Msg As Outlook.MailItem
    
    Set OL = New Outlook.Application
    
    strFile = Dir$("C:\ATT\MSG\" & "*.MSG")
    
    Do While strFile <> vbNullString
        
        Set Msg = OL.CreateItemFromTemplate("C:\ATT\MSG\" & strFile)
        
         'for all attachments do...
        For Each at In Msg.Attachments
            at.SaveAsFile "C:\ATT\MSG\" & Replace(at.FileName, GetExtName(at.FileName), "") & "-" & CStr(i) & "." & GetExtName(at.FileName)
            i = i + 1
        Next at
        
        Set Msg = Nothing
        strFile = Dir
        
    Loop
    
    Set OL = Nothing
    
End Function


Sub ExportToExcel()

On Error Resume Next
    Kill "C:\ATT\MSG\*.*"
    Kill "C:\ATT\*.*"
    On Error GoTo 0

  On Error GoTo ErrHandler
 
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Dim rng As Excel.Range

Dim strSheet As String

Dim strPath As String

Dim intRowCounter As Integer

Dim intColumnCounter As Integer

Dim Msg As Outlook.MailItem

Dim nms As Outlook.NameSpace

Dim fld As Outlook.MAPIFolder

Dim itm As Object

    strSheet = "List.xls"
    strPath = "C:\ATT\"

strSheet = strPath & strSheet

Debug.Print strSheet

  'Select export folder

Set nms = Application.GetNamespace("MAPI")

Set fld = nms.PickFolder

  'Handle potential errors with Select Folder dialog box.

If fld Is Nothing Then

MsgBox "There are no mail messages to export", vbOKOnly, "Error"

Exit Sub

ElseIf fld.DefaultItemType <> olMailItem Then

MsgBox "There are no mail messages to export", vbOKOnly, "Error"

Exit Sub

ElseIf fld.Items.Count = 0 Then

MsgBox "There are no mail messages to export", vbOKOnly, "Error"

Exit Sub

End If

  'Open and activate Excel workbook.


Dim i As Integer
i = 0
For Each itm In fld.Items

For Each Atmt In itm.Attachments
    
    If (GetExtName(Atmt.FileName) = "msg") Then
       FileName = "C:\ATT\" & Replace(Atmt.FileName, "." & GetExtName(Atmt.FileName), "") & "-" & CStr(i) & "." & GetExtName(Atmt.FileName)
       Atmt.SaveAsFile FileName
       i = i + 1
    End If
    Next Atmt

Next itm

SaveXML1
ReadXML

Set Msg = Nothing

Set nms = Nothing

Set fld = Nothing

Set itm = Nothing

  Exit Sub

ErrHandler:  If Err.Number = 1004 Then

MsgBox strSheet & " doesn't exist", vbOKOnly, "Error"

Else

MsgBox Err.Number & "; Description: ", vbOKOnly, _
"Error"

End If

Set appExcel = Nothing

Set wkb = Nothing

Set wks = Nothing

Set rng = Nothing

Set Msg = Nothing

Set nms = Nothing

Set fld = Nothing

Set itm = Nothing

End Sub
Function ReadXML()
Dim xml_doc As New DOMDocument
Dim oRoot As MSXML2.IXMLDOMNode
Dim nde_test As IXMLDOMElement
Dim nde_test2 As IXMLDOMElement
Dim oAttributes As MSXML2.IXMLDOMNamedNodeMap
Dim oChildren As MSXML2.IXMLDOMNodeList
Dim strFile As String

Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Dim rng As Excel.Range
Dim strSheet As String

Dim strPath As String

Dim intRowCounter As Integer

Dim intColumnCounter As Integer
strSheet = "List.xls"
strPath = "C:\"
intColumnCounter = 1


intRowCounter = 1
strSheet = strPath & strSheet
    Dim i As Integer
   
    
    Dim OL As Outlook.Application
    Dim Msg As Outlook.MailItem
    
    Set OL = New Outlook.Application
 
   
    Set appExcel = CreateObject("Excel.Application")

appExcel.Workbooks.Open (strSheet)


Set wkb = appExcel.ActiveWorkbook

Set wks = wkb.Sheets(1)

wks.Activate

appExcel.Application.Visible = True
 strFile = Dir$("C:\ATT\MSG\" & "*.xml")
 Do While strFile <> vbNullString
 intColumnCounter = 1
 intRowCounter = intRowCounter + 1
    xml_doc.Load "C:\att\msg\" & strFile
    Dim nNodeList As MSXML2.IXMLDOMNodeList
    Dim nNode As MSXML2.IXMLDOMNode
    Set oRoot = xml_doc.DocumentElement
   
   Set rng = wks.Cells(1, 1)
   rng.Value = "Provider"
   Set rng = wks.Cells(1, 2)
   rng.Value = "SONumber"
   Set rng = wks.Cells(1, 3)
   rng.Value = "Receiptdate"
   Set rng = wks.Cells(1, 4)
   rng.Value = "Receipthr"
   Set rng = wks.Cells(1, 5)
   rng.Value = "NumberOfCartons"
   Set rng = wks.Cells(1, 6)
   rng.Value = "Weight"
   Set rng = wks.Cells(1, 7)
   rng.Value = "Volume"
       For Each nNode In oRoot.ChildNodes
    
     For Each lnode In nNode.ChildNodes
              

            Set rng = wks.Cells(intRowCounter, intColumnCounter)

           rng.Value = lnode.Text
           intColumnCounter = intColumnCounter + 1
     Next lnode
   
    Next nNode
    
    strFile = Dir
   
   Loop
    Set OL = Nothing
   
   

End Function

The Panes Collection Object

The Panes collection object is a property object of the Explorer object. The Panes collection object contains the three panes of the Outlook Explorer window, as shown in Figure 11-6. These are the Outlook Bar pane, the Folder List pane, and the Preview pane.

You can create an instance of an OutlookBarPane object from the Panes collection only. The Preview and Folder List panes are not accessible from the Outlook Object Model. When you navigate an Outlook Bar’s groups and shortcuts in code, you start with the Panes collection object, as demonstrated in the following code example:

Dim OlBarPane As Outlook.OutlookBarPane
Dim OlExplorer As Outlook.Explorer
Set OlExplorer = Application.ActiveExplorer
Set OlBarPane = OlExplorer.Panes("OutlookBar")
‘Make the Outlook Bar visible if it’s hidden
If OlBarPane.Visible = False Then
    OlBarPane.Visible = True
End If
MsgBox "The Current Outlook Bar Group is " _
  & OlBarPane.CurrentGroup, vbInformation

Figure 11.6 - Three Explorer panes comprise the Panes collection object.

Determining Whether a Pane is Visible

You can determine whether an individual pane is visible by using the IsPaneVisible method of the Explorer object. To make a pane visible, you use theShowPane method. The following VBScript code makes the Folder List pane visible:

Sub ShowFolderList
    Const olFolderList = 2
    Set objExpl = Application.ActiveExplorer
    If Not(objExpl.IsPaneVisible(olFolderList)) Then
        objExpl.ShowPane olFolderList, True
    End If
End Sub

You cannot size panes programmatically in the Explorer window in Outlook.

The OutlookBarPane Object

The OutlookBarPane object is the only object you can instantiate from the Panes collection object. It represents the Outlook Bar as well as its groups and shortcuts. Generally, you’ll create a reference to the OutlookBarPane object as a means to access its dependent child objects that represent Outlook Bar groups and shortcuts. You can use the CurrentGroup property of the OutlookBarPane object to set or get the current group on the Outlook Bar. The OutlookBarPane object supports two important events: BeforeNavigate and BeforeGroupSwitch. These events inform you when a user is navigating to a shortcut or a group, respectively. For additional information on writing event procedures as well as the events supported by the OutlookBarPane, OutlookBarGroups, and OutlookBarShortcuts objects, see Chapter 9.

The Outlook Window (Explorer Objects)

The Explorer object represents the window in which the contents of a folder are displayed. The Explorers object is the parent collection object for Explorer objects. The following sections cover some of the methods and properties for the Explorer and Explorers objects.

Figure 11-5 illustrates elements of the Outlook user interface viewed from an object model perspective. This illustration is not meant to be all-inclusive; it shows just a few of the objects in the Outlook Object Model that you can manipulate programmatically.

Figure 11.5 - Object model components of the Outlook user interface.

For a complete list and description of the properties, methods, and events for the Explorer and Explorers objects, see Microsoft Outlook Visual Basic Reference Help.

Open the Explorer object item in the VBScript Samples folder to work directly with this code in Outlook.

Explorer Methods

Creating a New Explorer Window

Outlook supports an Explorers collection object. You can use the Explorers object to add a new Explorer window for a specific folder. Use the Display method to present the new Explorer window. The following example creates a new Explorer window for the Drafts folder by using the Add method and then shows the new window on the desktop in a normal window state.

Const olNormalWindow = 2
Sub DisplayNewExplorer_Click
    Set myExplorers = Application.Explorers
    Set myFolder = Application.GetNameSpace("MAPI").GetDefaultFolder(16)
    Set myOlExpl = myExplorers.Add(myFolder, 2)
    myOlExpl.Display

    myOlExpl.WindowState = olNormalWindow
End Sub

Explorer Properties

Returning the Active Folder

You can use the CurrentFolder property of the Explorer object to return the active folder in the Outlook window. The following example shows the name of the active folder in the message box when the DisplayTheCurrentFolder control is clicked:

Sub DisplayTheCurrentFolder_Click
    Set myExplorer = Application.ActiveExplorer
    MsgBox "The current folder in the Explorer is: " _
        & myExplorer.CurrentFolder.Name, vbInformation
End Sub

Obtaining the Current View for the Active Explorer

You can use the CurrentView property of the Explorer object to return or set the current view for the Active Explorer window. The example displays the name of the current view for the Active Explorer window.

Sub DisplayTheExplorerView_Click
    Set myExplorer = Application.ActiveExplorer
    MsgBox "The current Explorer view is: " & vbCr _
        & myExplorer.CurrentView, vbInformation
End Sub

Determining Which Items are Selected in the Explorer

The Selection collection object lets you know which items are selected in the Explorer window. The Selection object, in turn, contains an Items collection that lets you iterate over selected items. If you are writing VBA code, you can respond to the SelectionChange event of the Explorer object. The following example displays the number of selected items in the Active Explorer and then asks whether the user wants to display the items:

Sub DisplaySelectedItems_Click
    DisplayNewExplorer_Click
    Set mySelection = Application.ActiveExplorer.Selection
    MsgBox "The number of selected items in the Explorer is " _
        & mySelection.Count, vbInformation
    If MsgBox ("Display selected items?", vbYesNo+vbQuestion) = vbNo Then
        Exit Sub
    End If
    For i = 1 to mySelection.Count
        Set myItem = mySelection.Item(i)
        myItem.Display
    Next
End Sub

The NameSpace Object

In Outlook, the NameSpace object represents the MAPI message store. The NameSpace object provides methods for logging on or off Outlook, referencing a default folder, and returning objects directly by ID. In addition, the NameSpace object provides access to a variety of methods and properties that are not normally available with the Application object.

For a complete list and description of the methods, properties, and events for the NameSpace object, see Microsoft Outlook Visual Basic Reference Help.

Open the NameSpace object item in the VBScript Samples folder to work directly with this code in Outlook.

NameSpace Object Methods

This section covers the GetDefaultFolder method and the dial method of the NameSpace object.

Returning a Default Folder

You can use the GetDefaultFolder method of the NameSpace object to access folders in the root folder, also known as the Mailbox. To reference a folder in the Mailbox, you can either specify a numeric value as the argument in the GetDefaultFolder method or copy the olDefaultFolders constants from the Outlook Constants item in the VBScript Samples folder and paste them into your code. The table lists these numeric values.

The following example uses the GetDefaultFolder method of the NameSpace object to return the Contacts folder and then display it:

Sub CommandButton1_Click
    Set MyFolder = Application.GetNameSpace("MAPI").GetDefaultFolder(10)
    MyFolder.Display
End Sub

Folder	Value
Deleted Items	3
Outbox	4
Sent Items	5
Inbox	6
Calendar	9
Contacts	10
Journal	11
Notes	12
Tasks	13
Drafts	16

Dialing a Phone Number

The Dial method is new to Outlook 2002. If you supply a ContactItem as the argument to the Dial method, you will display the Outlook automatic phone dialer for that contact. The following code example uses the Dial method for the first ContactItem in your Contacts folder:

Sub DialPhone_Click()
    On Error Resume Next
    Dim objContactsFolder, objContactItem
    Const olFolderContacts = 10
    Set objContactsFolder = _
        Application.GetNamespace("MAPI") _
        .GetDefaultFolder(olFolderContacts)
    Set objContactItem = objContactsFolder.Items(1)
    If objContactItem Is Nothing Then
        MsgBox "Could not find a contact to dial." _
            , vbInformation
    Else
        Application.GetNamespace("MAPI").Dial (objContactItem)
    End If 
End Sub

Properties of the NameSpace Object

The NameSpace object provides two properties that you use quite often. These are the CurrentUser and Folders properties.

Returning the Name of the Current User

You can use the CurrentUser property of the NameSpace object to return the name of the currently logged-on user. This example shows the current user’s name in the message box when the CommandButton1 control is clicked:

Sub CommandButton1_Click
    Set MyNameSpace = Application.GetNameSpace("MAPI")
    MsgBox MyNameSpace.CurrentUser
End Sub

Referencing a Folder Collection

You can use the Folders property of the NameSpace object to reference the collection of folders in the MAPI NameSpace. The following example displays the number of subfolders in the Building Microsoft Outlook 2002 Applications .pst file:

Sub ReferenceAFolderCollection_Click
    Set MyNameSpace = Application.GetNameSpace("MAPI")
    set objFolder = _
        MyNameSpace("Building Microsoft Outlook 2002 Applications")
    Set colFolders = objFolder.Folders
    MsgBox "There are " & colFolders.Count & " subfolders" _
        & vbCr & "in " & objFolder.Name, vbInformation
End Sub

Selecting a Folder

You can use the PickFolder method of the NameSpace object to return a MAPIFolder object. The PickFolder method displays a dialog box for the user to select a folder from all available folders in the current profile. The following example displays the Select Folders dialog box and also displays an alert dialog box if the user clicks Cancel. If the user selects a folder, then the folder is displayed in an Explorer window.

Sub PickAFolder_Click()
On Error Resume Next
Set MyNameSpace = Application.GetNameSpace("MAPI")
Set objFolder = MyNameSpace.PickFolder
    If objFolder Is Nothing then
        MsgBox "User Pressed Cancel!", vbInformation
    Else
        objFolder.Display
    End If
End Sub

The Outlook E-Mail Security Update Object Model Guard

If you’re using Outlook 2002 or a version of Outlook 2000 or Outlook 98 with the Outlook E-Mail Security Update installed, the code shown in CommandButton1_Click will display a Warning dialog box, as shown in Figure 11-4. Accessing the CurrentUser property causes the Warning dialog box to appear. The object model guard component of the Outlook E-Mail Security Update figures prominently in this chapter. Wherever possible, I will suggest workarounds for blocked object model properties and methods. Remember that developers targeting Exchanger Server users can use the Administrative form in Public Folders to designate selected COM Add-ins as trusted. These trusted COM Add-ins are not subject to the limitations of the object model guard. For details regarding the Outlook E-Mail Security Update and the use of trusted COM Add-ins to customize security restrictions, see Chapter 13.

In order to avoid the Warning dialog box, use the following code to obtain the name of the CurrentUser property:

Function GetCurrentUser()
    Dim objTopFolder As MAPIFolder
    Dim strTopFolder As String
    Dim olApp As Outlook.Application
    Set olApp =CreateObject(“Outlook.Application ”)
    Set objTopFolder =olApp.GetNamespace(“MAPI ”)_
        .GetDefaultFolder(olFolderInbox).Parent
    strTopFolder =objTopFolder.Name
    If InStr(1,strTopFolder,“-”)Then
        GetCurrentUser =_
            Trim(Right(strTopFolder,_
            Len(strTopFolder)-InStr(1,strTopFolder,“-”)))
    Else
        GetCurrentUser =strTopFolder
    End If
End Function

Figure 11.4 - This Warning dialog box appears when you attempt to access the CurrentUser property of the NameSpace object.

The Application Object

The Application object sits at the top of the object model and represents the entire Outlook application. The Outlook Application object has several purposes:

• As the root object, it enables you to reference other objects in the Outlook object hierarchy.
• It provides methods such as CreateItem and CreateObject so that you can create new items and reference them without moving through the object hierarchy.
• It provides methods for directly referencing the active Outlook window or form.

For a complete list and description of the methods, properties, and events for the Application object, see Microsoft Outlook Visual Basic Reference Help. Also see the Visio document entitled "Outlook 2002 Object Model Extended View" in the Outlook 2002 Object Model folder under the Visual Basic for Applications folder under the 4. Beyond the Basics folder. This object model diagram shows the properties, methods, and events for every object in the Outlook object model.

Open the Application object item in the VBScript Samples folder to work directly with this code in Outlook.

Application Object Methods

This section covers the ActiveExplorer, ActiveWindow, AdvancedSearch, CopyItem, CreateItem, CreateObject, and GetNameSpace methods.

Returning the Active Window

You can use the ActiveWindow method of the Application object to return the topmost active Outlook window. The ActiveWindow method returns either an Explorer or an Inspector object, depending on the actual active window.

‘Get the Active Outlook Window
‘The ActiveWindow object is the topmost window in the running
‘Outlook instance
Sub GetOutlookActiveWindow_Click
    If TypeName(Application.ActiveWindow) = "Inspector" Then
        MsgBox "The active window is an inspector", vbInformation
    Else
        MsgBox "The active window is an explorer", vbInformation
    End If
End Sub

The following example sets the MyExplorer object to the currently active Outlook Explorer window, displays a message that indicates the active window type, and then redisplays the item window when the ShowOutlookActiveExplorer control is clicked:

Sub ShowOutlookActiveExplorer_Click
    Set MyExplorer = Application.ActiveExplorer
    MyExplorer.Display
    GetOutlookActiveWindow_Click()
    Item.Display
End Sub

Creating a Standard Item

You can use the CreateItem method of the Application object to create and return Outlook standard items such as a Message item, a Post item, or an Appointment item. The following example creates a Message item using the default editor and displays it when CreateAMailMessageis clicked:

Sub CreateAMailMessage_Click
    Set MyItem = Application.CreateItem(0)
    MyItem.Display
End Sub

The next simple example creates an HTML message.

Sub CreateHTMLMessage_Click
    Const olMailItem = 0
    Set myItem = Application.CreateItem(olMailItem)
    myItem.shtmlLBody = ""
    myItem.Display
End Sub

It’s useful to know that you can also create a Word Envelope message in code. The following example uses the CreateObject method to launch Word and display a Word Envelope message. From the standpoint of the message body, Word Envelope messages are equivalent to HTML messages.

‘Creates a Word Message item and displays it
Sub CreateAWordMessage_Click
    Const wdNewEmailMessage = 2
    Dim objApp,objMsg 
    Set objApp = CreateObject("Word.Application")
    Set objMsg = objApp.Documents.Add(,,wdNewEmailMessage)
    objApp.Visible = True
    objApp.ActiveWindow.EnvelopeVisible = True
End Sub

The following table lists the numeric values you use as arguments for the CreateItem method. You can also copy the CONST declarations in the Enum OlItemType section of the Outlook Constants item in the VBScript Samples folder and paste them into your code.

Type of Item	Value
Appointment	1
Contact	2
Distribution List	7
Journal	4
Mail Message	0
Note	5
Post	6
Task	3

For more information about creating custom items, see "Items Collection Methods" later in this chapter.

Creating an Automation Object

You can use the CreateObject method of the Application object to create Automation objects, such as Microsoft Excel, Microsoft Access, or Microsoft Word objects. You can also use the CreateObject method to create instances of custom ActiveX DLLs that extend the functionality of Outlook. The following example uses the CreateObject method to create an instance of Excel, adds a workbook, and then renames the first sheet in the workbook to Outlook CreateObject Example:

‘Launch Excel with CreateObject
Sub LaunchExcel_Click
    Dim xLApp ‘As Excel.Application
    Dim xLSheet ‘As Excel.Worksheet
    Set xLApp = CreateObject("Excel.Application")
    If xLApp Is Nothing Then
        MsgBox "Could not create Excel Application", vbCritical
        Exit Sub
    End If
    xLApp.Workbooks.Add
    Set xLSheet = xLApp.Sheets(1)
    xLSheet.Name = "Outlook CreateObject Example"
    ‘Make the Excel Application window visible
    xLApp.Visible = True
End Sub

When you are writing Automation code for VBScript in Outlook forms, you can expedite the development process by using the VBA Editor in Outlook to write the code and then pasting the code into VBScript. As noted in the example above, you must place comment marks before the As keyword in the object type declarations, or VBScript will raise an error. The beauty of this approach is that you have all the IntelliSense features of the VBA Editor at your disposal, including auto list members, syntax checking, parameter information, quick information, and code formatting. Before you begin, set references to the appropriate object libraries by using the Tool menu’s References command in the VBA Editor window.

Copying an Item from the File System

The CopyItem method is new to Outlook 2002. It lets you copy an item from the File System to an Outlook folder. The following code example creates a Word document in the temporary folder, adds some text to the document, and then uses the CopyItem method to copy the document to the user’s Inbox folder. Note that the CopyItem method accepts a string for the path to the destination folder instead of a MAPIFolder object.

Sub CopyItemToInbox_Click
   Dim objWord 'As Word.Application
   Dim objDoc 'As Word.Document
   Dim objSelect 'As Word.Selection
   Dim objDocItem 'As DocumentItem
   Set objWord = CreateObject("Word.Application")
   Set objDoc = objWord.Documents.Add
   Set objSelect = objWord.Selection
   objSelect.TypeText "Word document created with Automation"
   strPath = GetTempDir & "\test.doc"
   objDoc.SaveAs strPath
   Set objDocItem = Application.CopyFile(strPath, "Inbox")
   objDocItem.Display
   Set objWord = Nothing
End Sub

Returning a MAPI NameSpace Object

You can use the GetNameSpace("MAPI") method of the Application object to return the MAPI message store.

In the following example, the GetNameSpace method returns the NameSpace object. The Offline property of the NameSpace object is then used to display a message box indicating whether the user is on line or off line.

Sub CommandButton1_Click()
    Dim MyNameSpace As NameSpace
    Set MyNameSpace = Application.GetNamespace("MAPI")
    If MyNameSpace.Offline Then
        MsgBox "You are offline!", vbInformation
    Else
        MsgBox "You are online!", vbInformation
    End If
End Sub

The only data source currently supported is MAPI, which allows access to all Outlook data stored in MAPI. For this reason, theGetNameSpace method must always appear in Outlook as GetNameSpace("MAPI").

Creating Office Objects

The Application object has several child objects that are actually members of the Microsoft Office Object Model. For example, the Application object contains member objects for the Office AnswerWizard, Assistant, COMAddIns, and LanguageSettings objects. The following code example uses an animated Assistant to display the LanguageID settings for the Outlook Application object.

‘Display the LanguageSettings
Sub DisplayLanguageSettings_Click
    Const msoLanguageIDInstall = 1, msoLanguageIDUI = 2
    Const msoLanguageIDHelp = 3
    Const msoAnimationListensToComputer = 26
    Const msoModeModal = 0, msoButtonSetOK = 1, msoIconTip = 3
    On Error Resume Next
    Dim oa ‘As Office.Assistant
    Dim bln ‘As Office.Balloon
    strMsg = "The following locale IDs are registered " _
        & "for this application:" & vbCr & "Install Language - " & _
        Application.LanguageSettings.LanguageID(msoLanguageIDInstall) _
        & vbCr & "User Interface Language - " & _
        Application.LanguageSettings.LanguageID(msoLanguageIDUI) _
        & vbCr & "Help Language - " & _
        Application.LanguageSettings.LanguageID(msoLanguageIDHelp)
    Set oa = Application.Assistant
    oa.On = True ‘Assistant not available
    If Err Then
        MsgBox strMsg, vbInformation
    Else
        oa.Visible = True
        Set bln = oa.NewBalloon
        bln.Heading = "Language Settings"
        bln.Mode = msoModeModal
        bln.Button = msoButtonSetOK
        bln.Icon = msoIconTip
        bln.Text = strMsg
        bln.Show
        oa.Animation = msoAnimationListensToComputer
    End If
End Sub 

Creating a Programmatic Search

The ability to create a programmatic search using the AdvancedSearch method of the Application object is new to Outlook 2002. AdvancedSearchreturns a Search object, which in turn contains a Results object that you can use to iterate over the items contained in that Results object. A Results object is identical to an Items collection object. You use the AdvancedSearch method in conjunction with the AdvancedSearchComplete event for the Application object. When the AdvancedSearchComplete event fires, you’ll know that the Search object for your query is available for further processing. Assign a Tag value in your call to AdvancedSearch so that you can identify the correct Search object in the AdvancedSearchComplete event. See Chapter 9, "Raise Events and Move to the Head of the Class," for additional details on the AdvancedSearchComplete event and Chapter 14, "Creating COM Add-Ins with Visual Basic," for a discussion of the sample Search add-in.

AdvancedSearch takes four arguments, two of which are enigmatically explained in Outlook Visual Basic Help. Here is the syntax for a call toAdvancedSearch:

Set objSearch = objApp.AdvancedSearch(Scope, Filter, SearchSubfolders, Tag)

Both the Scope and Filter arguments can be understood in the context of Microsoft Exchange 2000 Web Storage queries. Although you don’t have to run against an Exchange 2000 server to use AdvancedSearch, you should consult the Exchange SDK to gain a complete understanding of Web Storage System SQL. The Exchange SDK is available on the Web at http://msdn.microsoft.com/exchange and is also included on this book’s companion CD. See the section entitled "Web Storage System SQL."

Fortunately, there are quicker and less painful ways to get up to speed with Filter and Scope syntax. You can use an undocumented Registry key to display a Query Builder page on the Filter dialog box associated with the View Summary dialog box. (See Figure 11-1.) After you use the Query Builder to construct your query, you can then copy the Filter syntax displayed on the SQL page and paste it into your code. Do not attempt to add the Query Builder page Registry setting unless you are familiar with the Microsoft Windows Registry Editor.

Figure 11.1 - The undocumented Query Builder page on the Filter dialog box.

To display the Query Builder page on the Filter dialog box

1. Click Start, point to Run, type Regedit in the Run dialog box, and then click OK to launch the Windows Registry editor.
2. In the Registry tree, navigate to HKEY_CURRENT_USER\Software\ Microsoft\Office\10.0\Outlook.
3. Select New from the Edit menu, and then select Key from the New submenu.
4. Type QueryBuilder in the Key edit box. Regedit will suggest New Key #1, but you should replace that key name with QueryBuilder.

To build a filter using the Query Builder page on the Filter dialog box

1. In Outlook, select Current View from the View menu and then select Customize Current View from the Current View submenu.
2. Click the Filter button on the View Summary dialog box.
3. Click the Query Builder page on the Filter dialog box.
4. Use the Query Builder interface to build your query. When you construct a filter, you actually build a WHERE clause without the WHERE keyword. Notice that you can use the logical AND or logical OR operator to develop the query and move clauses up or down.
5. Click the SQL page shown in Figure 11-2 on the Filter dialog box, and clear the Edit These Criteria Directly check box. Once you clear the check box, you can copy the query by selecting it and pressing Ctrl+C to copy to the Clipboard.
6. Because you don’t want to modify the view, click Cancel to dismiss the Filter dialog box. Then click Cancel again to dismiss the View Summary dialog box.

Once you have constructed your Filter string, the rest of the process is relatively straightforward. The Scope argument can use either an unqualified folder name such as Inbox, Drafts, Tasks, or a folder path in a Web Storage System SQL Scope clause. SearchSubFolders is Boolean and will work only in a Mailbox or PST store. If you’re searching a public folder, you can search only one folder at a time. This is a built-in limitation of the MAPI Public Folder store. As stated previously, you should use the AdvancedSearchComplete event to process the Search object returned byAdvancedSearch. This next code example shows you how to construct a programmatic search and displays the user form shown in Figure 11-3 when the search is complete.

Figure 11.2 - Copy a Filter string from the SQL page of the Filter dialog box to provide the Filter argument for the AdvancedSearch method.

Sub ShowSearch()
    Dim olApp As Outlook.Application
    Dim objFolder As MAPIFolder
    Dim objSearch As Search
    Dim strFolderPath As String, strScope As String, strFilter As String
    Set olApp = New Outlook.Application
    ‘Create a MAPIFolder object for Inbox
    Set objFolder = olApp.GetNamespace("MAPI") _
       .GetDefaultFolder(olFolderInbox)
    ‘Get the folder path
    strFolderPath = objFolder.FolderPath
    ‘Build a scope string
    strScope = "SCOPE (‘shallow traversal of " _
       & AddQuotes(strFolderPath) & "‘)"
    ‘Build a filter string (WHERE clause without the WHERE)
    strFilter = AddQuotes("urn:schemas:mailheader:subject") _
       & " LIKE ‘RE:%’"
    ‘Create the Search object by calling AdvancedSearch
    Set objSearch = _
       olApp.AdvancedSearch(strScope, strFilter, False, "RESearch")
End Sub

Private Sub Application_AdvancedSearchComplete _
    (ByVal SearchObject As Search)
    On Error Resume Next
    Dim objResults As Results
    Dim objItem As Object
    Dim objListItem As Object
    Dim frmAdvancedSearch As New frmSearch
    If SearchObject.Tag = "RESearch" Then
        frmAdvancedSearch.ListView1.ListItems.Clear
        ‘Create the Results object
        Set objResults = SearchObject.Results
        ‘Create a reference to first item in Results object
        Set objItem = objResults.GetFirst
        If Not objItem Is Nothing Then
            Do
                ‘Add item to the ListView control
                Set objListItem = _
                   frmAdvancedSearch.ListView1.ListItems.Add
                With objListItem
                    .Text = objItem.Subject
                    .SubItems(1) = objItem.SenderName
                    .SubItems(2) = objItem.ReceivedTime
                    .SubItems(3) = objItem.Size
                    ‘Parent is Item container
                    .SubItems(4) = objItem.Parent
                    .SubItems(5) = objItem.EntryID
                End With
                ‘Reference next item in the Results object
                Set objItem = objResults.GetNext
            Loop Until objItem Is Nothing
        End If
        frmAdvancedSearch.Show
    End If
End Sub

Figure 11.3 - Display a custom dialog box that shows the results of a programmatic search in a ListView control.

Firing Order of Events

For item-level events, you can clearly predict the sequence in which events will fire. However, events at the application level are less predictable. You cannot guarantee that event firing will follow a specific sequence either because different events cause a unique firing sequence or because, in future versions of Outlook, the event sequence might change. Some insights into a typical firing sequence are offered here. If you want to investigate further, it is suggested that you write Debug.Print Object EventName statements for each event procedure supported by a given object variable declared using the WithEvents keyword. The following list shows just such a debug sequence when a user opens a mail message, enters recipients for the message, types a subject and message body, and then sends the message. Again, don't take this sequence as invariable; it simply gives you a reasonable expectation of the event sequence when you send a mail message.

1. Inspectors NewInspector
2. MailItem Open
3. Explorer Deactivate
4. Inspector Activate
5. MailItem Read
6. MailItem PropertyChange To
7. MailItem PropertyChange CC
8. MailItem PropertyChange BCC
9. MailItem BeforeCheckNames
10. MailItem PropertyChange To
11. MailItem PropertyChange CC
12. MailItem PropertyChange BCC
13. MailItem PropertyChange Conversation Index
14. MailItem PropertyChange ReceivedTime
15. MailItem PropertyChange Subject
16.  Application ItemSend   
17. MailItem Write
18. MailItem Close
19. Inspector Deactivate
20. Explorer Activate

Item Events

We've covered many of the application-level events available in Outlook 2002. Do you believe that you're ready to move to the head of the class yet? You can go beyond what you've learned so far and raise item-level events in addition to application-level events. You should be aware that Outlook 2000 introduced several new item-level events, including AttachmentAdd, AttachmentRead, BeforeAttachmentSave, and BeforeCheckNames. Outlook 2002 offers an additional item-level event: the BeforeDelete event. Following the convention established for application-level events, event names that begin with Before are subject to cancellation in the event procedure.

The important point about Outlook item-level events is that they can be controlled from ThisOutlookSession with Outlook VBA or from a COM Add-in created in Visual Basic or Visual C++. Outlook 97 and Outlook 98 did not support the declaration of item-level object variables using WithEvents in order to write event procedure code. Item-level event procedures were previously created using VBScript in Outlook custom forms. Many development projects will still require VBScript in Outlook forms. VBScript behind forms provides a simple means of enabling form-level automation and allowing that automation to travel from recipient to recipient.

In theory, an Outlook custom form could contain almost no VBScript code. The event-related behavior of this form could be controlled by item-level object variables that use Visual Basic code to create event procedures. The problem with this theoretical approach is that each client receiving your custom form would need to have your COM Add-in installed in order for the form's event procedures to function correctly. Portability of event automation is problematic when your code does not travel with the form itself. In a controlled corporate environment operating in conjunction with Exchange Server, the theoretical proposition of item-level event code residing in COM Add-in event procedures becomes more feasible if the correct COM Add-ins are deployed consistently to every client in the organization. In fact, this is one area of event automation that lends itself perfectly to COM Add-ins. If you want to create event procedures for default Outlook items such as a MailItem or ContactItem, you can do so without hesitation. A common complaint by Outlook developers is that a custom form cannot be substituted for the default mail message whose message class is IPM.Note. In Chapter 13, you'll learn how to make a custom form the default mail message in place of IPM.Note by changing registry settings. This ability constitutes a hidden feature of Outlook 2000 and Outlook 2002. If you don't want to resort to registry changes, you can create event procedure code that instantiates a MailItem object variable declared using WithEvents in the NewInspector event of the Inspectors collection object. You can use that MailItem object to write additional item-level event procedures.

The following example requires a subject to be entered for a message before the message is sent. If the user fails to enter a subject after the initial InputBox is displayed, the Send event is canceled. This code also requires chained event procedures to accomplish the intended result. All the required event procedures and declarations in the example have been included so that you can see how object variables and events are linked.

'Place these declarations in the general section of ThisOutlookSession
Public WithEvents colInsp As Outlook.Inspectors
Public WithEvents objMailItem As Outlook.MailItem

Private Sub Application_Startup()
    Set colInsp = Application.Inspectors
End Sub

Private Sub colInsp_NewInspector(ByVal Inspector As Inspector)
    Dim objItem As Object
    Set objInsp = Inspector
    On Error Resume Next
    Set objItem = objInsp.CurrentItem
    Select Case objItem.Class
        Case olMail
            Set objMailItem = objItem
    End Select
End Sub

Private Sub objMailItem_Send(Cancel As Boolean)
    If objMailItem.Subject = "" Then
       objMailItem.Subject = _
       InputBox("Enter a subject for this message:", "Subject Required")
       If objMailItem.Subject = "" Then
           Cancel = True
       End If
    End If
Exit Sub

This functionality could also be achieved by writing code for the ItemSend event of the Application object. However, the MailItem object has a much more granular event model. Using the previous example, you could write code for any one of the item-level events supported by the objMailItem object variable.

Adding Events to VBScript Behind Forms

By using the Script Editor, you can add code to an Outlook item event to modify the event's behavior. For example, for the Open event, you can add code to specify the current form page, or you can add code to load a ComboBox control with a recordset from a database. For the Click event, you can create a procedure that creates a custom item, includes an attachment to the current item, and then posts the item in a folder. Item events are available for all Outlook item types except for the NoteItem object. Certain events are cancelable; you can prevent them from completing by writing the appropriate code. The following table summarizes the item events in Outlook.

Event	Cancelable	Description
AttachmentAdd	No	Occurs when an attachment has been added to the item.
AttachmentRead	No	Occurs when an attachment has been opened for reading.
BeforeAttachmentSave	Yes	Occurs before an attachment in an item is saved.
BeforeCheckNames	Yes	Occurs before Outlook starts resolving names in the Recipients collection of the item.
BeforeDelete  (new to Outlook 2002)	Yes	Occurs before an item is deleted. This event will not fire unless the item is displayed in an Inspector.
Close	Yes	Occurs before Outlook closes the Inspector displaying the item.
CustomAction	Yes	Occurs before Outlook executes a custom action of an item.
CustomPropertyChange	No	Occurs when a custom item property has changed.
Forward	Yes	Occurs before Outlook executes the Forward action of an item.
Open	Yes	Occurs before Outlook opens an Inspector to display the item.
PropertyChange	No	Occurs when an item property has changed.
Read	No	Occurs when a user opens an item for editing.
Reply	Yes	Occurs before Outlook executes the Reply action of an item.
ReplyAll	Yes	Occurs before Outlook executes the Reply To All action of an item.
Send	Yes	Occurs before Outlook sends the item.
Write	Yes	Occurs before Outlook saves the item in a folder.

To add or modify an event

1. With the form in Design mode, click View Code on the Form Design toolbar.
2. On the Script Editor Script menu, click Event Handler.
3. In the Insert Event Handler list box, double-click the event you want.

With an event procedure, the word item refers to the current Outlook item associated with the form. For example, the following Item_PropertyChangeprocedure sets the value of the Subject field in the item when the value in the Sensitivity drop-down list box in the Message Options dialog box is changed:

Sub Item_PropertyChange(ByVal PropertyName)
    Select Case PropertyName
    Case "Sensitivity"
        Item.Subject = "The sensitivity value has changed."
    End Select
End Sub

Item events are not raised exclusively at the item level in Outlook 2000 and Outlook 2002. A COM Add-in application event handler can also use the WithEvents declaration to raise item-level events and write item event procedure code in VBA or Visual Basic. However, if you want an event's code to travel with the form, you should use item-level event procedures written in VBScript.

The Firing Sequence of Events

When an Outlook form is opened to compose or read an item, events are fired in the sequences described in the following table.

Event	When Fired
Open	A form is opened to compose an item.
Send, Write, Close	An item is sent.
Write, Close	An item is posted.
Write	An item is saved.
Close	An item is closed.
Read, Open	An item is opened in a folder.
Reply	A user replies to an item's sender.
ReplyAll	A user replies to an item's sender and all recipients.
Forward	The newly created item is passed to the procedure after the user selects the item's Forward action.
PropertyChange	One of the item's standard properties is changed.
CustomPropertyChange	One of the item's custom properties is changed.
CustomAction	A user-defined action is initiated.
AttachmentAdd	An attachment is added to a message. This event occurs before the BeforeAttachmentSave event (described below).
AttachmentRead	An attachment is read.
BeforeDelete	This event occurs before an item is deleted. The item must be open in an Inspector for this event to fire.
BeforeAttachmentSave	An attachment is saved in a message. This event occurs after the Send event and before the Write event.
BeforeCheckNames	Recipient names are resolved. This event occurs before the Send event.

An item's events fire whether the item is created through program code (in VBScript or VBA) or through the Outlook user interface.

The order in which Outlook calls event handlers might change depending on other events that can occur. The order might also change in future versions of Outlook.

Creating a New Item

When you create a new item, the Item_Open event is fired. In Outlook, you generally create a new item by opening a form. However, for certain folders, such as the Tasks folder, you can create a new item by clicking the New Item row in the folder. In either case, the Item_Open event is fired.

Sending an Item

When you send an item, the Item_Send event is fired, followed by the Item_Write event and then the Item_Close event.

Posting or Saving an Item

In Outlook, posting an item achieves the same result as saving an item. When you post or save an item, the Item_Write event is fired. An Item_Write event can occur in several situations. It can occur when a Post item is created and the Post button is clicked on the form, when a Contact item is created and the Save And Close button is clicked on the form, or when a Task item is created by clicking the New Item row and the item is then saved by the user clicking outside the New Item row. After the Item_Write event fires, the Item_Close event fires.

Opening an Existing Item

In Outlook, an item can be opened in two ways. If the item exists in a view that allows in-cell editing, simply clicking the item in the view will open it. An existing item can also be opened by double-clicking it and viewing it in a form. In either case, the Item_Read event is fired. If the item is viewed in a form, the Item_Read event is followed by the Item_Open event.

Sometimes you will not want the Item_Open event to fire when an item is opened by simply being clicked in a view with in-cell editing or by another process. Outlook does not provide an Item_New event. The following Item_Open procedure provides an escape hatch for exiting the procedure if the item is not new:

Sub Item_Open
    '1/1/4501 represents a date field with no value
    'If the item is not new, exit sub
    If Item.CreationTime <> #1/1/4501# Then
        Exit Sub
    End If
    'Continue Item_Open code here
End Sub

Closing an Item

When you close an item, the Item_Close event is fired. If changes have been made to the item, when you attempt to close the item, Outlook will ask if you want to save the changes. If you click Yes, the Item_Write event is fired, followed by the Item_Close event.

Preventing Events from Firing

In some cases, you might want to prevent events from occurring. For example, you might want to add code for a Reply event that opens a custom form rather than the default Message form. To achieve this functionality, you must first prevent the default behavior from occurring. To do so, assignFalse to the function value. The actual Event procedure must be declared as a Function procedure instead of a Sub procedure so that you can cancel an Item event with an Item_EventName = False statement.

VBScript and VBA use different syntax to cancel events. In VBScript code, you write the event procedure as a function and set the function value toFalse in order to cancel the event. In VBA or Visual Basic code, the event procedure contains a Cancel argument. Set the Cancel argument to True to cancel the event. The following VBScript example prevents the standard Reply event from occurring in a Mail Message form. Instead, it opens a custom Orders form when the Reply button is clicked.

Function Item_Reply(ByVal Response)
    Item_Reply = False
    Set MyFolder = Application.GetNameSpace("MAPI").GetDefaultFolder(6)
    Set MyItem = MyFolder.Items.Add("IPM.Note.Orders")
    MyItem.To = Item.To
    MyItem.Subject = "RE: " & MyItem.Subject
    MyItem.Display
End Function

The following example shows the same code located in an application-level event procedure in VBA. Unlike Outlook 97 and Outlook 98, Outlook 2002 allows you to raise events at the form level using the correct WithEvents declarations.

Public WithEvents objMailItem As Outlook.MailItem
'Additional code to instantiate objMailItem object as Inspector.CurrentItem
Private Sub objMailItem_ReplyAll(ByVal Response As Object, Cancel As Boolean)
    Cancel = True
    Set objFolder = _
        objApp.GetNameSpace("MAPI").GetDefaultFolder(olFolderInbox)
    Set objItem = objFolder.Items.Add("IPM.Note.Orders")
    objItem.To = objMailItem.To
    objItem.Subject = "RE: " & objItem.Subject
    objItem.Display
End Sub

Form-level events that can be canceled include the following:

• Item_BeforeAttachmentSave
• Item_BeforeCheckNames
• Item_BeforeDelete
• Item_Close
• Item_CustomAction
• Item_Forward
• Item_Open
• Item_Reply
• Item_ReplyAll
• Item_Send
• Item_Write

Many other cancelable events are available at the application level. If you want to respond to application-level events, you must write VBA code in ThisOutlookSession or create a COM Add-in.

The AttachmentAdd Event

The AttachmentAdd event occurs when an attachment is added to an item either through a user action or by a procedure that uses code to add an Attachment object. The following VBScript example checks the size of the item after an embedded attachment has been added and displays a warning if the size exceeds 250,000 bytes:

Sub Item_AttachmentAdd(ByVal NewAttachment)
    Const olByValue = 1
    If NewAttachment.Type = olByValue Then
        If Item.Size > 250000 Then
        MsgBox "Warning: Item size is now " _
            & Item.Size & " bytes.", vbCritical
        End If
    End If
End Sub

The AttachmentRead Event

The AttachmentRead event occurs when an attachment in an item has been opened for reading. If the Attachment object is blocked by the Outlook E-Mail Security Update, the AttachmentRead event occurs after the Security Warning dialog box appears. The following example saves a copy of the attachment into the My Documents folder when the user opens that attachment:

Sub Item_AttachmentRead(ByVal ReadAttachment)
    Const olByValue = 1
    If ReadAttachment.Type = olByValue Then
    IntResponse = MsgBox("Save to c:\my documents?", vbQuestion + vbYesNo)
        If IntResponse = vbYes Then
            ReadAttachment.SaveAsFile "c:\my documents\" _
            & ReadAttachment.DisplayName
        End If
    End If
End Sub

The BeforeAttachmentSave Event

The BeforeAttachmentSave event occurs just after the Item_Send event and just before the Item_Write event. BeforeAttachmentSave always occurs after the AttachmentAdd event. This event is cancelable. If you're sending an item that contains an attachment and you cancel BeforeAttachmentSave, you will also cancel the Item_Write event. The following example prevents the user from saving an .exe file as an attachment:

Function Item_BeforeAttachmentSave(ByVal SaveAttachment)
    Const olByValue = 1
    If SaveAttachment.Type = olByValue Then
        If Instr(SaveAttachment.FileName, ".exe") Then
            MsgBox "Cannot save this attachment!", vbCritical
            Item_BeforeAttachmentSave = False
        End If
    End If
End Function

The BeforeCheckNames Event

The BeforeCheckNames event occurs just before Outlook starts to resolve names in an item's Recipient collection. This event will always fire before the Item_Send event fires. If you cancel the BeforeCheckNames event, the Item_Send event will also be canceled.

The BeforeDelete Event

The BeforeDelete event is new to Outlook 2002. It fulfills the need of developers searching for a means to handle item-level deletes. Be aware that an Inspector for the item must be open in order for the event to fire. If the user clicks the item in a view without opening it and then presses the Delete button, the BeforeDelete event will not fire. For a more reliable means to detect a Delete event in a folder, consider Exchange 5.5 scripting agents or Exchange 2000 event sinks.

The Click Event

The Click event occurs when a user clicks a control such as a command button on a form. You can create as many Click event procedures as there are controls on the form.

The Click event is the only VBScript control event supported in Outlook.

To create a Click event procedure

• In the Script Editor, type the name of the control that you're creating the Click event for, followed by an underscore character (_) and the wordClick.

The following example creates an item and displays the standard Message form when CommandButton1 is clicked on the form:

Sub CommandButton1_Click  
  Set MyItem = Application.CreateItem(0)
  MyItem.Subject = "This is a test."
  MyItem.To = "Davide Atenoux"
  MyItem.Display
End Sub

For more information about the Click event, see "Responding to the Click Event" in Chapter 11.

The Close Event

The Close event occurs when the form associated with the item is being closed. When the Close event fires, the form is still open on the desktop. You can prevent the form from closing by setting the function value to False.

The CustomAction Event

The CustomAction event occurs when a custom action defined in the form occurs through code or a user action. You define custom actions on the Actions page of a form simply by creating a new action. When a CustomAction event is fired, both the name of the custom action being executed and the newly created response item are passed to the CustomAction event. You can prevent the custom action from occurring by setting the function value to False.

You can also use the CustomAction event as a custom method. For example, you can set the CustomAction event to False so that the response form is not loaded. You can then add code to the CustomAction event procedure to accomplish the desired task. For example, you can add code to the CustomAction event to automatically create an announcement message and send it to a distribution list.

The CustomPropertyChange Event

The CustomPropertyChange event occurs when the value in one of the item's user-defined fields changes. Because the field name is passed to the procedure, you can create a Select Case statement to determine which field value has changed.

The Forward Event

The Forward event occurs when the user initiates the Forward action on a form-usually by clicking the Forward button. You can prevent the form from being forwarded by setting the function value to False. In the following example, the Forward event is prevented from firing if the message is confidential:

Function Item_Forward(ByVal MyForwardItem)
    If Item.Sensitivity = 3 Then
        MsgBox "This message is confidential and cannot be forwarded.", _
            vbCritical
        Item_Forward = False
    End If
End Function

The Open Event

The Open event occurs when an Outlook form is opened to compose or read an item. When the Open event occurs, the form is initialized but not yet displayed. You can prevent the form from opening by setting the function value to False.

The example checks the ReceivedTime property of a message to determine whether the form is in Compose or Read mode. If the ReceivedTime property is not set, the Message page is shown. If the ReceivedTime property is set, the (P.2) page is shown so that the person processing the order can view it. Before you run this example, perform the following steps:

1. Add a Message field to the (P.2) page.
2. Add this example code in the Script Editor, and then click Publish Form As on the Form Design toolbar.
3. In the Display Name text box, type TestEvent, and then publish the form to the Personal Forms Library.
4. On the Outlook File menu, select New and then click Choose Form.
5. Select the TestEvent form in the Personal Forms Library. Notice that the Message page is shown.
6. Address the form to yourself, and click Send.
7. Double-click the sent item in your Inbox. Notice that the (P.2) page is shown.

   Function Item_Open() 
       If Item.ReceivedTime <> "1/1/4501" Then
           Item.GetInspector.SetCurrentFormPage("P.2")\
           Item.GetInspector.HideFormPage("Message")
       Else 
           Item.GetInspector.SetCurrentFormPage("Message")
           Item.GetInspector.HideFormPage("P.2")
       End If
   End Function 

Note in the example that the If statement checks to see whether the ReceivedTime field is set to 1/1/4501. In Outlook, Date fields that display None will return a value of 1/1/4501.

The PropertyChange Event

The PropertyChange event occurs when one of the item's standard fields (such as Subject, To, Importance, or Sensitivity) changes. Because the field name is passed to the procedure, you can use the Select Case statement to determine which field value has changed.

In the following example, a message box shows the values of the Importance and Sensitivity fields that have changed. To see how it works, add the following code to the Script Editor. Then exit Design mode and change the values in the Importance and Sensitivity drop-down list boxes in the Message Options dialog box.

Sub Item_PropertyChange(ByVal FieldName)
    Select Case FieldName
    Case "Importance"
        MsgBox "The value of the Importance field is " _
            & Item.Importance & "."
    Case "Sensitivity"
        MsgBox "The value of the Sensitivity field is " _
            & Item.Sensitivity & "."
    End Select
End Sub

The Read Event

The Read event occurs when a user opens an existing Outlook item for editing. The Read event differs from the Open event in that it occurs whenever a user selects the item in a view that supports in-cell editing. The Read event also occurs when the item is being opened in an Inspector. This event is fired before the Open event. In the following example, the value of the Date Opened user-defined field is set when the submitted item is first opened.

Before you run this example, perform the following steps:

1. Click New in the Field Chooser, and then type Date Opened in the Name text box.
2. In the Type drop-down list box, click Date/Time and then click OK.
3. Drag the Date Opened field to the Message page, and then click Edit Read Page on the Form Design toolbar.
4. Resize the Message control to make room for the Date Opened control, and then drag the Date Opened field from the Field Chooser to the Read page.
5. Click Publish Form As, and publish the form in your Personal Forms Library.
6. On the Outlook File menu, select New, click Choose Form, and then select the form. Address the form to yourself, and click Send.
7. Double-click the sent item in your Inbox. Notice that the current date is now in the Date Opened field.

   Function Item_Read()
       If UserProperties("Date Opened") = "1/1/4501" Then
           UserProperties("Date Opened") = Now
       End If
   End Function 

The Reply Event

The Reply event occurs when a user clicks the Reply button on the form. When this event occurs, the newly created Reply item is passed to the procedure. You can prevent the item from being sent by setting the function value to False. The following example prevents the standard Reply event from occurring and creates an item with a custom Task form. The code then copies the values from the item's Subject and To fields to the Task Response item.

Function Item_Reply(ByVal Response)
    Item_Reply = False
    Set MyFolder = Application.GetNameSpace("MAPI").GetDefaultFolder(13)
    Set MyItem = MyFolder.Items.Add("IPM.Task.Test")
    MyItem.To = Item.To
    MyItem.Subject = "RE: " & MyItem.Subject
    MyItem.Display
End Function

The ReplyAll Event

The ReplyAll event occurs when a user clicks the Reply To All button on a form. When a user clicks Reply To All, the response is sent to the sender as well as to all recipients in the To and Cc boxes on the form. You can prevent the item from being sent by setting the function value to False. The following example reminds the user that he or she is replying to all the original recipients of an item and allows the user to cancel the action:

Function Item_ReplyAll (ByVal MyResponse)
    MyResult = MsgBox("Do you really want to send this reply to all " _
        & "the recipients in the To and Cc boxes? ", _
        vbQuestion + vbYesNo + vbDefaultButton2, "Flame Protector")
    If MyResult = vbYes Then
        Item_ReplyAll = True
    Else
        Item_ReplyAll = False
    End If
End Function

The Send Event

The Send event occurs when a user sends an item. You can prevent the item from being sent by setting the function value to False. If you prevent this event from occurring, the form remains open. The following example automatically sets the expiration date on the item when it is sent:

Function Item_Send()
    Item.ExpiryTime = Date + 7
End Function

The Write Event

The Write event occurs when the user sends, posts, or saves an item. You can prevent an item from being saved by setting the function value to False. The following code prevents a form from changing its message class after either the HideFormPage or ShowFormPage method is called:

Function Item_Write
    Set objForm = Item.FormDescription
    Item.MessageClass = objForm.MessageClass
End Function