Export Outlook Notes to Excel

The following code exports Notes to Excel from Outlook.

The code is late bound, so first we need to place the following enumeration into a standard Excel module when using this code.

Public Enum OlDefaultFolders
  olFolderCalendar = 9
  olFolderConflicts = 19
  olFolderContacts = 10
  olFolderDeletedItems = 3
  olFolderDrafts = 16
  olFolderInbox = 6
  olFolderJournal = 11
  olFolderJunk = 23
  olFolderLocalFailures = 21
  olFolderNotes = 12
  olFolderOutbox = 4
  olFolderSentMail = 5
  olFolderServerFailures = 22
  olFolderSyncIssues = 20
  olFolderTasks = 13
  olPublicFoldersAllPublicFolders = 18
End Enum

This code should be placed at the top of a standard module. See Where do I paste the code that I want to use in my workbook if you need to know where to put this code.

Export Notes

This code will return notes in the local default Notes folder and write them to an array. You can then put them into a listbox, or dump them onto a worksheet.

An optional header row is also provided, so you can see what each column of values represents. It also makes it easier for pasting onto a worksheet when you have the header row as part of the array. I chose to grab every possible property of the NoteItem Object where possible.

Function ExportNotes(Optional headerRow As Boolean = False) As String()

Dim olApp As Object ' Outlook.Application
Dim olNS As Object ' Outlook.Namespace
Dim notesFolderItems As Object ' Outlook.items
Dim folderItem As Object
Dim note As Object ' Outlook.NoteItem
Dim tempString() As String
Dim i As Long
Dim numRows As Long
Dim startRow As Long

  Set olApp = GetOutlookApp
  Set olNS = GetNS(olApp)
  Set notesFolderItems = GetItems(olNS, olFolderNotes)

  ' if calling procedure wants header row
  If headerRow Then
    startRow = 1
  Else
    startRow = 0
  End If

  numRows = notesFolderItems.count

  ' resize array
  ReDim tempString(1 To (numRows + startRow), 1 To 11)

  ' loop through folder items
  For i = 1 To numRows
    Set folderItem = notesFolderItems.item(i)

    If IsNote(folderItem) Then
      Set note = folderItem
    End If

    With note
      tempString(i + startRow, 1) = .Body
      tempString(i + startRow, 2) = .Categories
      tempString(i + startRow, 3) = .Color
      tempString(i + startRow, 4) = .CreationTime
      tempString(i + startRow, 5) = .Height
      tempString(i + startRow, 6) = .LastModificationTime
      tempString(i + startRow, 7) = .Left
      tempString(i + startRow, 8 ) = .size
      tempString(i + startRow, 9) = .subject
      tempString(i + startRow, 10) = .Top
      tempString(i + startRow, 11) = .Width
    End With

  Next i

  ' first row of array should be header values
  If headerRow Then
    tempString(1, 1) = "Body"
    tempString(1, 2) = "Categories"
    tempString(1, 3) = "Color"
    tempString(1, 4) = "CreationTime"
    tempString(1, 5) = "Height"
    tempString(1, 6) = "LastModificationTime"
    tempString(1, 7) = "Left"
    tempString(1, 8 ) = "Size"
    tempString(1, 9) = "Subject"
    tempString(1, 10) = "Top"
    tempString(1, 11) = "Width"
  End If

  ExportNotes = tempString
End Function

Helper Functions

Visit Utility Functions for IsNote, GetNS, GetOutlookApp, GetItems.

Sample Usage

This sample procedure gets the Notes from the default Notes folder and pastes them into Excel, along with header row:

Sub GetNoteInfo()

Dim results() As String

  ' get contacts
  results = ExportNotes(True)

  ' paste onto worksheet
  Range(cells(1, 1), cells(UBound(results), UBound(results, 2))).value = results

End Sub

The sample workbook includes the code and a userform so you can see how the results would be put into a listbox.

Download workbook for Excel 2003