More Outlook Resource Sites

Microsoft Developer Network (MSDN)

FAQs and other general resources

share code 21-Feb-2019 21:14

Looking for help with Outlook programming projects — VSTO, add-ins, VBA, custom Outlook forms, etc.? You′ve come to the right place!

NEW! >> Subscribe to this site via RSS. For more RSS options, see the complete list of feeds on our main news page.

Code level: beginner    Code area: Outlook Expert Techniques Printer Friendly Version
Title: Query Outlook Custom Form Properties From Excel
Description: Here's a short VBA macro that will retrieve Outlook custom form properties, their number, and their content and put it all into an Excel spreadsheet.
Date: 15-Nov-2005  04:03
Code level: beginner
Code area: Outlook Expert Techniques
Posted by: Bruce Ferman
This message is displayed as VB.NET
 'I created the code below to extract custom property values from Outlook forms directly into an Excel spreadsheet.
'You could use similar VBA code in MS Access or any other office product.  The code runs pretty slow, but it works.

'This code can easily be updated to grab the property contents of more than just the first form in
'the selected Outlook folder.

'In order to use this code, you must set the Microsoft Outlook 11.0 Object as a reference using
'the Tools/References menu item in the Visual Basic menubar in Excel.  You must also have
'Outlook installed on the computer running this code.

Sub Get_Properties()
Dim Outlook As Outlook.Application
Dim Index As Long
Dim Sheet As Worksheet

    'Instantiate the Outlook Application object
    Set Outlook = New Outlook.Application
    Set Sheet = ThisWorkbook.Worksheets(1)

    'Iterate through all of the custom properties of the
    'first item in the selected Outlook folder.
    With Outlook.Explorers(1).CurrentFolder
        For Index = 1 To .Items(1).UserProperties.Count
            'The number of the custom property
            Sheet.Cells(Index, 1) = Index
            'The property name
            Sheet.Cells(Index, 2) = .Items(1).UserProperties(Index).Name
            'The property value
            Sheet.Cells(Index, 3) = .Items(1).UserProperties(Index).Value
        Next Index
    End With

    Set Outlook = Nothing
End Sub
All 5comments
Page [ 1  
  15-Nov-2005  12:25   
A couple of refinements you might want to consider:

-- Add an On Error Resume Next statement before the For Each ... Next loop. I've seen some custom properties return errors when accessed this way.

-- Instead of Outlook.Explorers(1).CurrentFolder, use OUtlook.ActiveExplorer.CurrentFolder.
  15-Nov-2005  14:20   
Thanks for the error catching tip Sue. I've updated the code.
  15-Nov-2005  14:23   
'I created the code below to extract custom property values from Outlook forms directly into an Excel spreadsheet.
'You could use similar VBA code in MS Access or any other office product. The code runs pretty slow, but it works.

'This code can easily be updated to grab the property contents of more than just the first form in
'the selected Outlook folder.

'In order to use this code, you must set the Microsoft Outlook 11.0 Object as a reference using
'the Tools/References menu item in the Visual Basic menubar in Excel. You must also have
'Outlook installed on the computer running this code.

Sub Get_Properties()
Dim Outlook As Outlook.Application
Dim Index As Long
Dim Sheet As Worksheet

    'Instantiate the Outlook Application object
    Set Outlook = New Outlook.Application
    Set Sheet = ThisWorkbook.ActiveSheet

    'Iterate through all of the custom properties of the
    'first item in the selected Outlook folder.
    With Outlook.ActiveExplorer.CurrentFolder
        'Some properties may raise an error when they are accessed,
        'so we'll catch these and deal with them in the code below.
        On Error Resume Next
        
        'Make sure there's at least one item in the selected folder
        If .Items.Count > 0 Then
            'Ensure there's at least one custom property in the first item
            If .Items(1).UserProperties.Count > 0 Then
                For Index = 1 To .Items(1).UserProperties.Count
                    'The number of the custom property
                    Sheet.Cells(Index, 1) = Index
                    
                    'The property name
                    Sheet.Cells(Index, 2) = .Items(1).UserProperties(Index).Name
                    'If accessing the property triggered an error, let the user know what happened
                    If Err.Number > 0 Then Sheet.Cells(Index, 2) = "Error Accessing Property"
                    
                    'The property value
                    Sheet.Cells(Index, 3) = .Items(1).UserProperties(Index).Value
                    'If accessing the property triggered an error, let the user know what happened
                    If Err.Number > 0 Then Sheet.Cells(Index, 3) = "Error Accessing Property"
                    
                    'Clear any error that may have been raised
                    Err.Clear
                Next Index
            End If
        End If
    End With

    Set Outlook = Nothing
End Sub
  09-Oct-2007  22:59   
Bruce

Thanks for the code. You mention that the code can be looped to extract multiple forms sitting in the selected Outlook folder. As a VBA novice how is this done (my IT department did not know!)
  14-Oct-2007  10:18   
Ewan, there is an IF THEN block right under the comment "'Make sure there's at least one item in the selected folder" You can replace that bit of code with the following:
'Make sure there's at least one item in the selected folder
If .Items.Count > 0 Then
    Dim nFormCount as Integer
    
    For nFormCount = 1 to .Items.Count
        'Ensure there's at least one custom property in the first item
        If .Items(nFormCount).UserProperties.Count > 0 Then
            For Index = 1 To .Items(nFormCount).UserProperties.Count
                'The number of the custom property
                Sheet.Cells(Index, 1) = Index
                
                'The property name
                Sheet.Cells(Index, 2) = .Items(nFormCount).UserProperties(Index).Name
                'If accessing the property triggered an error, let the user know what happened
                If Err.Number > 0 Then Sheet.Cells(Index, 2) = "Error Accessing Property"
                
                'The property value
                Sheet.Cells(Index, 3) = .Items(nFormCount).UserProperties(Index).Value
                'If accessing the property triggered an error, let the user know what happened
                If Err.Number > 0 Then Sheet.Cells(Index, 3) = "Error Accessing Property"
                
                'Clear any error that may have been raised
                Err.Clear
            Next Index
        End If
    Next nFormCount
End If


I have also written an Outlook report designer program using VBA in Excel. This program allows you to create Outlook Forms reports without any knowledge of VBA.
 
Page [ 1