Exporting Outlook Messages to Excel


I’m writing this post primarily for Sen who in a comment to another post asked

How do i Export e-mail messages with the subject, received date & time from Outlook to Excel with the sender address?

The simplest way to carry out this is to use Outlook’s built-in export capability. Using it you can export to a .csv (comma separated values) file which you then open with Excel. However, there are a couple of drawbacks to using export. Export doesn’t allow you to pick specific messages to export so you’ll have to export an entire folder at a time. It also doesn’t allow you to limit the export to certain fields. Instead it will export everything and you’ll have to delete the columns you don’t want.

A better, but more complicated, approach is to use a macro to do the export. With a macro you can select the messages you want, export only the fields you want, and it can write directly to Excel. The code for doing this is both simple and straightforward. It creates a spreadsheet, loops though the selected messages writing the fields you want to that spreadsheet, then saves and closes the spreadsheet. This solution should work in Outlook 2007 and later.

Adding the code to Outlook.

  1. Start Outlook
  2. Press ALT+F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. If not already expanded, expand Modules
  5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Click the diskette icon on the toolbar to save the changes
  8. Close the VB Editor
Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.VERSION, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function
 

Using the Code.

  1. With Outlook open select a folder that contains emails.
  2. Run the macro.
  3. When prompted enter a filename to save the export to. You can cancel the export by not entering anything.
  4. The macro will display a dialog-box when it’s finished. The dialog-box includes a count of the number of messages exported.

Notes.

  • This code can easily be modified to export a different set of fields. To do that, change the headings written to the spreadsheet (lines 15-17) and the message fields (lines 25-27).
  • If you don’t want the macro to prompt for a filename each time, then you can change line 7 to strFilename = “Path_and_File_Name”

Revisions.

1 2 3 4 5 6 7 8 9 10
11 12 13 14 15 16 17 18 19 20
21 22 23 24 25 26 27 28 29 30
31 32 33 34 35 36 37 38 39 40
41 42 43 44 45 46 47 48 49  

This revision is for Sen who asked for a way to use the code to export a set list of folders each day without having to manually open and export each one individually. To accomplish that I’ve added a controller which calls the export routine for each folder in Sen’s list. All Sen needs to do is edit the ExportController subroutine by adding a line for each folder he wants to export. Each call will pass the filename and path to the spreadsheet the code will export the messages to and the path to the source Outlook folder it will export from.

In case you aren’t familiar with Outlook folder paths, here’s an explanation of how to figure them out. A folder path in Outlook is essentially the same as a folder path in the file system. The one difference being that Outlook folder paths do not include a drive letter. The path to a folder is a list of all the folders from the root to the target folder with each folder name separated from the preceding folder name by a backslash (i.e. \). Consider the following folder structure:

Mailbox - Doe, John
    - Calendar
    - Inbox
    - Tasks
Personal Folders
    + Marketing
        + Proposals
        + Reviews
    + Projects
        + Project 1
        + Project 2

The path to “Inbox” is “Mailbox – Doe, John\Inbox”.
The path to “Reviews” is “Personal Folders\Marketing\Reviews”.
The path to “Project 1” is “Personal Folders\Projects\Project 1”.

Const MACRO_NAME = "Export Messages to Excel (Rev 1)"

Sub ExportController()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folders\Folder1"
    ExportMessagesToExcel "C:\Folder2.xlsx", "Personal Folders\Folder2"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, strFolderPath As String)
    Dim olkMsg As Object, _
        olkFld As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer
    If strFilename <> "" Then
        If strFolderPath <> "" Then
            Set olkFld = OpenOutlookFolder(strFolderPath)
            If TypeName(olkFld) <> "Nothing" Then
                intVersion = GetOutlookVersion()
                Set excApp = CreateObject("Excel.Application")
                Set excWkb = excApp.Workbooks.Add()
                Set excWks = excWkb.ActiveSheet
                'Write Excel Column Headers
                With excWks
                    .Cells(1, 1) = "Subject"
                    .Cells(1, 2) = "Received"
                    .Cells(1, 3) = "Sender"
                End With
                intRow = 2
                'Write messages to spreadsheet
                For Each olkMsg In olkFld.Items
                    'Only export messages, not receipts or appointment requests, etc.
                    If olkMsg.Class = olMail Then
                        'Add a row for each field in the message you want to export
                        excWks.Cells(intRow, 1) = olkMsg.Subject
                        excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                        excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                        intRow = intRow + 1
                    End If
                Next
                Set olkMsg = Nothing
                excWkb.SaveAs strFilename
                excWkb.Close
            Else
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            End If
        Else
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    Set olkMsg = Nothing
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.VERSION, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function
 

This revision is for Canny who asked for a means of exporting to an existing spreadsheet instead of creating a new one each time, and for the exported items to be linked back to the originals in Outlook. This version implements both of these features. The first time Canny runs the export the code will create the spreadsheet if it doesn’t exist. On each subsequent export it will either overwrite the first sheet in the workbook or add a sheet. The behavior is controlled by changing the value of EXPORT_NEWSHEET (True = add a new sheet each time, False = overwrite the first sheet in the book each time).

If the hyperlinks from the items in the spreadsheet to the original items in Outlook don’t work, then it’s because you don’t have the Outlook protocol handler installed. Outlook 2007 and later doesn’t automatically install the Outlook protocol handler (which allows linking to Outlook items and folders) like earlier versions of Outlook did. You have to edit the registry and add an entry to enable it. Here’s a link to a page with details and a .reg file you can download to speed up the process. Pay attention to the instructions on the page about editing the .reg file. Also, if you have Outlook 2010 you’ll have to make another edit, changing the references to Office12 to Office14.

'On the next line change the value to True if you want each export to go on a new sheet.
Const EXPORT_NEWSHEET = False
Const MACRO_NAME = "Export Messages to Excel (Rev 2)"

Sub ExportController()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folders\Folder1"
    ExportMessagesToExcel "C:\Folder2.xlsx", "Personal Folders\Folder2"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, strFolderPath As String)
    Dim olkMsg As Object, _
        olkFld As Outlook.MAPIFolder, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVer As Integer
    If strFilename <> "" Then
        If strFolderPath <> "" Then
            Set olkFld = OpenOutlookFolder(strFolderPath)
            If TypeName(olkFld) <> "Nothing" Then
                intVer = GetOutlookVersion()
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                Set excApp = CreateObject("Excel.Application")
                If objFSO.FileExists(strFilename) Then
                    Set excWkb = excApp.Workbooks.Open(strFilename)
                    If EXPORT_NEWSHEET Then
                        Set excWks = excWkb.Worksheets.Add()
                        excWks.Name = Format(Date, "m-dd-yy") 
                    Else
                        Set excWks = excWkb.Worksheets(1)
                    End If
                Else
                    Set excWkb = excApp.Workbooks.Add()
                    Set excWks = excWkb.Worksheets(1)
                End If
                'Write Excel Column Headers
                With excWks
                    .Cells(1, 1) = "Subject"
                    .Cells(1, 2) = "Received"
                    .Cells(1, 3) = "Sender"
                End With
                intRow = 2
                'Write messages to spreadsheet
                For Each olkMsg In olkFld.Items
                    If olkMsg.Class = olMail Then
                        'Add a row for each field in the message you want to export
                        excWks.Cells(intRow, 1) = olkMsg.Subject
                        excWks.Hyperlinks.Add Anchor:=excWks.Range("A" & intRow), Address:="outlook:" & olkMsg.EntryID, TextToDisplay:=IIf(olkMsg.Subject = "", "Blank", olkMsg.Subject)
                        excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                        excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVer)
                        intRow = intRow + 1
                    End If
                Next
                Set olkMsg = Nothing
            Else
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            End If
        Else
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    If objFSO.FileExists(strFilename) Then
        excWkb.Save
    Else
        excWkb.SaveAs strFilename
    End If
    excWkb.Close True
    Set olkMsg = Nothing
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

This revision is for Dan who asked “Is there a way using the original version of your code to include emails in subfolders of the folder selected?” Of course the answer is “yes”. In order to handle sub-folders I had to move code from the main routine (i.e. ExportMessagesToExcel) to a new subroutine called ProcessFolder and add a recursive call (i.e. have that routine call itself once for each subfolder). This allows the code to process the currently selected folder and all its subfolders. Other than that this version is identical to the original version.

Const MACRO_NAME = "Export Messages to Excel (Rev 3)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVersion As Integer, _
    intMessages As Integer

Sub ExportMessagesToExcel()
    Dim strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intMessages = 0
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        ProcessFolder Application.ActiveExplorer.CurrentFolder
        excWkb.SaveAs strFilename
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intMessages & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, olkSub As Outlook.MAPIFolder, intRow As Integer
    intRow = excWks.UsedRange.Rows.Count
    intRow = intRow + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(intRow, 1) = olkMsg.Subject
            excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
            excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
            intRow = intRow + 1
            intMessages = intMessages + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkssub = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.VERSION, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Kris Fong and chirag both of whom asked for a modification to export based on a date range. To accomplish that I’ve added a second InputBox that takes a date range in the form mm/dd/yyyy to mm/dd/yyyy. The code tacks on “12:00am” to the starting date and “11:59pm” to the ending date, which it then uses to filter the folder. The code in this revision is based on the code in the original post, so it doesn’t handle the modifications from the other revisions.

Const MACRO_NAME = "Export Messages to Excel (Rev 4)"

Sub ExportMessagesToExcel()
    Dim olkLst As Object, _
        olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        strDateRange As String, _
        arrTemp As Variant, _
        datStart As Date, _
        datEnd As Date
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
        arrTemp = Split(strDateRange, "to")
        datStart = IIF(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
        datEnd = IIF(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        intRow = 2
        'Write messages to spreadsheet
        Set olkLst = Application.ActiveExplorer.CurrentFolder.Items.Restrict("[ReceivedTime] >= '" & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set olkLst = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Christy who asked for the ability to export the “To” and “Attachment” fields (i.e. the names of the attachments not the actual attachments themselves) in addition to the fields the script is already exporting.

Const MACRO_NAME = "Export Messages to Excel (Rev 5)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        olkAtt As Outlook.Attachment, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        strAtt As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "To"
            .Cells(1, 5) = "Attachments"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                excWks.Cells(intRow, 4) = olkMsg.To
                strAtt = ""
                For Each olkAtt In olkMsg.Attachments
                    If Not IsHiddenAttachment(olkAtt) Then
                        strAtt = strAtt & olkAtt.FileName & ", "
                    End If
                Next
                If strAtt <> "" Then
                    strAtt = Left(strAtt, Len(strAtt) - 2)
                End If
                excWks.Cells(intRow, 5) = strAtt
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for chirag who asked for two modifications: working off of a date range (the same as in revision 4) and saving the attachments of the items that fall into the date range to a folder on the hard drive. Be sure to edit the EXPORT_FOLDER constant. It contains the path to the folder the attachments will be saved to.

Const MACRO_NAME = "Export Messages to Excel (Rev 6)"

Sub ExportMessagesToExcel()
    'Edit the path on the next line.  This is the folder the code will save the attachments to.  Make sure that the path ends with a \
    Const EXPORT_FOLDER = "c:\SomeFolder\"
    Dim olkLst As Object, _
        olkMsg As Object, _
        olkAtt As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        strDateRange As String, _
        arrTemp As Variant, _
        datStart As Date, _
        datEnd As Date
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
        arrTemp = Split(strDateRange, "to")
        datStart = IIF(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
        datEnd = IIF(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        intRow = 2
        'Write messages to spreadsheet
        Set olkLst = Application.ActiveExplorer.CurrentFolder.Items.Restrict("[ReceivedTime] >= '" & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                For Each olkAtt In olkMsg.Attachments
                    If Not IsHiddenAttachment(olkAtt) Then olkAtt.SaveAsFile EXPORT_FOLDER & olkAtt.FileName
                Next
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set olkLst = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This rev is for Nilesh who asked for a version which appends data to an existing sheet instead of creating a new sheet or overwriting an existing sheet. The code will always write to the same workbook and sheet, and will append the exported data to the bottom of the sheet. Other than that this rev is identical to the code in the original post.

'On the next line edit the path to the spreadsheet you want to export to
Const WORKBOOK_PATH = "c:\Users\David\Documents\Sample.xlsx"
'On the next line edit the name of the sheet you want to export to
Const SHEET_NAME = "Sheet1"
Const MACRO_NAME = "Export Messages to Excel (Rev 7)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intExp As Integer, _
        intVersion As Integer
    intVersion = GetOutlookVersion()
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(WORKBOOK_PATH)
    Set excWks = excWkb.Worksheets(SHEET_NAME)
    intRow = excWks.UsedRange.Rows.Count + 1
    'Write messages to spreadsheet
    For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(intRow, 1) = olkMsg.Subject
            excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
            excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
            intRow = intRow + 1
            intExp = intExp + 1
        End If
    Next
    Set olkMsg = Nothing
    excWkb.Close True
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intExp & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.VERSION, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Alex who has a special situation. Alex receives messages with an HTML table in the body. In addition to the subject, sender, etc., Alex needs to export the table into the spreadsheet. To accomplish this I’ve added a function that uses regular expressions to parse the table out of the message. I then push the table into the clipboard and paste it into the spreadsheet. The code that copies the HTML to the clipboard is not mine. It comes from this Microsoft support article.

'On the next line edit the path to the spreadsheet you want to export to
Const WORKBOOK_PATH = "c:\Users\David\Documents\Sample.xlsx"
'On the next line edit the name of the sheet you want to export to
Const SHEET_NAME = "Sheet1"
Const MACRO_NAME = "Export Messages to Excel (Rev 8)"

Private Declare Function CloseClipboard Lib "user32" () As Long
Private Declare Function OpenClipboard Lib "user32" (ByVal hwnd As Long) _
   As Long
Private Declare Function GlobalAlloc Lib "kernel32" ( _
   ByVal wFlags As Long, ByVal dwBytes As Long) As Long
Private Declare Function SetClipboardData Lib "user32" ( _
   ByVal wFormat As Long, ByVal hMem As Long) As Long
Private Declare Function EmptyClipboard Lib "user32" () As Long
Private Declare Function RegisterClipboardFormat Lib "user32" Alias _
   "RegisterClipboardFormatA" (ByVal lpString As String) As Long
Private Declare Function GlobalLock Lib "kernel32" (ByVal hMem As Long) _
   As Long
Private Declare Function GlobalUnlock Lib "kernel32" ( _
   ByVal hMem As Long) As Long
Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" ( _
   pDest As Any, pSource As Any, ByVal cbLength As Long)
Private Declare Function GetClipboardData Lib "user32" ( _
   ByVal wFormat As Long) As Long
Private Declare Function lstrlen Lib "kernel32" Alias "lstrlenA" ( _
   ByVal lpData As Long) As Long

Private Const m_sDescription = _
                  "Version:1.0" & vbCrLf & _
                  "StartHTML:aaaaaaaaaa" & vbCrLf & _
                  "EndHTML:bbbbbbbbbb" & vbCrLf & _
                  "StartFragment:cccccccccc" & vbCrLf & _
                  "EndFragment:dddddddddd" & vbCrLf
                  
Private m_cfHTMLClipFormat As Long

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intExp As Integer, _
        intVersion As Integer
    intVersion = GetOutlookVersion()
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(WORKBOOK_PATH)
    Set excWks = excWkb.Worksheets(SHEET_NAME)
    excApp.Visible = True
    intRow = excWks.UsedRange.Rows.Count + 1
    'Write messages to spreadsheet
    For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(intRow, 1) = olkMsg.Subject
            excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
            excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
            PutHTMLClipboard GetTable(olkMsg.HTMLBody)
            excWks.Range("D" & intRow).Select
            excWks.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False, NoHTMLFormatting:=False
            intRow = intRow + 1
            intExp = intExp + 1
        End If
    Next
    Set olkMsg = Nothing
    excWkb.Close True
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intExp & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function GetTable(strBody As String) As String
    Dim objRegEx As Object, colMatches As Object, varMatch As Variant
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = "<table.*?>(.*?)</table>"
        Set colMatches = .Execute(strBody)
    End With
    For Each varMatch In colMatches
        GetTable = varMatch
    Next
    Set objRegEx = Nothing
    Set colMatches = Nothing
End Function

Function RegisterCF() As Long
   'Register the HTML clipboard format
   If (m_cfHTMLClipFormat = 0) Then
      m_cfHTMLClipFormat = RegisterClipboardFormat("HTML Format")
   End If
   RegisterCF = m_cfHTMLClipFormat
   
End Function

Public Sub PutHTMLClipboard(sHtmlFragment As String, _
   Optional sContextStart As String = "<HTML><BODY>", _
   Optional sContextEnd As String = "</BODY></HTML>")
   
   Dim sData As String
   
   If RegisterCF = 0 Then Exit Sub
   
   'Add the starting and ending tags for the HTML fragment
   sContextStart = sContextStart & "<!--StartFragment -->"
   sContextEnd = "<!--EndFragment -->" & sContextEnd
   
   'Build the HTML given the description, the fragment and the context.
   'And, replace the offset place holders in the description with values
   'for the offsets of StartHMTL, EndHTML, StartFragment and EndFragment.
   sData = m_sDescription & sContextStart & sHtmlFragment & sContextEnd
   sData = Replace(sData, "aaaaaaaaaa", _
                   Format(Len(m_sDescription), "0000000000"))
   sData = Replace(sData, "bbbbbbbbbb", Format(Len(sData), "0000000000"))
   sData = Replace(sData, "cccccccccc", Format(Len(m_sDescription & _
                   sContextStart), "0000000000"))
   sData = Replace(sData, "dddddddddd", Format(Len(m_sDescription & _
                   sContextStart & sHtmlFragment), "0000000000"))

   'Add the HTML code to the clipboard
   If CBool(OpenClipboard(0)) Then
   
      Dim hMemHandle As Long, lpData As Long
      
      hMemHandle = GlobalAlloc(0, Len(sData) + 10)
      
      If CBool(hMemHandle) Then
               
         lpData = GlobalLock(hMemHandle)
         If lpData <> 0 Then
            
            CopyMemory ByVal lpData, ByVal sData, Len(sData)
            GlobalUnlock hMemHandle
            EmptyClipboard
            SetClipboardData m_cfHTMLClipFormat, hMemHandle
                        
         End If
      
      End If
   
      Call CloseClipboard
   End If

End Sub

I created this revision for Chris who posted a comment asking for a way to

  • Create a new workbook each time the script is run
  • Extract the entire mailbox to the workbook
  • Create a new worksheet for each store (in Outlook a store is an either an Exchange mailbox or PST file)
  • Dump all folders in the store to the same sheet

With everything in a given store going to the same worksheet I thought it might be handy to add a column that shows which folder the item is in. Hopefully that will save Chris some confusion. The rest of the code remains unchanged.

Const MACRO_NAME = "Export Messages to Excel (Rev 9)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVersion As Integer, _
    intMessages As Integer, _
    lngRow As Long

Sub ExportMessagesToExcel()
    Dim strFilename As String, olkSto As Outlook.Store
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intMessages = 0
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        For Each olkSto In Session.Stores
            Set excWks = excWkb.Worksheets.Add()
            excWks.Name = olkSto.DisplayName
            'Write Excel Column Headers
            With excWks
                .Cells(1, 1) = "Folder"
                .Cells(1, 2) = "Subject"
                .Cells(1, 3) = "Received"
                .Cells(1, 4) = "Sender"
            End With
            lngRow = 2
            ProcessFolder olkSto.GetRootFolder()
        Next
        excWkb.SaveAs strFilename
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intMessages & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, olkSub As Outlook.MAPIFolder
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(lngRow, 1) = olkFld.Name
            excWks.Cells(lngRow, 2) = olkMsg.Subject
            excWks.Cells(lngRow, 3) = olkMsg.ReceivedTime
            excWks.Cells(lngRow, 4) = GetSMTPAddress(olkMsg, intVersion)
            lngRow = lngRow + 1
            intMessages = intMessages + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkSub = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Christian who asked if I could modify the code in revision 4 to use the current date in lieu of a date range. Revision 4 prompts for starting and ending dates and then extracts all the messages in the current folder that arrived between those two dates. Instead of prompting for a date range this revision gets today’s date and uses it.

Const MACRO_NAME = "Export Messages to Excel (Rev 10)"

Sub ExportMessagesToExcel()
    Dim olkLst As Object, _
        olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        datStart As Date, _
        datEnd As Date
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        datStart = Date & " 12:00am"
        datEnd = Date & " 11:59pm"
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        intRow = 2
        'Write messages to spreadsheet
        Set olkLst = Application.ActiveExplorer.CurrentFolder.Items.Restrict("[ReceivedTime] >= '" & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set olkLst = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for nickyzzz who asked for a solution that exports voting responses. Nickyzzz explained that she routinely sends messages with a document attached asking the recipient to review the document and accept/reject it. She’s using Outlook’s voting buttons to handle the response portion. The code here is that from the original post with a “Vote” column added. For each message the code places the value of the voting button the recipient clicked (i.e. VotingResponse) in that column.

Const MACRO_NAME = "Export Messages to Excel (Rev 11)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "Vote"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                excWks.Cells(intRow, 4) = olkMsg.VotingResponse
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for Gur Pollante who asked for a version that melds revisions 1 and 7. What Gur needs is a version that automates exporting the same folder every day (like revision 1) while appending the data to an existing spreadsheet (like revision 7). He also wants to move the exported message from the source folder to a history folder once the export is complete.

Const MACRO_NAME = "Export Messages to Excel (Rev 12)"

'On the next line edit the path as needed
Const HIST_PATH = "Personal Folder - HISTORY"

Sub ExportController()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folder\Daily"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, strFolderPath As String)
    Dim olkMsg As Object, _
        olkFld As Object, _
        olkHst As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        intCnt As Integer
    If strFilename <> "" Then
        If strFolderPath <> "" Then
            Set olkFld = OpenOutlookFolder(strFolderPath)
            Set olkHst = OpenOutlookFolder(HIST_PATH)
            If TypeName(olkFld) <> "Nothing" Then
                intVersion = GetOutlookVersion()
                Set excApp = CreateObject("Excel.Application")
                Set excWkb = excApp.Workbooks.Open(strFilename)
                Set excWks = excWkb.ActiveSheet
                'Write Excel Column Headers
                With excWks
                    .Cells(1, 1) = "Subject"
                    .Cells(1, 2) = "From"
                    .Cells(1, 3) = "Date"
                End With
                intRow = excWks.UsedRange.Rows.Count + 1
                'Write messages to spreadsheet
                For intCnt = olkFld.Items.Count To 1 Step -1
                    Set olkMsg = olkFld.Items(intCnt)
                    'Only export messages, not receipts or appointment requests, etc.
                    If olkMsg.Class = olMail Then
                        'Add a row for each field in the message you want to export
                        excWks.Cells(intRow, 1) = olkMsg.Subject
                        excWks.Cells(intRow, 2) = GetSMTPAddress(olkMsg, intVersion)
                        excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                        intRow = intRow + 1
                        olkMsg.Move olkHst
                    End If
                Next
                Set olkMsg = Nothing
                excWkb.Close True
            Else
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            End If
        Else
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    Set olkMsg = Nothing
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is also for Gur Pollante who asked if I could make to modifications to Rev 12. First, he wants to append new data beginning at the first row where the cell in column A is blank instead of the first unused row. This allows him to add a formula in another column, something that would change the first unused row. Second, he’d like to schedule running the script, so it needs to be converted from VBA (which runs inside of an Office product) to VBScript (which runs outside of Office products). With the script converted to VBScript Gur can schedule it to run using Windows’ built in task scheduler.

Instructions.

  1. Start Notepad.
  2. Copy the code from the code snippet box and paste it into Notepad.
  3. Edit the code as needed.
  4. Save the script. You can name it anything you want, just be sure that the file extension is .vbs
  5. Run the script by double-clicking on it.
Const MACRO_NAME = "Export Messages to Excel (Rev 13)"
Const olMail = 43

'On the next line edit the path as needed
Const HIST_PATH = "Personal Folder - HISTORY"

Dim olkApp, olkSes, excApp, excWkb, excWks

'Add as many exports as you need. Each export is controlled by a line like the following
'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folder\Daily", "Data"
MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME


Sub ExportMessagesToExcel(strFilename, strFolderPath, strSheet)
    Dim olkMsg, olkFld, olkHst, ntRow, intVersion, intCnt
    If strFilename <> "" Then
        If strFolderPath <> "" Then
            Set olkApp = CreateObject("Outlook.Application")
            Set olkSes = olkApp.GetNamespace("MAPI")
            olkSes.Logon olkApp.DefaultProfileName
            Set olkFld = OpenOutlookFolder(strFolderPath)
            Set olkHst = OpenOutlookFolder(HIST_PATH)
            If TypeName(olkFld) <> "Nothing" Then
                intVersion = GetOutlookVersion()
                Set excApp = CreateObject("Excel.Application")
                Set excWkb = excApp.Workbooks.Open(strFilename)
                Set excWks = excWkb.Sheets(strSheet)
                'Write Excel Column Headers
                With excWks
                    .Cells(1, 1) = "Subject"
                    .Cells(1, 2) = "From"
                    .Cells(1, 3) = "Date"
                End With
                intRow = GetFirstBlankRow()
                'Write messages to spreadsheet
                For intCnt = olkFld.Items.Count To 1 Step -1
                    Set olkMsg = olkFld.Items(intCnt)
                    'Only export messages, not receipts or appointment requests, etc.
                    If olkMsg.Class = olMail Then
                        'Add a row for each field in the message you want to export
                        excWks.Cells(intRow, 1) = olkMsg.Subject
                        excWks.Cells(intRow, 2) = GetSMTPAddress(olkMsg, intVersion)
                        excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                        intRow = intRow + 1
                        olkMsg.Move olkHst
                    End If
                Next
                Set olkMsg = Nothing
                excWkb.Close True
            Else
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            End If
        Else
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    olkSes.Logoff
    Set olkSes = Nothing
    Set olkApp = Nothing
    Set olkMsg = Nothing
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Function OpenOutlookFolder(strFolderPath)
    Dim arrFolders, varFolder, bolBeyondRoot
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = olkSes.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Private Function GetSMTPAddress(Item, intOutlookVersion)
    Dim olkSnd, olkEnt
    On Error Resume Next
    Select Case intOutlookVersion
        Case 1-13
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion()
    Dim arrVer
    arrVer = Split(olkApp.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg)
    Dim olkPA
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function GetFirstBlankRow()
    Dim intCnt
    intCnt = 1
    Do Until excWks.Cells(intCnt, 1) = ""
        intCnt = intCnt + 1
    Loop
    GetFirstBlankRow = intCnt
End Function

I put this revision together for Leslie Roman who receives messages with information that she needs to parse out of the message body. To accomplish that I’ve modified the code to look for certain keywords in the body and extract everything on the line the keyword is on from the end of the keyword to the end of the line.

Const MACRO_NAME = "Export Messages to Excel (Rev 14)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strBuffer As String, _
        strFilename As String, _
        strTemp As String, _
        arrLines As Variant, _
        varLine As Variant, _
        bolComments As Boolean
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "Name"
            .Cells(1, 5) = "Order#"
            .Cells(1, 6) = "Email"
            .Cells(1, 7) = "Testimonial"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                strBuffer = ""
                bolComments = False
                arrLines = Split(olkMsg.Body, vbCrLf)
                For Each varLine In arrLines
                    strTemp = Trim(varLine)
                    If bolComments Then
                        strBuffer = strBuffer & strTemp
                    Else
                        If Left(strTemp, 6) = "Name: " Then
                            excWks.Cells(intRow, 4) = Mid(strTemp, 7)
                        Else
                            If Left(strTemp, 10) = "Order Id: " Then
                                excWks.Cells(intRow, 5) = Mid(strTemp, 11)
                            Else
                                If Left(strTemp, 9) = "Order #: " Then
                                    excWks.Cells(intRow, 5) = Mid(strTemp, 9)
                                    bolComments = True
                                Else
                                    If Left(strTemp, 7) = "Email: " Then
                                        excWks.Cells(intRow, 6) = Mid(strTemp, 8)
                                    Else
                                        If Left(strTemp, 12) = "Testimonial:" Then
                                            bolComments = True
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    End If
                Next
                excWks.Cells(intRow, 7) = strBuffer
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Santhosh Mateti who asked

1) How export the selected mail items which are in same folder? Example : Subject contain “Down”.
2) How to append the exporting mail items to exiting Excel ?

This version is essentially the same as revision 2 with the exception that it doesn’t link to the original message and only processes messages that have the word “down” in the subject line.

'On the next line change the value to True if you want each export to go on a new sheet.
Const EXPORT_NEWSHEET = False
Const MACRO_NAME = "Export Messages to Excel (Rev 15)"

Sub ExportController()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folders\Folder1"
    ExportMessagesToExcel "C:\Folder2.xlsx", "Personal Folders\Folder2"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, strFolderPath As String)
    Dim olkMsg As Object, _
        olkFld As Outlook.MAPIFolder, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVer As Integer
    If strFilename <> "" Then
        If strFolderPath <> "" Then
            Set olkFld = OpenOutlookFolder(strFolderPath)
            If TypeName(olkFld) <> "Nothing" Then
                intVer = GetOutlookVersion()
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                Set excApp = CreateObject("Excel.Application")
                If objFSO.FileExists(strFilename) Then
                    Set excWkb = excApp.Workbooks.Open(strFilename)
                    If EXPORT_NEWSHEET Then
                        Set excWks = excWkb.Worksheets.Add()
                        excWks.Name = Format(Date, "m-dd-yy") 
                    Else
                        Set excWks = excWkb.Worksheets(1)
                    End If
                Else
                    Set excWkb = excApp.Workbooks.Add()
                    Set excWks = excWkb.Worksheets(1)
                End If
                'Write Excel Column Headers
                With excWks
                    .Cells(1, 1) = "Subject"
                    .Cells(1, 2) = "Received"
                    .Cells(1, 3) = "Sender"
                End With
                intRow = 2
                'Write messages to spreadsheet
                For Each olkMsg In olkFld.Items
                    If olkMsg.Class = olMail Then
                        If InStr(1, LCase(olkMsg.Subject), "down") then
                            'Add a row for each field in the message you want to export
                            excWks.Cells(intRow, 1) = olkMsg.Subject
                            excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                            excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVer)
                            intRow = intRow + 1
                        End If
                    End If
                Next
                Set olkMsg = Nothing
            Else
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            End If
        Else
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    If objFSO.FileExists(strFilename) Then
        excWkb.Save
    Else
        excWkb.SaveAs strFilename
    End If
    excWkb.Close True
    Set olkMsg = Nothing
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

This revision is for Nicolas who asked for a way to merge revisions 1 and 3 in such a way that he can pass the process a path to the Outlook folder he wants to export and have the script export that folder and all its sub-folders. This revision accomplishes that. Nicolas will use the instrucitons from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 16)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVersion As Integer, _
    intMessages As Integer

Sub ExportController()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folders\Folder1"
    ExportMessagesToExcel "C:\Folder2.xlsx", "Personal Folders\Folder2"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, strFolder As String)
    Dim olkFolder As Outlook.MAPIFolder
    If strFilename <> "" Then
        Set olkFolder = OpenOutlookFolder(strFolder)
        If TypeName(olkFolder) = "Nothing" Then
            MsgBox "Could not find the folder '" & strFolder & "'.  Processing aborted.", vbCritical + vbOKOnly + vbSystemModal, MACRO_NAME
        Else
            intMessages = 0
            intVersion = GetOutlookVersion()
            Set excApp = CreateObject("Excel.Application")
            Set excWkb = excApp.Workbooks.Add
            Set excWks = excWkb.Worksheets(1)
            'Write Excel Column Headers
            With excWks
                .Cells(1, 1) = "Subject"
                .Cells(1, 2) = "Received"
                .Cells(1, 3) = "Sender"
            End With
            ProcessFolder olkFolder
            excWkb.SaveAs strFilename
            MsgBox "Process complete.  A total of " & intMessages & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
        End If
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    Set olkFolder = Nothing
End Sub

Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, olkSub As Outlook.MAPIFolder, intRow As Integer
    intRow = excWks.UsedRange.Rows.Count
    intRow = intRow + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(intRow, 1) = olkMsg.Subject
            excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
            excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
            intRow = intRow + 1
            intMessages = intMessages + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkssub = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.
    ' Written: 4/24/2009
    ' Author:  David Lee
    ' Outlook: All versions
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

This revision is for Christopher. See his comment below, it’s the one dated 2012/12/11 at 12:55 pm., for details.

'On the next line change the value to True if you want each export to go on a new sheet.
Const EXPORT_NEWSHEET = False
'On the next line edit the list of categories you want to export for. The list must be comma-separated values.
Const EXPORT_CATS = "Accepted,Pending,Declined"
'On the next line change the value to the number of characters you want to pull from the beginning of the message.
Const CHAR_LIMIT = 70
Const MACRO_NAME = "Export Messages to Excel (Rev 17)"

Sub ExportController()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", "Personal Folders\Folder1"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, strFolderPath As String)
    Dim olkMsg As Object, _
        olkFld As Outlook.MAPIFolder, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intExp As Integer, _
        intRow As Integer, _
        intVer As Integer, _
        arrCat As Variant, _
        arrExp As Variant, _
        varExp As Variant, _
        bolExp As Boolean, _
        strTmp As String
    If strFilename <> "" Then
        If strFolderPath <> "" Then
            Set olkFld = OpenOutlookFolder(strFolderPath)
            If TypeName(olkFld) <> "Nothing" Then
                intVer = GetOutlookVersion()
                Set objFSO = CreateObject("Scripting.FileSystemObject")
                Set excApp = CreateObject("Excel.Application")
                If objFSO.FileExists(strFilename) Then
                    Set excWkb = excApp.Workbooks.Open(strFilename)
                    If EXPORT_NEWSHEET Then
                        Set excWks = excWkb.Worksheets.Add()
                        excWks.Name = Format(Date, "m-dd-yy")
                    Else
                        Set excWks = excWkb.Worksheets(1)
                    End If
                Else
                    Set excWkb = excApp.Workbooks.Add()
                    Set excWks = excWkb.Worksheets(1)
                End If
                'Write Excel Column Headers
                With excWks
                    .Cells(1, 1) = "Date"
                    .Cells(1, 2) = "MyName"
                    .Cells(1, 3) = "Company"
                    .Cells(1, 4) = "First"
                    .Cells(1, 5) = "Last"
                    .Cells(1, 6) = "Title"
                    .Cells(1, 7) = "Email"
                    .Cells(1, 8) = "Phone"
                    .Cells(1, 9) = "Industry"
                    .Cells(1, 10) = "2ndContact"
                    .Cells(1, 11) = "Method"
                    .Cells(1, 12) = "Attempt"
                    .Cells(1, 13) = "Result"
                    .Cells(1, 14) = "Message"
                    .Cells(1, 15) = "Notes"
                    .Cells(1, 16) = "Notes 2"
                    .Cells(1, 17) = "Co Size"
                    .Cells(1, 18) = "ID"
                End With
                arrExp = Split(EXPORT_CATS, ",")
                'Write messages to spreadsheet
                For Each olkMsg In olkFld.Items
                    If olkMsg.Class = olMail Then
                        For intRow = 2 To excWks.UsedRange.Rows.Count
                            If excWks.Cells(intRow, 18) = olkMsg.EntryID Then
                                bolFound = True
                                Exit For
                            End If
                        Next
                        If Not bolFound Then intRow = excWks.UsedRange.Rows.Count + 1
                        bolExp = False
                        arrCat = Split(olkMsg.Categories, ",")
                        For Each varExp In arrExp
                            For intExp = LBound(arrCat) To UBound(arrCat)
                                If varExp = arrCat(intExp) Then
                                    bolExp = True
                                    Exit For
                                End If
                            Next
                            If bolExp Then Exit For
                        Next
                        If bolExp Then
                            'Add a row for each field in the message you want to export
                            excWks.Cells(intRow, 1) = Date
                            'On the next line replace John Doe Inc with the name of your company
                            strTmp = Replace(olkMsg.Subject, " call with John Doe Inc", "")
                            If Left(strTmp, 4) = "RE: " or Left(strTmp, 4) = "FW: " Then strTmp = Mid(strTmp, 5)
                            excWks.Cells(intRow, 3) = strTmp
                            excWks.Cells(intRow, 13) = olkMsg.Categories
                            excWks.Cells(intRow, 14) = Left(olkMsg.Body, CHAR_LIMIT)
                            excWks.Cells(intRow, 18) = olkMsg.EntryID
                            intRow = intRow + 1
                        End If
                    End If
                Next
                Set olkMsg = Nothing
            Else
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            End If
        Else
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        End If
    Else
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    End If
    If objFSO.FileExists(strFilename) Then
        excWkb.Save
    Else
        excWkb.SaveAs strFilename
    End If
    excWkb.Close True
    Set olkMsg = Nothing
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

This revision is for Andre. See his comment below, it’s the one dated 2012/12/26 at 2:11 pm., for complete details. In short, Andre receives automated emails letting him know the current storage capacity of servers he works with. Andre needs to extract the message time and a line of information from the body of those messages to an Excel spreadsheet. The code I’ve used to accomplish this is a slight variation of this post’s original code. Andre will use the instructions from the original post to add this code to Outlook and to run it.

Const MACRO_NAME = "Export Messages to Excel (Rev 18)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intCnt As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        objFSO As Object, _
        bolExists As Boolean
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
    If strFilename <> "" Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set excApp = CreateObject("Excel.Application")
        bolExists = objFSO.FileExists(strFilename)
        If bolExists Then
            Set excWkb = excApp.Workbooks.Open(strFilename)
        Else
            Set excWkb = excApp.Workbooks.Add()
        End If
        'On the next line change the sheet number as needed
        Set excWks = excWkb.Worksheets(1)
        If bolExists Then
            intRow = excWks.UsedRange.Rows.Count + 1
        Else
            'Write Excel Column Headers
            With excWks
                .Cells(1, 1) = "Received"
                .Cells(1, 2) = "Data"
            End With
            intRow = 2
        End If
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 2) = FindString(olkMsg.Body, "(.*?) has exceeded its upper threshold limits for disk space by (.*?) percent")
                intRow = intRow + 1
                intCnt = intCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        If bolExists Then
            excWkb.Close True
        Else
            excWkb.SaveAs strFilename
            excWkb.Close
        End If
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    Set objFSO = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Function FindString(strText As String, strFind As String) As String
    Dim objRegEx As Object, colMatches As Object, objMatch As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = False
        .Global = True
        .Pattern = strFind
        Set colMatches = .Execute(strText)
    End With
    If colMatches.Count > 0 Then
        Set objMatch = colMatches.Item(0)
        FindString = objMatch.Value
    Else
        FindString = "Not found"
    End If
    Set objRegEx = Nothing
    Set colMatches = Nothing
    Set objMatch = Nothing
End Function

This revision is for Molly. Molly is using a custom Outlook form and needs to export the form’s custom fields along with a couple of the stock fields. This version is designed to be called from a rule, although I can modify it to run manually if that’s what Molly needs.

Adding the code to Outlook.

  1. Start Outlook
  2. Press ALT+F11 to open the Visual Basic Editor
  3. If not already expanded, expand Microsoft Office Outlook Objects
  4. If not already expanded, expand Modules
  5. Select an existing module (e.g. Module1) by double-clicking on it or create a new module by right-clicking Modules and selecting Insert > Module.
  6. Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window
  7. Edit the code per the comments I included in it
  8. Click the diskette icon on the toolbar to save the changes
  9. Close the VB Editor
  10. Create a rule that fires for these messages
  11. Set the rule’s action to “run a script” and select HandleAnalystRequestForm as the macro to run
'Declare some constants
'On the next line edit the path to the workbook 
Const WORKBOOK_PATH = "C:\Users\David\Documents\TestArea\Molly.xlsx"
Const MACRO_NAME = "Export Messages to Excel (Rev 19)"

Sub HandleAnalystRequestForm(Item As Outlook.MailItem)
    If Item.MessageClass = "IPM.Note.Analyst Request Form" Then
        ExportFormToExcel Item, WORKBOOK_PATH, 1
    End If
End Sub

Sub ExportFormToExcel(olkMsg As Outlook.MailItem, strWorkbook As String, intSheet As Integer)
    'Declare some variables
    Dim excApp As Object, excWkb As Object, excWks As Object

    'Connect to Excel
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(strWorkbook)
    Set excWks = excWkb.Worksheets(intSheet)

    'Add a row to the spreadsheet
    intRow = excWks.UsedRange.Rows.Count + 1

    'Write the data to the spreadsheet.  Add a row for each field in the message you want to export.
    excWks.Cells(intRow, 1) = olkMsg.To
    excWks.Cells(intRow, 2) = olkMsg.CC
    excWks.Cells(intRow, 3) = olkMsg.UserProperties.Item("Trans").Value
    excWks.Cells(intRow, 4) = olkMsg.UserProperties.Item("Situation").Value
    excWks.Cells(intRow, 5) = olkMsg.UserProperties.Item("Question").Value
    excWks.Cells(intRow, 6) = olkMsg.UserProperties.Item("Potential Solution").Value
    excWks.Cells(intRow, 7) = olkMsg.UserProperties.Item("Response").Value
    excWks.Cells(intRow, 8) = olkMsg.UserProperties.Item("Next Steps").Value

    'Save and close the workbook
    excWkb.Close True

    'Clean-up open objects to avoid memory leaks
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

To be filled in later.

I put this version together for Thomas who asked me to merge various pieces of other version together for him. Outlook triggers the code via a rule. When it runs it extracts four pieces of data in the body of the message, writes the data to the spreadsheet, moves the triggering message to an Outlook folder, and runs an Excel macro. Thomas must edit the three constants at the top of the code per the instructions I included in the code for the script to work. The instructions for adding this code to Outlook are the same as for the original version of the code in this post.

'Declare some constants
'On the next line edit the path to the workbook
Const WORKBOOK_PATH = "C:\Users\David\Documents\TestArea\Kleppinger.xlsm"
'On the next line edit the path to the Outlook folder the message will be mvoed to after processing it
Const FOLDER_PATH = "Personal Folders\History"
'On the next line edit the name of the Excel macro to run after the data is added to Excel.  Be sure to include the name of the module the macro is in.
Const EXCEL_MACRO = "Sheet1.Macro_Email"
Const MACRO_NAME = "Export Messages to Excel (Rev 21)"

Sub ExportMessagesToExcel(Item As Outlook.MailItem)
    'Declare some variables
    Dim excApp As Object, excWkb As Object, excWks As Object, arrRows As Variant, arrRow As Variant, varRow As Variant, intRow
    
    'Get the folder object of the folder the message is to be moved to
    Set olkFld = OpenOutlookFolder(FOLDER_PATH)

    'Connect to Excel
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(WORKBOOK_PATH)
    Set excWks = excWkb.Worksheets(1)

    'Set the row number to change
    intRow = 1
    
    'Get data from the message and put it into the spreadsheet
    arrRows = Split(Item.Body, vbCrLf)
    For Each varRow In arrRows
        If InStr(1, varRow, ":") > 0 Then
            arrRow = Split(varRow, ":")
            Select Case arrRow(0)
                Case "A"
                    excWks.Cells(intRow, 1) = arrRow(1)
                Case "B"
                    excWks.Cells(intRow, 2) = arrRow(1)
                Case "C"
                    excWks.Cells(intRow, 3) = arrRow(1)
                Case "D"
                    excWks.Cells(intRow, 4) = arrRow(1)
            End Select
        End If
    Next
    
    'Move the message to a folder
    Item.Move olkFld
    
    'Run an Excel macro
    excApp.Run EXCEL_MACRO
    
    'Save and close the workbook
    excWkb.Close True

    'Clean-up open objects to avoid memory leaks
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.
    ' Written: 4/24/2009
    ' Author:  David Lee
    ' Outlook: All versions
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

This revision is a knock-off of Revision 5. I put it together for Yogesh Patel who is using Rev 5 and asked if I could add the ability to “… extract (save) attachments to a defined folder and also create the log.” Yogesh will need to edit the ATTACHMENT_PATH constant to point to the folder he wants to save attachments to. As it saves each attachment the code checks to see if an attachment with that name already exists in the folder. If it does, then it prepends “Copy (x)” to the file name. This precludes overwriting attachments with the same name. The instructions for adding this code to Outlook are the same as for the original version of the code in this post.

Const MACRO_NAME = "Export Messages to Excel (Rev 22)"
'On the nest line edit the path to the folder you want to save attachments to.  The path name must end with a \
Const ATTACHMENT_PATH = "c:\Users\David\Documents\TestArea\YogeshPatel\"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        olkAtt As Outlook.Attachment, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        intCount As Integer, _
        strFilename As String, _
        strAtt As String, _
        strSaveFilePath As String, _
        objFSO As Object
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "To"
            .Cells(1, 5) = "Attachments"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                excWks.Cells(intRow, 4) = olkMsg.To
                strAtt = ""
                For Each olkAtt In olkMsg.Attachments
                    If Not IsHiddenAttachment(olkAtt) Then
                        strAtt = strAtt & olkAtt.Filename & ", "
                        intCount = 0
                        strSaveFilePath = ATTACHMENT_PATH & olkAtt.Filename
                        Do While objFSO.FileExists(strSaveFilePath)
                            intCount = intCount + 1
                            strSaveFilePath = ATTACHMENT_PATH & "Copy (" & intCount & ") of " & olkAtt.Filename
                        Loop
                        olkAtt.SaveAsFile strSaveFilePath
                    End If
                Next
                If strAtt <> "" Then
                    strAtt = Left(strAtt, Len(strAtt) - 2)
                End If
                excWks.Cells(intRow, 5) = strAtt
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set objFSO = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for Tom who posted a comment asking for a version that would

  1. Be triggered by a rule.
  2. Extract text from the subject line of the message that triggered the rule. The text appears between two colons.
  3. Connect to an already open Excel workbook.
  4. Write the extracted text to column D on the first line where column D is blank.

The instructions for adding this code to Outlook are the same as for the original version of the code at the beginning of this post.

Sub ExportMessagesToExcel(olkMsg As Outlook.MailItem)
    '--> Create some constants
    'On the next line edit the name of the workbook
    Const WBK_NAME = "Book1"
    'On the next line edit the name of the worksheet
    Const WKS_NAME = "Sheet1"
    Const MACRO_NAME = "Export Messages to Excel (Rev 23)"
    
    '--> Create some variables
    Dim excApp As Object, excWkb As Object, excWks As Object, lngRow As Long, strTxt As String, intP1 As Integer, intP2 As Integer
    
    '--> Turn error handling off
    On Error Resume Next
    
    '--> Main routine
    intP1 = InStr(1, olkMsg.Subject, ":")
    intP2 = InStrRev(olkMsg.Subject, ":")
    strTxt = Mid(olkMsg.Subject, intP1 + 1, intP2 - (intP1 + 1))
    Set excApp = GetObject(, "Excel.Application")
    If TypeName(excApp) = "Nothing" Then
        MsgBox "The spreadsheet was not open.  Operation aborted.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        Set excWkb = excApp.Workbooks.Item(WBK_NAME)
        If TypeName(excWkb) = "Nothing" Then
            MsgBox "The workbook named " & WBK_NAME & " isn't open.  Operation aborted.", vbCritical + vbOKOnly, MACRO_NAME
        Else
            Set excWks = excWkb.Worksheets.Item(WKS_NAME)
            If TypeName(excWks) = "Nothing" Then
                MsgBox "The workbook doesn't have a worksheet named " & WKS_NAME & ".  Operation aborted.", vbCritical + vbOKOnly, MACRO_NAME
            Else
                lngRow = 1
                Do Until excWks.Cells(lngRow, 4) = ""
                    lngRow = lngRow + 1
                Loop
                excWks.Cells(lngRow, 4) = strTxt
            End If
        End If
    End If
    
    '--> Cleanup
    Set excApp = Nothing
    Set excWkb = Nothing
    Set excWks = Nothing
End Sub

This revision is for Tanveer who asked for a knockoff of revision 6 with the added ability of giving each saved attachment a unique filename. The filename will be the attachment name plus an incremental number.

The instructions for adding this code to Outlook are the same as for the original version of the code in this post.

Const MACRO_NAME = "Export Messages to Excel (Rev 24)"

Sub ExportMessagesToExcel()
    'Edit the path on the next line.  This is the folder the code will save the attachments to.  Make sure that the path ends with a \
    Const EXPORT_FOLDER = "C:\SomeFolder\"
    Dim olkLst As Object, _
        olkMsg As Object, _
        olkAtt As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        objFSO As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        intCount As Integer, _
        strFilename As String, _
        strDateRange As String, _
        strTemp As String, _
        arrTemp As Variant, _
        datStart As Date, _
        datEnd As Date
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
        arrTemp = Split(strDateRange, "to")
        datStart = IIf(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
        datEnd = IIf(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
        intVersion = GetOutlookVersion()
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "Attachments"
        End With
        intRow = 2
        'Write messages to spreadsheet
        Set olkLst = Application.ActiveExplorer.CurrentFolder.Items.Restrict("[ReceivedTime] >= '" & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                For Each olkAtt In olkMsg.Attachments
                    If Not IsHiddenAttachment(olkAtt) Then
                        intCount = 0
                        strTemp = olkAtt.Filename
                        Do While objFSO.FileExists(EXPORT_FOLDER & strTemp)
                            intCount = intCount + 1
                            strTemp = objFSO.GetBaseName(olkAtt.Filename) & " " & intCount & "." & objFSO.GetExtensionName(olkAtt.Filename)
                        Loop
                        olkAtt.SaveAsFile EXPORT_FOLDER & strTemp
                        excWks.Cells(intRow, 4).Value = excWks.Cells(intRow, 4).Value & strTemp & vbCrLf
                    End If
                Next
                If Len(excWks.Cells(intRow, 4).Value) > 0 Then
                    excWks.Cells(intRow, 4).Value = Left(excWks.Cells(intRow, 4).Value, Len(excWks.Cells(intRow, 4).Value) - 1)
                End If
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set olkLst = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for Nicholas K. who asked for a solution that parses out all the hyperlinks in each message and writes them to successive columns in the spreadsheet. With the exception of that addition, this version is identical to the code from the original post. Nicholas will use the instructions from the original post to add this code to Outlook.

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intCol As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        arrHyp As Variant, _
        varhyp As Variant
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                intCol = 4
                arrHyp = Split(GetLinks(olkMsg.HTMLBody), "|")
                For Each varhyp In arrHyp
                    excWks.Cells(intRow, intCol) = varhyp
                    intCol = intCol + 1
                Next
                intRow = intRow + 1
            End If
        Next
        excWks.Columns("A:ZZ").AutoFit
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function GetLinks(strHTML As String) As String
    Const READYSTATE_COMPLETE = 4
    Dim objIE As Object, objDoc As Object, colLinks As Object, objLink As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    Do Until objIE.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    objIE.document.Body.innerHTML = strHTML
    Set objDoc = objIE.document
    Set colLinks = objDoc.getElementsByTagName("a")
    If colLinks.Length > 0 Then
        For Each objLink In colLinks
            GetLinks = GetLinks & objLink.href & "|"
        Next
        GetLinks = Left(GetLinks, Len(GetLinks) - 1)
    Else
        GetLinks = ""
    End If
    Set objLink = Nothing
    Set colLinks = Nothing
    Set objDoc = Nothing
    Set objIE = Nothing
End Function

This revision is for Malcolm who needs to extract two pieces of information from the body of certain messages. The code is a knock-off of revision 14, changed to watch for the key text in Macolm’s messages and extract the data that appears after it. Malcolm will use the instructions from the original post to add the code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 26)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intVersion As Integer, _
        strFilename As String, _
        strTemp As String, _
        arrLines As Variant, _
        varLine As Variant
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "Contact #"
            .Cells(1, 5) = "Email"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                arrLines = Split(olkMsg.Body, vbCrLf)
                For Each varLine In arrLines
                    strTemp = Trim(varLine)
                    If Left(strTemp, 16) = "Contact number: " Then
                        excWks.Cells(intRow, 4) = Mid(strTemp, 17)
                    Else
                        If Left(strTemp, 15) = "Email Address: " Then
                            excWks.Cells(intRow, 5) = Mid(strTemp, 16)
                        End If
                    End If
                Next
                intRow = intRow + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intRow - 2 & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Laura. Laura receives messages from an automated process each containing an HTML table in the message body. She needs to pull three pieces of data from that table, each piece located in a different cell. To do that, I wrote a function that returns an array of the cells, then simply get the contents of the three cells Laura needs. The rest of the code is essentially the same as that in the original post. Laura will use the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 27)"

Private Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intCnt As Integer, _
        strFilename As String, _
        arrCells As Variant
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Date"
            .Cells(1, 4) = "Email"
            .Cells(1, 5) = "Count"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Get the cells from the table in the body of the message
                arrCells = Split(GetCells(olkMsg.HTMLBody), Chr(255))
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = olkMsg.Subject
                excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 3) = Left(arrCells(12), 19)
                excWks.Cells(intRow, 4) = arrCells(16)
                excWks.Cells(intRow, 5) = arrCells(14)
                intRow = intRow + 1
                intCnt = intCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetCells(strHTML As String) As String
    Const READYSTATE_COMPLETE = 4
    Dim objIE As Object, objDoc As Object, colCells As Object, objCell As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    Do Until objIE.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    objIE.document.Body.innerHTML = strHTML
    Set objDoc = objIE.document
    Set colCells = objDoc.getElementsByTagName("td")
    If colCells.Length > 0 Then
        For Each objCell In colCells
            GetCells = GetCells & objCell.innerText & Chr(255)
        Next
        GetCells = Left(GetCells, Len(GetCells) - 1)
    Else
        GetCells = ""
    End If
    Set objCell = Nothing
    Set colCells = Nothing
    Set objDoc = Nothing
    Set objIE = Nothing
End Function

This revision is for Aiyana. Aiyana was sent a message with multiple message items attached. For each attachment, Aiyana needs to parse data from the body of the item and put it in certain rows in the spreadsheet. The data from each message will go in one column of the spreadsheet. Aiyana will use the instructions from the original post to add this code to Outlook. To use the script, Aiyana will select the message containing the attachments and then run the macro.

Const MACRO_NAME = "Export Messages to Excel (Rev 28)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        olkAtt As Object, _
        olkTmp As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intCol As Integer, _
        strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        intCol = 1
        'Write attachment bodies to the spreadsheet
        Set olkMsg = Application.ActiveExplorer.Selection(1)
        For Each olkAtt In olkMsg.Attachments
            If Right(LCase(olkAtt.Filename), 4) = ".msg" Then
                olkAtt.SaveAsFile Environ("TEMP") & "\" & olkAtt.Filename
                Set olkTmp = Application.CreateItemFromTemplate(Environ("TEMP") & "\" & olkAtt.Filename)
                excWks.Cells(1, intCol) = StripChar(Replace(FindString(olkTmp.Body, "name:(.+)\r\n"), "name: ", ""))
                excWks.Cells(2, intCol) = StripChar(Replace(FindString(olkTmp.Body, "email:(.+)\r\n"), "email: ", ""))
                excWks.Cells(3, intCol) = StripChar(Replace(FindString(olkTmp.Body, "company:(.+)\r\n"), "company: ", ""))
                excWks.Cells(4, intCol) = StripChar(Replace(FindString(olkTmp.Body, "city:(.+)\r\n"), "city: ", ""))
                excWks.Cells(5, intCol) = StripChar(Replace(FindString(olkTmp.Body, "branch:(.+)\r\n"), "branch: ", ""))
                excWks.Cells(6, intCol) = StripChar(Replace(FindString(olkTmp.Body, "design:(.+)\r\n"), "design: ", ""))
                excWks.Cells(7, intCol) = StripChar(Replace(FindString(olkTmp.Body, "satisfaction:(.+)\r\n"), "satisfaction: ", ""))
                excWks.Cells(8, intCol) = StripChar(Replace(FindString(olkTmp.Body, "response:(.+)\r\n"), "response: ", ""))
                excWks.Cells(9, intCol) = StripChar(Replace(FindString(olkTmp.Body, "frequency:(.+)\r\n"), "frequency: ", ""))
                excWks.Cells(10, intCol) = StripChar(Replace(FindString(olkTmp.Body, "delivery:(.+)\r\n"), "delivery: ", ""))
                excWks.Cells(11, intCol) = StripChar(Replace(FindString(olkTmp.Body, "aftersales:(.+)\r\n"), "aftersales: ", ""))
                excWks.Cells(12, intCol) = StripChar(Replace(FindString(olkTmp.Body, "total:(.+)\r\n"), "total: ", ""))
                excWks.Cells(13, intCol) = StripChar(Replace(FindString(olkTmp.Body, "referencecompany:(.+)\r\n"), "referencecompany: ", ""))
                excWks.Cells(14, intCol) = StripChar(Replace(FindString(olkTmp.Body, "referencecontact:(.+)\r\n"), "referencecontact: ", ""))
                excWks.Cells(15, intCol) = StripChar(Replace(FindString(olkTmp.Body, "referenceemail:(.+)\r\n"), "referenceemail: ", ""))
                excWks.Cells(16, intCol) = StripChar(Replace(FindString(olkTmp.Body, "message:(.+)\b"), "message: ", ""))
                intCol = intCol + 1
            End If
            Set olkTmp = Nothing
        Next
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set olkMsg = Nothing
    Set olkAtt = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function FindString(strText As String, strFind As String) As String
    Dim objRegEx As Object, colMatches As Object, objMatch As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = strFind
        Set colMatches = .Execute(strText)
    End With
    If colMatches.Count > 0 Then
        Set objMatch = colMatches.Item(0)
        FindString = objMatch.Value
    Else
        FindString = "Not found"
    End If
    Set objRegEx = Nothing
    Set colMatches = Nothing
    Set objMatch = Nothing
End Function

Private Function StripChar(strValue As String) As String
    StripChar = Replace(strValue, vbCr, "")
    StripChar = Replace(StripChar, vbLf, "")
    StripChar = Replace(StripChar, vbTab, "")
    StripChar = Trim(StripChar)
End Function 

I created this revision for Juan Carlos Rodriguez. Juan asked for a version that exports all the messages in the selected folder and all that folder’s sub-folders with a couple of twists. He wants the export to write each message’s subject, recipients (both those it was sent to and those that were CC’d), the sender’s name, a list of attachments, and the message’s unique ID. Juan also wants the export to create a file system folder for each message and save a copy of the message, in plain-text format, and all attachments to that folder. Finally, he wants a link to each message’s folder added to the spreadsheet.

Juan will follow the instructions from the original post to add the code to Outlook. When he is ready to run the macro, he will

  1. Select a folder in Outlook
  2. Run the macro RunExportMessagesToExcel
'--> Declare some constants
'On the next line edit the path to the folder you want to export the messages to.  The macro will create a sub-folder under this folder for each message.
Const ROOT_PATH = "C:\Users\David\Documents\TestArea\JCR"
Const MACRO_NAME = "Export Messages to Excel (Rev 29)"

'--> Declare some variables
Private excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        objFSO As Object, _
        lngCnt As Long, _
        lngRow As Long

Sub RunExportMessagesToExcel()
    '--> Declare some variables
    Dim strWkb As String
    strWkb = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strWkb <> "" Then
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        'Connect to Excel
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Folder"
            .Cells(1, 2) = "Subject"
            .Cells(1, 3) = "To"
            .Cells(1, 4) = "From"
            .Cells(1, 5) = "CC"
            .Cells(1, 6) = "Attachments"
            .Cells(1, 7) = "ID"
            .Cells(1, 8) = "Link"
        End With
        lngRow = 2
        ExportMessagesToExcel Application.ActiveExplorer.CurrentFolder
        excWks.Columns("A:H").AutoFit
        excWkb.SaveAs strWkb
        excWkb.Close
        Set excWks = Nothing
        Set excWkb = Nothing
        Set excApp = Nothing
        Set objFSO = Nothing
        MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
    Else
        MsgBox "Operation canceled.", vbInformation + vbOKOnly, MACRO_NAME
    End If
End Sub

Private Sub ExportMessagesToExcel(olkFol As Outlook.MAPIFolder)
    '--> Declare some variables
    Dim olkMsg As Object, _
        olkAtt As Object, _
        olkSub As Object, _
        objFol As Object, _
        strAtt As String
    'Process the current folder and write its messages to the spreadsheet
    For Each olkMsg In olkFol.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            strAtt = ""
            'Create a new file system folder to store the message and its attachments
            Set objFol = objFSO.CreateFolder(ROOT_PATH & "\" & olkMsg.EntryID)
            'Save a plain-text version of the email to the folder
            olkMsg.SaveAs objFol.Path & "\Message.txt", olTXT
            'Save the attachments to the folder
            For Each olkAtt In olkMsg.Attachments
                If Not IsHiddenAttachment(olkAtt) Then
                    olkAtt.SaveAsFile objFol.Path & "\" & olkAtt.Filename
                    strAtt = strAtt & olkAtt.Filename & ", "
                End If
            Next
            If Len(strAtt) > 0 Then strAtt = Left(strAtt, Len(strAtt) - 2)
            'Add a row for each field in the message you want to export
            excWks.Cells(lngRow, 1) = olkMsg.Parent.FolderPath
            excWks.Cells(lngRow, 2) = olkMsg.Subject
            excWks.Cells(lngRow, 3) = olkMsg.To
            excWks.Cells(lngRow, 4) = olkMsg.SenderName
            excWks.Cells(lngRow, 5) = olkMsg.CC
            excWks.Cells(lngRow, 6) = strAtt
            excWks.Cells(lngRow, 7) = olkMsg.EntryID
            excWks.Hyperlinks.Add Anchor:=excWks.Range("H" & lngRow, "H" & lngRow), Address:="file://" & objFol.Path, TextToDisplay:="View Original Items"
            lngRow = lngRow + 1
            lngCnt = lngCnt + 1
            Set olkAtt = Nothing
        End If
        DoEvents
    Next
    Set olkMsg = Nothing
    Set objFol = Nothing
    For Each olkSub In olkFol.Folders
        ExportMessagesToExcel olkSub
        DoEvents
    Next
    Set olkSub = Nothing
End Sub

Private Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    ' Purpose: Determines if an attachment is a hidden attachment.
    ' Written: 7/12/2012
    ' Author:  David Lee
    ' Outlook: 2007 and later
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I created this revision is for Suhas. Suhas asked for a mashup of capabilities from multiple revisions. Specifically, Suhas wants a version that

  1. Ask for the path and folder name to save the data
  2. Ask for a date range
  3. Export the data from the subfolders based on the date range into the same excel sheet

This version does all three. Suhas will use the instructions from the original post to add the code to Outlook. To run the code, Suhas will

  1. Select a folder. The macro will process that folder and all sub-folders under it.
  2. Run the macro ExportMessagesToExcel.
  3. Enter the path and filename of the file the macro will export the data to. The macro will create this file.
  4. Enter a date range. The macro will only export messages that fall inside the date range Suhas enters.
Const MACRO_NAME = "Export Messages to Excel (Rev 30)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVersion As Integer, _
    lngMessages As Long, _
    lngRow As Long, _
    datStart As Date, _
    datEnd As Date

Sub ExportMessagesToExcel()
    Dim strFilename As String, strDateRange As String, arrTemp As Variant
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
        arrTemp = Split(strDateRange, "to")
        datStart = IIf(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
        datEnd = IIf(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
        lngMessages = 0
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        lngRow = excWks.UsedRange.Rows.Count + 1
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        ProcessFolder Application.ActiveExplorer.CurrentFolder
        excWkb.SaveAs strFilename
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & lngMessages & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkLst As Object, _
        olkMsg As Object, _
        olkSub As Object
    If olkFld.DefaultItemType = olMailItem Then
        Set olkLst = olkFld.Items.Restrict("[ReceivedTime] >= '" & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        'Write messages to spreadsheet
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(lngRow, 1) = olkMsg.Subject
                excWks.Cells(lngRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(lngRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                lngRow = lngRow + 1
                lngMessages = lngMessages + 1
            End If
        Next
        Set olkMsg = Nothing
    End If
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkSub = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This is another revision I put together for Saroj. Every hour Saroj receives a message with a body like this

Provider: AFU Service: toxxxxfile MaxLimit: 1 Active: 0 Total: 0
Tasks:
Provider: AFURVGX Service: fltocvx MaxLimit: 5 Active: 0 Total: 1
Tasks:
P1406624a0ef5136ac692
Provider: AFU Service: extractor MaxLimit: 87 Active: 7 Total: 7
Tasks:
Provider: AFU Service: pllxxltoxt MaxLimit: 9 Active: 0 Total: 0
Tasks:
Provider: AFU Service: catly5tooneas MaxLimit: 3 Active: 0 Total: 0
Tasks:
Provider: AFUR Service: urdestoxt MaxLimit: 6 Active: 0 Total: 0
Tasks:
Provider: AFU Service: slcreate MaxLimit: 6 Active: 0 Total: 0
Tasks:
Provider: AFUR Service: caty5tocvx MaxLimit: 23 Active: 5 Total: 8

He needs to be able to extract the number after the word “Active:” and write it to a spreadsheet. All the values from one message will go in different columns on the same row. The solution I’ve put together extracts the same message information as the original post plus the values Saroj needs.

Saroj will follow the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 31)"

Private Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        lngCol As Long, _
        lngCnt As Long, _
        intVersion As Integer, _
        strFilename As String, _
        arrHits As Variant, _
        varHit As Variant
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        lngRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(lngRow, 1) = olkMsg.Subject
                excWks.Cells(lngRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(lngRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                lngCol = 4
                arrHits = Split(FindString(olkMsg.Body, "Active:\s[0-9]*\s"), "|")
                For Each varHit In arrHits
                    excWks.Cells(lngRow, lngCol) = Trim(Replace(varHit, "Active: ", ""))
                    lngCol = lngCol + 1
                Next
                lngRow = lngRow + 1
                lngCnt = lngCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function FindString(strText As String, strFind As String) As String
    Dim objRegEx As Object, colMatches As Object, objMatch As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = False
        .Global = True
        .Pattern = strFind
        Set colMatches = .Execute(strText)
    End With
    If colMatches.Count > 0 Then
        For Each objMatch In colMatches
            FindString = FindString & objMatch.Value & "|"
        Next
        FindString = Left(FindString, Len(FindString) - 1)
    Else
        FindString = "Not found"
    End If
    Set objRegEx = Nothing
    Set colMatches = Nothing
    Set objMatch = Nothing
End Function

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for Jonathan Xiang who asked for a knock-off of rev 30 with a few changes. Specifically

1. I would like to hard-code the sub-folders that the macro has to extract the messages from; And only those folders have to be processed.
2. Instead of a date range; I would like to extract the data of a specific date.
3. If the subject line of the message is empty, then it should skip exporting that message to excel.
4. When extracting to an excel file it should automatically load a pre-defined template and extract the data into “sheet1″.

This rev incorporates all the changes Jonathan asked for. Jonathan can use the instructions from the original post to add the code to Outlook. He will also need to edit two lines at the top of the code per the comments I included in it.

Const MACRO_NAME = "Export Messages to Excel (Rev 32)"
'On the next line edit the list of Outlook folders you want to process. Each folder name must be the path to a folder. Folder names are separated by a comma.
Const FOLDER_LIST = "mailbox\Projects\Project1,personal folders\marketing"
'On the next line edit the path to and name of the Excel template file.
Const TEMPLATE_NAME = "C:\Cooldude_Template.xlsx"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVersion As Integer, _
    lngMessages As Long, _
    lngRow As Long, _
    datStart As Date, _
    datEnd As Date

Private Sub ExportMessagesToExcel()
    Dim strFilename As String, strDateRange As String, arrFolders As Variant, varFolder As Variant
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        strDateRange = InputBox("Enter the date you want to export messages for in the form ""mm/dd/yy""", MACRO_NAME, Date)
        If IsDate(strDateRange) Then
            datStart = strDateRange & " 12:00am"
            datEnd = strDateRange & " 11:59pm"
        Else
            datStart = Date & " 12:00am"
            datEnd = Date & " 11:59pm"
        End If
        lngMessages = 0
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add(TEMPLATE_NAME)
        Set excWks = excWkb.Worksheets(1)
        lngRow = excWks.UsedRange.Rows.Count + 1
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        arrFolders = Split(FOLDER_LIST, ",")
        For Each varFolder In arrFolders
            ProcessFolder OpenOutlookFolder(CStr(varFolder))
        Next
        excWkb.SaveAs strFilename
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & lngMessages & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkLst As Object, _
        olkMsg As Object
    If olkFld.DefaultItemType = olMailItem Then
        Set olkLst = olkFld.Items.Restrict("[ReceivedTime] >= '" & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        'Write messages to spreadsheet
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                If olkMsg.Subject <> "" Then
                    'Add a row for each field in the message you want to export
                    excWks.Cells(lngRow, 1) = olkMsg.Subject
                    excWks.Cells(lngRow, 2) = olkMsg.ReceivedTime
                    excWks.Cells(lngRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                    lngRow = lngRow + 1
                    lngMessages = lngMessages + 1
                End If
            End If
        Next
        Set olkMsg = Nothing
    End If
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.
    ' Written: 4/24/2009
    ' Author:  David Lee
    ' Outlook: All versions
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

I put together this revision for Jesper Jespersen who asked for a mashup of features from revisions 1, 3, and 7. From rev 1 Jesper wants the the ability to specifiy which Outlook folders to process. From rev 3 he wants the ability to process sub-folders. From rev 7 he wants the ability to append the exported data to an existing workbook rather than creating a new one. Jesper also asked for the ability to filter messages based on keywords in the subject line and to export either the time the message was sent or received depending on whether he sent or received the message. This revision contains everything Jesper asked for. Jesper can use the instructions from the original post to add the code to Outlook.

'On the next line edit the list of keywords to search for.  Each keyword will be separated from the previous keyword by a |.
Const KEYWORDS = "word1|word2"
Const MACRO_NAME = "Export Messages to Excel (Rev 33)"

Dim lngRow As Long, _
    lngMessages As Long, _
    intVersion As Integer, _
    excApp As Object, _
    excWkb As Object, _
    excWks As Object
    
Sub ExportController()
    intVersion = GetOutlookVersion()
    'Add as many exports as you need. Each export is controlled by a line like the following
    'The format of the command is ExportMessagesToExcel "Path to Workbook", "Sheet Number", "Path to Outlook Folder"
    ExportMessagesToExcel "C:\Folder1.xlsx", 1, "Personal Folders\Folder1"
    ExportMessagesToExcel "C:\Folder2.xlsx", 1, "Personal Folders\Folder2"
    MsgBox "Process complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Sub ExportMessagesToExcel(strFilename As String, intSheet As Integer, strFolderPath As String)
    Dim olkFld As Object
    If strFilename = "" Then
        MsgBox "The filename was empty.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        If strFolderPath = "" Then
            MsgBox "The folderpath was empty.", vbCritical + vbOKOnly, MACRO_NAME
        Else
            Set olkFld = OpenOutlookFolder(strFolderPath)
            If TypeName(olkFld) = "Nothing" Then
                MsgBox "The folder pointed to by the path '" & strFolderPath & "' does not exist in Outlook.", vbCritical + vbOKOnly, MACRO_NAME
            Else
                Set excApp = CreateObject("Excel.Application")
                Set excWkb = excApp.Workbooks.Open(strFilename)
                Set excWks = excWkb.Worksheets(intSheet)
                lngRow = excWks.UsedRange.rows.Count + 1
                ProcessFolder olkFld
                excWkb.Close SaveChanges:=True
            End If
        End If
    End If
    Set olkFld = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, olkSub As Outlook.MAPIFolder
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            If ContainsKeyword(olkMsg.Subject) Then
                excWks.Cells(lngRow, 1) = olkMsg.Subject
                excWks.Cells(lngRow, 2) = IIf(olkMsg.SenderEmailAddress = Session.CurrentUser.Address, olkMsg.SentOn, olkMsg.ReceivedTime)
                excWks.Cells(lngRow, 3) = olkMsg.To
                excWks.Cells(lngRow, 4) = olkMsg.SenderEmailAddress
                excWks.Cells(lngRow, 5) = olkMsg.Parent.FolderPath
                lngRow = lngRow + 1
                lngMessages = lngMessages + 1
            End If
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkssub = Nothing
End Sub

Private Function ContainsKeyword(strText As String) As Boolean
    Dim objRegEx As Object, colMatches As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = True
        .Pattern = KEYWORDS
        .Global = True
    End With
    Set colMatches = objRegEx.Execute(strText)
    ContainsKeyword = (colMatches.Count > 0)
    Set objRegEx = Nothing
    Set colMatches = Nothing
End Function

Public Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.'
    ' Written: 4/24/2009'
    ' Author:  BlueDevilFan'
    ' Outlook: All versions'
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Shawn who asked for a version that will

  • Create a new spreadsheet or update an existing spreadsheet.
  • Export messages from a folder based on a date range.
  • Save all attachments from the processed messages to a file system folder.
  • Hyperlink to each saved attachment from the spreadsheet.

To avoid the possibility of duplicate file names, Shawn asked to have a date stamp added to each attachment’s file name.

Shawn can use the instructions from the original post to add the code to Outlook.

'--> Declare some constants
'Edit the path on the next line.  This is the folder the code will save the attachments to.  Make sure the path ends with a \
Const EXPORT_FOLDER = "C:\Users\David\Documents\TestArea\Shawn_Tester\"
Const MACRO_NAME = "Export Messages to Excel (Rev 34)"

Private Sub ExportMessagesToExcel()
    '--> Declare some variables
    Dim olkLst As Object, _
        olkMsg As Object, _
        olkAtt As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        objFSO As Object, _
        lngRow As Long, _
        lngCol As Long, _
        lngCnt As Long, _
        intVersion As Integer, _
        strFilename As String, _
        strAttFilename As String, _
        strDateRange As String, _
        arrTemp As Variant, _
        datStart As Date, _
        datEnd As Date, _
        bolNew As Boolean
        
    '--> Initialize some variables
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    intVersion = GetOutlookVersion()
    Set excApp = CreateObject("Excel.Application")
    
    '--> Get a date range from the user.  Parse what they enter into starting and ending dates.
    strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
    arrTemp = Split(strDateRange, "to")
    datStart = IIf(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
    datEnd = IIf(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
    
    '--> Main routine
    If IsDate(datStart) And IsDate(datEnd) Then
        Set olkLst = Application.ActiveExplorer.CurrentFolder.Items.Restrict("[ReceivedTime] >= '" & " " & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        If olkLst.Count > 0 Then
            strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
            If strFilename = "" Then
                MsgBox "You did not enter a filename.  Operation cancelled.", vbInformation + vbOKOnly, MACRO_NAME
            Else
                If objFSO.FileExists(strFilename) Then
                    Set excWkb = excApp.Workbooks.Open(strFilename)
                    Set excWks = excWkb.Worksheets(1)
                    lngRow = excWks.UsedRange.rows.Count + 1
                Else
                    Set excWkb = excApp.Workbooks.Add()
                    Set excWks = excWkb.Worksheets(1)
                    lngRow = 2
                    With excWks
                        .Cells(1, 1) = "Subject"
                        .Cells(1, 2) = "Received"
                        .Cells(1, 3) = "Sender"
                        .Cells(1, 4) = "Attachments"
                    End With
                    bolNew = True
                End If
                For Each olkMsg In olkLst
                    If olkMsg.Class = olMail Then
                        lngCol = 4
                        'Add a row for each field in the message you want to export
                        excWks.Cells(lngRow, 1) = olkMsg.Subject
                        excWks.Cells(lngRow, 2) = olkMsg.ReceivedTime
                        excWks.Cells(lngRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                        For Each olkAtt In olkMsg.Attachments
                            If Not IsHiddenAttachment(olkAtt) Then
                                strAttFilename = EXPORT_FOLDER & objFSO.GetBaseName(olkAtt.Filename) & Format(olkMsg.ReceivedTime, "yyyy-mm-dd-hh-nn-ss") & "." & objFSO.GetExtensionName(olkAtt.Filename)
                                olkAtt.SaveAsFile strAttFilename
                                excWks.Hyperlinks.Add Anchor:=excWks.Range(NumberToLetter(lngCol) & lngRow), Address:="FILE:\\" & strAttFilename, TextToDisplay:=olkAtt.Filename
                                lngCol = lngCol + 1
                            End If
                        Next
                        lngRow = lngRow + 1
                        lngCnt = lngCnt + 1
                    End If
                Next
                If bolNew Then
                    excWkb.SaveAs strFilename
                    excWkb.Close
                Else
                    excWkb.Close True
                End If
            End If
        Else
            MsgBox "I did not find any items that arrived between " & datStart & " and " & datEnd & ".  Operation cancelled.", vbInformation + vbOKOnly, MACRO_NAME
        End If
    End If

    '--> Destroy all objects
    Set olkLst = Nothing
    Set olkMsg = Nothing
    Set olkAtt = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    Set objFSO = Nothing
    
    '--> Let the user know how many items were exported
    MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function NumberToLetter(ByVal intNumber As Integer) As String
    NumberToLetter = Chr(64 + intNumber)
End Function

I created this revision for Stefan. Stefan receives messages containing a single HTML table. He asked if I could build a solution that parses the table and exports the data to Excel. The table contains a variable number of rows with each row containing a fixed number of columns. Stefan wants the exported data to be laid out exactly as it was in the table. One of the columns contains a very long number which Excel normally displays using scientific notation (e.g. 1.00E+12). Stefan asked if I could also force that column to display as a regular number.

Stefan can use the instructions from the original post to add the code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 35)"

Private Sub ExportMessagesToExcel()
    Const CELLS_PER_ROW = 14
    Dim olkFld As Outlook.MAPIFolder, _
        olkMsg As Outlook.MailItem, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        arrCel As Variant, _
        varCel As Variant, _
        lngRow As Long, _
        lngCol As Long, _
        intPtr As Integer
    Set olkFld = Session.PickFolder
    If TypeName(olkFld) = "Nothing" Then
        MsgBox "You did not select a folder.  Operation cancelled.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        excApp.Visible = True
        With excWks
            .Cells(1, 1) = "Time"
            .Cells(1, 2) = "ReceipientID"
            .Cells(1, 3) = "Shard"
            .Cells(1, 4) = "SenderEmail"
            .Cells(1, 5) = "Status"
            .Cells(1, 6) = "SenderClientIP"
            .Cells(1, 7) = "SenderID"
            .Cells(1, 8) = "SederName"
            .Cells(1, 9) = "Group"
            .Cells(1, 10) = "MailCount"
            .Cells(1, 11) = "SampleSubject"
            .Cells(1, 12) = "SampleNumber"
            .Cells(1, 13) = "SumNumber"
            .Cells(1, 14) = "SampleBody"
        End With
        lngRow = 2
        For Each olkMsg In olkFld.Items
            arrCel = Split(GetCells(olkMsg.HTMLBody), Chr(255))
            lngCol = 1
            For intPtr = LBound(arrCel) To UBound(arrCel)
                If lngCol = 2 Then
                    excWks.Cells(lngRow, lngCol).NumberFormat = "@"
                End If
                excWks.Cells(lngRow, lngCol).Value = arrCel(intPtr)
                If (intPtr + 1) Mod CELLS_PER_ROW = 0 Then
                    lngRow = lngRow + 1
                    lngCol = 1
                Else
                    lngCol = lngCol + 1
                End If
            Next
        Next
        excWks.Columns("A:N").AutoFit
        excApp.Visible = True
        Set excWks = Nothing
        Set excWkb = Nothing
        Set excApp = Nothing
    End If
    Set olkFld = Nothing
End Sub

Private Function GetCells(strHTML As String) As String
    Const READYSTATE_COMPLETE = 4
    Dim objIE As Object, objDoc As Object, colCells As Object, objCell As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    Do Until objIE.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    objIE.document.Body.innerHTML = strHTML
    Set objDoc = objIE.document
    Set colCells = objDoc.getElementsByTagName("td")
    If colCells.Length > 0 Then
        For Each objCell In colCells
            GetCells = GetCells & objCell.innerText & Chr(255)
        Next
        GetCells = Left(GetCells, Len(GetCells) - 1)
    Else
        GetCells = ""
    End If
    Set objCell = Nothing
    Set colCells = Nothing
    Set objDoc = Nothing
    objIE.Quit
    Set objIE = Nothing
End Function 

I created this revision for Luis. It is identical to the code in the original post with the exception that Luis also needs the To and Body fields of the message.

Luis can use the instructions from the original post to add the code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 36)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intCnt As Integer, _
        intVersion As Integer, _
        strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename <> "" Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Sender"
            .Cells(1, 2) = "Receiver"
            .Cells(1, 3) = "Received"
            .Cells(1, 4) = "Subject"
            .Cells(1, 5) = "Body"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = GetSMTPAddress(olkMsg, intVersion)
                excWks.Cells(intRow, 2) = olkMsg.To
                excWks.Cells(intRow, 3) = olkMsg.ReceivedTime
                excWks.Cells(intRow, 4) = olkMsg.Subject
                excWks.Cells(intRow, 5) = olkMsg.Body
                intRow = intRow + 1
                intCnt = intCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFilename
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.VERSION, ".")
    GetOutlookVersion = arrVer(0)
End Function

Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for a reader named Cleverson who asked for a couple of changes to the original version. Specifically, Cleverson asked for a version that exports a given folder each time rather than having to select a folder to export. He also asked me to rearrange the order of the exported fields.

Cleverson can use the instructions from the original post to add the code to Outlook.

'On the next line edit the path to the Outlook folder you want to export
Const FOLDER_PATH = "Mailbox - Doe, John\Inbox"
Const MACRO_NAME = "Export Messages to Excel (Rev 37)"

Private Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        olkFld As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intCnt As Integer, _
        intVersion As Integer, _
        strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename = "" Then
        MsgBox "You did not enter the path and name of a file to save the export to.  Export cancelled.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        Set olkFld = OpenOutlookFolder(FOLDER_PATH)
        If TypeName(olkFld) = "Nothing" Then
            MsgBox "The folder " & FOLDER_PATH & " does not exist.  Export cancelled.", vbCritical + vbOKOnly, MACRO_NAME
        Else
            intVersion = GetOutlookVersion()
            Set excApp = CreateObject("Excel.Application")
            Set excWkb = excApp.Workbooks.Add()
            Set excWks = excWkb.Worksheets(1)
            'Write Excel Column Headers
            With excWks
                .Cells(1, 1) = "Received"
                .Cells(1, 2) = "Sender"
                .Cells(1, 3) = "Subject"
            End With
            intRow = 2
            'Write messages to spreadsheet
            For Each olkMsg In olkFld.Items
                'Only export messages, not receipts or appointment requests, etc.
                If olkMsg.Class = olMail Then
                    'Add a row for each field in the message you want to export
                    excWks.Cells(intRow, 1) = olkMsg.ReceivedTime
                    excWks.Cells(intRow, 2) = GetSMTPAddress(olkMsg, intVersion)
                    excWks.Cells(intRow, 3) = olkMsg.Subject
                    intRow = intRow + 1
                    intCnt = intCnt + 1
                End If
            Next
            Set olkMsg = Nothing
            excWks.Columns("A:C").AutoFit
            excWkb.SaveAs strFilename
            excWkb.Close
            MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
        End If
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.
    ' Written: 4/24/2009
    ' Author:  David Lee
    ' Outlook: All versions
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function

This revision goes out to a reader named Allen who needs to export data from a custom Outlook form rather than a stock Outlook message. Allen only wants to export the user-defined fields he’s added to his custom form. While this revision will only work for Allen’s form, it demonstrates how an export can handle custom form fields. All you need to know is the name of the custom form (required to ensure that we only process instances of that form and not regular messages which wouldn’t have the custom fields) and the names of the fields on the custom form.

Allen will use the instructions from the original post to add this code to Outlook.

Sub ExportMessagesToExcel()
    'On the next line edit the custom form's name as needed.
    Const CUSTOM_FORM_NAME = "IPM.Note.Near Miss Log"
    Const MACRO_NAME = "Export Messages to Excel (Rev 38)"
    Dim olkMsg As Object, _
        olkUDP As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        lngCnt As Long, _
        strFilename As String
    strFilename = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFilename = "" Then
        MsgBox "You failed to enter a filename.  Export cancelled.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Incident"
            .Cells(1, 2) = "Corrective Action"
            .Cells(1, 3) = "GRG"
            .Cells(1, 4) = "Incident Date"
            .Cells(1, 5) = "Location Drop"
            .Cells(1, 6) = "Location Name"
            .Cells(1, 7) = "Medical Treatment"
            .Cells(1, 8) = "Message"
            .Cells(1, 9) = "Name1"
            .Cells(1, 10) = "Potential Root Cause"
            .Cells(1, 11) = "Time1"
            .Cells(1, 12) = "Work Area"
        End With
        lngRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages that use a specific custom form
            If olkMsg.MessageClass = CUSTOM_FORM_NAME Then
                'Add a row for each field in the message you want to export
                For Each olkUDP In olkMsg.UserProperties
                    Select Case olkUDP.Name
                        Case "Brief Description of Incident:"
                            excWks.Cells(lngRow, 1) = olkUDP.Value
                        Case "Description of Corrective Action"
                            excWks.Cells(lngRow, 2) = olkUDP.Value
                        Case "GRG"
                            excWks.Cells(lngRow, 3) = olkUDP.Value
                        Case "Incident Date:"
                            excWks.Cells(lngRow, 4) = olkUDP.Value
                        Case "Location Drop"
                            excWks.Cells(lngRow, 5) = olkUDP.Value
                        Case "Location Name:"
                            excWks.Cells(lngRow, 6) = olkUDP.Value
                        Case "Medical Treatment"
                            excWks.Cells(lngRow, 7) = olkUDP.Value
                        Case "Message"
                            excWks.Cells(lngRow, 8) = olkMsg.Body
                        Case "name1"
                            excWks.Cells(lngRow, 9) = olkUDP.Value
                        Case "Potential Root Cause:"
                            excWks.Cells(lngRow, 10) = olkUDP.Value
                        Case "Time1"
                            excWks.Cells(lngRow, 11) = olkUDP.Value
                        Case "Work Area:"
                            excWks.Cells(lngRow, 12) = olkUDP.Value
                    End Select
                Next
                lngRow = lngRow + 1
                lngCnt = lngCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        Set olkUDP = Nothing
        excWks.Columns("A:L").AutoFit
        excWkb.SaveAs strFilename
        excWkb.Close
        MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
End Sub

This revision is for Bobby, a reader who asked for a version to handle exporting messages with a unique format. Bobby receives messages about new hires in his company. For each of these messages, Bobby needs to export the new employee’s name, the date they begin work, and the location they’ll be working at. Bobby wants to write these messages to an existing spreadsheet. He will run the macro manually and it will always look for the messages in a specific folder.

Bobby will use the instructions from the original post to add this code to Outlook.

'--> On the next line edit the path to and name of the workbook the extracted data is to be written to
Const EXCEL_WORKBOOK = "C:\Users\Bobby\Documents\Bobby.xlsx"
'--> On the next line edit the path to the Outlook folder containing the messages to be exported
Const OUTLOOK_FOLDER = "Mailbox - Bobby\Inbox\New Hires"
Const NOTE_NAME = "Export Messages to Excel - Last Run"
Const MACRO_NAME = "Export Messages to Excel (Rev 39)"

Sub ExportMessagesToExcel()
    Dim olkFld As Object, _
        olkMsg As Object, _
        olkNot As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        lngCnt As Long, _
        strFil As String, _
        strTmp As String, _
        arrLin As Variant, _
        varLin As Variant, _
        datLst As Date
    On Error Resume Next
    Set olkNot = Session.GetDefaultFolder(olFolderNotes).Items.Item(NOTE_NAME)
    On Error GoTo 0
    If TypeName(olkNot) = "Nothing" Then
        Set olkNot = Application.CreateItem(olNoteItem)
        olkNot.Body = NOTE_NAME & vbCrLf & #1/1/2000 12:01:00 AM#
        olkNot.Save
    End If
    datLst = Replace(olkNot.Body, NOTE_NAME & vbCrLf, "")
    Set olkFld = OpenOutlookFolder(OUTLOOK_FOLDER).Items.Restrict("[ReceivedTime] > '" & Format(datLst, "ddddd h:nn AMPM") & "'")
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(EXCEL_WORKBOOK)
    Set excWks = excWkb.Worksheets(1)
    lngRow = excWks.UsedRange.rows.Count + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            arrLin = Split(olkMsg.Body, vbCrLf)
            For Each varLin In arrLin
                strTmp = Trim(varLin)
                If Left(strTmp, 14) = "Employee Name:" Then
                    excWks.Cells(lngRow, 1) = Trim(Mid(strTmp, 15))
                Else
                    If Left(strTmp, 11) = "Start Date:" Then
                        excWks.Cells(lngRow, 2) = Trim(Mid(strTmp, 12))
                    Else
                        If Left(strTmp, 14) = "Work Location:" Then
                            excWks.Cells(lngRow, 3) = Trim(Mid(strTmp, 15))
                        End If
                    End If
                End If
            Next
            lngRow = lngRow + 1
            lngCnt = lngCnt + 1
        End If
    Next
    olkNot.Body = NOTE_NAME & vbCrLf & Now
    olkNot.Save
    Set olkNot = Nothing
    Set olkFld = Nothing
    Set olkMsg = Nothing
    excWks.Columns("A:C").AutoFit
    excWkb.Close True
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function OpenOutlookFolder(strFolderPath As String) As Outlook.MAPIFolder
    ' Purpose: Opens an Outlook folder from a folder path.
    ' Written: 4/24/2009
    ' Author:  David Lee
    ' Outlook: All versions
    Dim arrFolders As Variant, _
        varFolder As Variant, _
        bolBeyondRoot As Boolean
    On Error Resume Next
    If strFolderPath = "" Then
        Set OpenOutlookFolder = Nothing
    Else
        Do While Left(strFolderPath, 1) = "\"
            strFolderPath = Right(strFolderPath, Len(strFolderPath) - 1)
        Loop
        arrFolders = Split(strFolderPath, "\")
        For Each varFolder In arrFolders
            Select Case bolBeyondRoot
                Case False
                    Set OpenOutlookFolder = Outlook.Session.Folders(varFolder)
                    bolBeyondRoot = True
                Case True
                    Set OpenOutlookFolder = OpenOutlookFolder.Folders(varFolder)
            End Select
            If Err.Number <> 0 Then
                Set OpenOutlookFolder = Nothing
                Exit For
            End If
        Next
    End If
    On Error GoTo 0
End Function
 

I put this revision together for a reader named chanakkiyan. Chanakkiyan asked for a version that merges Rev9’s ability to export the entire mailbox with Rev34’s ability to save and hyperlink to the attachments of each exported message.

Chanakkiyan will use the instructions from the original post to add this code to Outlook.

'--> Declare some constants
'On the next line, edit the path on the next line.  This is the folder the code will save the attachments to.  Make sure the path ends with a \
Const EXPORT_FOLDER = "C:\Users\David\Documents\TestArea\chanakkiyan\files\"
'On the next line, edit the path to the spreadsheet you want to export to.
Const EXCEL_WORKSHEET = "C:\Users\David\Documents\TestArea\chanakkiyan\chanakkiyan.xlsx"
Const MACRO_NAME = "Export Messages to Excel (Rev 40)"
 
Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    objFSO As Object, _
    intVer As Integer, _
    lngRow As Long

Sub ExportController()
    Dim olkSto As Object, _
        olkFld As Object

    '--> Initialize some variables
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    intVer = GetOutlookVersion()
    
    '--> Connect to Excel
    Set excApp = CreateObject("Excel.Application")
    Set excWkb = excApp.Workbooks.Open(EXCEL_WORKSHEET)
    Set excWks = excWkb.Worksheets(1)
    lngRow = excWks.UsedRange.rows.Count + 1
    
    '--> Main routine
    For Each olkSto In Session.Stores
        Set olkFld = olkSto.GetRootFolder
        ExportMessagesToExcel olkFld
    Next
    excWks.Columns("A:ZZ").AutoFit
    
    '--> Save and close the workbook
    excWkb.Close True
    
    '--> Destroy all objects
    Set olkSto = Nothing
    Set olkFld = Nothing
    Set objFSO = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    
    '--> Notify the user that the process is finished
    MsgBox "Export complete.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Sub ExportMessagesToExcel(olkFld As Outlook.MAPIFolder)
    '--> Declare some variables
    Dim olkMsg As Object, _
        olkSub As Object, _
        olkAtt As Object, _
        lngCol As Long, _
        strAFN As String, _
        arrTmp As Variant
         
    '--> Main routine
    'Only process folders containing emails, not contacts, calendars, tasks, or notes.
    If olkFld.DefaultItemType = olMailItem Then
        For Each olkMsg In olkFld.Items
            If olkMsg.Class = olMail Then
                lngCol = 5
                'Add a row for each field in the message you want to export
                excWks.Cells(lngRow, 1) = olkFld.FolderPath
                excWks.Cells(lngRow, 2) = olkMsg.Subject
                excWks.Cells(lngRow, 3) = olkMsg.ReceivedTime
                excWks.Cells(lngRow, 4) = GetSMTPAddress(olkMsg, intVer)
                For Each olkAtt In olkMsg.Attachments
                    If (Not IsHiddenAttachment(olkAtt)) And (Not olkAtt.Type = olOLE) Then
                        strAFN = EXPORT_FOLDER & objFSO.GetBaseName(olkAtt.Filename) & Format(olkMsg.ReceivedTime, "yyyy-mm-dd-hh-nn-ss") & "." & objFSO.GetExtensionName(olkAtt.Filename)
                        olkAtt.SaveAsFile strAFN
                        excWks.Hyperlinks.Add Anchor:=excWks.Range(NumberToLetter(lngCol) & lngRow), Address:="FILE:\\" & strAFN, TextToDisplay:=olkAtt.Filename
                        lngCol = lngCol + 1
                    End If
                Next
                lngRow = lngRow + 1
            End If
        Next
        'Process all the subfolders under this folder
        For Each olkSub In olkFld.Folders
            ExportMessagesToExcel olkSub
        Next
    End If

    '--> Destroy all objects
    Set olkMsg = Nothing
    Set olkSub = Nothing
    Set olkAtt = Nothing
End Sub
 
Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function
 
Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function
 
Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function
 
Private Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function
 
Private Function NumberToLetter(ByVal intNumber As Integer) As String
    NumberToLetter = Chr(64 + intNumber)
End Function

I put this revision together for a reader named Singh. Singh has multiple mailboxes and wants to be able to export all the messages from a given mailbox. In rev 3 I showed how to export all the messages from a folder and all it’s subfolders, while in rev 9 I showed how to export messages from every store (mailbox or PST file) in Outlook. In this revision I’ve modified the code from rev 3 to handle just one store (mailbox or PST file).

Singh will use the instructions from the original post to add this code to Outlook.

'On the next line edit the name of the store (mailbox) you want to export
Const STORE_NAME = "Mailbox - Singh"
Const MACRO_NAME = "Export Messages to Excel (Rev 41)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVer As Integer, _
    intCnt As Integer

Sub ExportMessagesToExcel()
    Dim strFil As String
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFil <> "" Then
        intCnt = 0
        intVer = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
        End With
        ProcessFolder GetRootFolder(STORE_NAME)
        excWkb.SaveAs strFil
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, olkSub As Outlook.MAPIFolder, intRow As Integer
    intRow = excWks.UsedRange.rows.Count
    intRow = intRow + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(intRow, 1) = olkMsg.Subject
            excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
            excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVer)
            intRow = intRow + 1
            intCnt = intCnt + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkssub = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function GetRootFolder(strStoreName As String) As Outlook.MAPIFolder
    Dim olkSto As Outlook.Store
    On Error Resume Next
    If strStoreName = "" Then
        Set GetRootFolder = Nothing
    Else
        Set olkSto = Session.Stores.Item(strStoreName)
        If TypeName(olkSto) <> "Nothing" Then
            Set GetRootFolder = olkSto.GetRootFolder
        End If
    End If
    On Error GoTo 0
    Set olkSto = Nothing
End Function

This revision is for David G., a reader who needs to export all the messages in the currently selected folder and all of its sub-folders. For each message, David wants to export the subject, date received, the sender, who the message was sent to, a list of the message’s attachments, who the message was CC’d to, the message body, and the message’s internet header. This solution will only work in Outlook 2007 and later.

David G. will use the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 42)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVer As Integer, _
    intCnt As Integer

Sub ExportMessagesToExcel()
    Dim strFil As String
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFil <> "" Then
        intCnt = 0
        intVer = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Date"
            .Cells(1, 3) = "From"
            .Cells(1, 4) = "To"
            .Cells(1, 5) = "Attachment"
            .Cells(1, 6) = "CC"
            .Cells(1, 7) = "Body"
            .Cells(1, 8) = "Header"
        End With
        ProcessFolder Application.ActiveExplorer.CurrentFolder
        excWks.Columns("A:I").AutoFit
        excWkb.SaveAs strFil
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, _
        olkAtt As Object, _
        olkSub As Object, _
        intRow As Integer, _
        strAtt As String
    intRow = excWks.UsedRange.rows.Count
    intRow = intRow + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Get the names of all attachments
            strAtt = ""
            For Each olkAtt In olkMsg.Attachments
                If Not IsHiddenAttachment(olkAtt) And olkAtt.Type <> olOLE Then
                    strAtt = strAtt & olkAtt.Filename & ", "
                End If
            Next
            If Len(strAtt) > 0 Then
                strAtt = Left(strAtt, Len(strAtt) - 2)
            End If
            'Add a row for each field in the message you want to export
            With excWks
                .Cells(intRow, 1) = olkMsg.Subject
                .Cells(intRow, 2) = olkMsg.ReceivedTime
                .Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVer)
                .Cells(intRow, 4) = olkMsg.To
                .Cells(intRow, 5) = strAtt
                .Cells(intRow, 6) = olkMsg.CC
                .Cells(intRow, 7) = olkMsg.Body
                .Cells(intRow, 8) = GetInetHeaders(olkMsg)
            End With
            intRow = intRow + 1
            intCnt = intCnt + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkSub = Nothing
    Set olkMsg = Nothing
    Set olkAtt = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function GetInetHeaders(olkMsg As Outlook.MailItem) As String
    ' Purpose: Returns the internet headers of a message.'
    ' Written: 4/28/2009'
    ' Author:  David Lee'
    ' Outlook: 2007'
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim olkPA As Outlook.PropertyAccessor
    Set olkPA = olkMsg.PropertyAccessor
    GetInetHeaders = olkPA.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
    Set olkPA = Nothing
End Function

Private Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    ' Purpose: Determines if an attachment is a hidden attachment.
    ' Written: 7/12/2012
    ' Author:  David Lee
    ' Outlook: 2007 and later
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

This revision is for Abdul who needs to export data from messages containing a 2×4 table. Each message will contain just one table in the message body. The table’s first column contains labels, the second contains the actual data. This solution is a knock-off of the solution I first introduced in revision 27.

Abdul will use the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 43)"

Private Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        intRow As Integer, _
        intCnt As Integer, _
        strFil As String, _
        arrCel As Variant
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFil <> "" Then
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Name"
            .Cells(1, 2) = "ID"
            .Cells(1, 3) = "Education"
            .Cells(1, 4) = "State"
        End With
        intRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Get the cells from the table in the body of the message
                arrCel = Split(GetCells(olkMsg.HTMLBody), Chr(255))
                'Add a row for each field in the message you want to export
                excWks.Cells(intRow, 1) = arrCel(1)
                excWks.Cells(intRow, 2) = arrCel(3)
                excWks.Cells(intRow, 3) = arrCel(5)
                excWks.Cells(intRow, 4) = arrCel(7)
                intRow = intRow + 1
                intCnt = intCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFil
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetCells(strHTML As String) As String
    Const READYSTATE_COMPLETE = 4
    Dim objIE As Object, objDoc As Object, colCells As Object, objCell As Object
    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.navigate "about:blank"
    Do Until objIE.readyState = READYSTATE_COMPLETE
        DoEvents
    Loop
    objIE.document.Body.innerHTML = strHTML
    Set objDoc = objIE.document
    Set colCells = objDoc.getElementsByTagName("td")
    If colCells.Length > 0 Then
        For Each objCell In colCells
            GetCells = GetCells & objCell.innerText & Chr(255)
        Next
        GetCells = Left(GetCells, Len(GetCells) - 1)
    Else
        GetCells = ""
    End If
    Set objCell = Nothing
    Set colCells = Nothing
    Set objDoc = Nothing
    Set objIE = Nothing
End Function

This revision is for Wayne who needs to parse three pieces of data from the body of certain messages. This solution is a knock-off of the solution I first introduced in revision 14.

Wayne will use the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 44)"

Sub ExportMessagesToExcel()
    Dim olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Integer, _
        lngCnt As Long, _
        strFil As String, _
        strTmp As String, _
        arrLin As Variant, _
        varLin As Variant
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFil <> "" Then
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Received"
            .Cells(1, 2) = "Name"
            .Cells(1, 3) = "Contact No"
            .Cells(1, 4) = "Email Address"
        End With
        lngRow = 2
        'Write messages to spreadsheet
        For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(lngRow, 1) = olkMsg.ReceivedTime
                arrLin = Split(olkMsg.Body, vbCrLf)
                For Each varLin In arrLin
                    strTmp = Trim(varLin)
                    If Left(strTmp, 6) = "Name: " Then
                        excWks.Cells(lngRow, 2) = Mid(strTmp, 7)
                    Else
                        If Left(strTmp, 12) = "Contact No: " Then
                            excWks.Cells(lngRow, 3) = Mid(strTmp, 13)
                        Else
                            If Left(strTmp, 15) = "Email Address: " Then
                                excWks.Cells(lngRow, 4) = Mid(strTmp, 16)
                            End If
                        End If
                    End If
                Next
                lngRow = lngRow + 1
                lngCnt = lngCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFil
        excWkb.Close
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

This revision is for Lin who needs to export some basic information (e.g. subject, sender, date received) plus the message body, up to a certain phrase, for all message received in a certain time-frame. The solution is a knock-off of the solution from revision 4 that adds the ability to extract the body of the message up to a designated key phrase. In Lin’s case, that key phrase is the beginning of a disclaimer that appears in each message.

Lin will use the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 45)"
 
Sub ExportMessagesToExcel()
    Dim olkLst As Object, _
        olkMsg As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        lngCnt As Long, _
        intVer As Integer, _
        strFil As String, _
        strRng As String, _
        arrTmp As Variant, _
        datBeg As Date, _
        datEnd As Date
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFil <> "" Then
        strRng = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
        arrTmp = Split(strRng, "to")
        datBeg = IIf(IsDate(arrTmp(0)), arrTmp(0), Date) & " 12:00am"
        datEnd = IIf(IsDate(arrTmp(1)), arrTmp(1), Date) & " 11:59pm"
        intVer = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add()
        Set excWks = excWkb.ActiveSheet
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Subject"
            .Cells(1, 2) = "Received"
            .Cells(1, 3) = "Sender"
            .Cells(1, 4) = "Body"
        End With
        lngRow = 2
        'Write messages to spreadsheet
        Set olkLst = Application.ActiveExplorer.CurrentFolder.Items.Restrict("[ReceivedTime] >= '" & Format(datBeg, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
        For Each olkMsg In olkLst
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                'Add a row for each field in the message you want to export
                excWks.Cells(lngRow, 1) = olkMsg.Subject
                excWks.Cells(lngRow, 2) = olkMsg.ReceivedTime
                excWks.Cells(lngRow, 3) = GetSMTPAddress(olkMsg, intVer)
                excWks.Cells(lngRow, 4) = GetMsgBody(olkMsg.Body)
                lngRow = lngRow + 1
                lngCnt = lngCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        excWkb.SaveAs strFil
        excWkb.Close
    End If
    Set olkLst = Nothing
    Set excWks = Nothing
    Set excWkb = Nothing
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub
 
Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function
 
Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function
 
Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Function GetMsgBody(strTxt As String) As String
    Const TEXT_TO_FIND = "The information contained in this message is intended only for the recipient, and may be a confidential"
    Dim lngPos As Long
    lngPos = InStr(1, strTxt, TEXT_TO_FIND, vbTextCompare)
    If lngPos > 0 Then
        GetMsgBody = Left(strTxt, lngPos - 1)
    Else
        GetMsgBody = strTxt
    End If
End Function

I put this revision together for Jörg who asked for a knock-off of rev 42 with a few additions. Specifically

  1. Adds the BCC recipients (will only show up when he runs it against a folder of sent items)
  2. The path to the folder the message is in
  3. A count of the number of recipients the message is addressed to
  4. The number of attachments
  5. Split the time received into two columns with the date in one and the time in the other

Jörg will use the instructions from the original post to add this code to Outlook.

Const MACRO_NAME = "Export Messages to Excel (Rev 46)"

Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVer As Integer, _
    intCnt As Integer

Sub ExportMessagesToExcel()
    Dim strFil As String
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
    If strFil <> "" Then
        intCnt = 0
        intVer = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        'Write Excel Column Headers
        With excWks
            .Cells(1, 1) = "Folder"
            .Cells(1, 2) = "Subject"
            .Cells(1, 3) = "Date"
            .Cells(1, 4) = "Time"
            .Cells(1, 5) = "From"
            .Cells(1, 6) = "To"
            .Cells(1, 7) = "Attachment"
            .Cells(1, 8) = "CC"
            .Cells(1, 9) = "BCC"
            .Cells(1, 10) = "Body"
            .Cells(1, 11) = "Header"
            .Cells(1, 12) = "Recipients"
            .Cells(1, 13) = "Attachments"
        End With
        ProcessFolder Application.ActiveExplorer.CurrentFolder
        excWks.Columns("A:I").AutoFit
        excWks.Columns("K:M").AutoFit
        excWkb.SaveAs strFil
    End If
    Set excWks = Nothing
    Set excWkb = Nothing
    excApp.Quit
    Set excApp = Nothing
    MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, _
        olkAtt As Object, _
        olkSub As Object, _
        intRow As Integer, _
        strAtt As String
    intRow = excWks.UsedRange.rows.Count
    intRow = intRow + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            Debug.Print olkMsg.Subject
            'Get the names of all attachments
            strAtt = ""
            For Each olkAtt In olkMsg.Attachments
                If Not IsHiddenAttachment(olkAtt) And olkAtt.Type <> olOLE Then
                    strAtt = strAtt & olkAtt.Filename & ", "
                End If
            Next
            If Len(strAtt) > 0 Then
                strAtt = Left(strAtt, Len(strAtt) - 2)
            End If
            'Add a row for each field in the message you want to export
            With excWks
                .Cells(intRow, 1) = olkMsg.Parent.FolderPath
                .Cells(intRow, 2) = olkMsg.Subject
                .Cells(intRow, 3) = Format(olkMsg.ReceivedTime, "yymmdd")
                .Cells(intRow, 4) = Format(olkMsg.ReceivedTime, "hh:nn")
                .Cells(intRow, 5) = GetSMTPAddress(olkMsg, intVer)
                .Cells(intRow, 6) = olkMsg.To
                .Cells(intRow, 7) = strAtt
                .Cells(intRow, 8) = olkMsg.CC
                .Cells(intRow, 9) = olkMsg.BCC
                .Cells(intRow, 10) = olkMsg.Body
                .Cells(intRow, 11) = GetInetHeaders(olkMsg)
                .Cells(intRow, 12) = olkMsg.Recipients.Count
                .Cells(intRow, 13) = olkMsg.Attachments.Count
            End With
            intRow = intRow + 1
            intCnt = intCnt + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkSub = Nothing
    Set olkMsg = Nothing
    Set olkAtt = Nothing
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function GetInetHeaders(olkMsg As Outlook.MailItem) As String
    ' Purpose: Returns the internet headers of a message.'
    ' Written: 4/28/2009'
    ' Author:  David Lee'
    ' Outlook: 2007'
    Const PR_TRANSPORT_MESSAGE_HEADERS = "http://schemas.microsoft.com/mapi/proptag/0x007D001E"
    Dim olkPA As Outlook.PropertyAccessor
    Set olkPA = olkMsg.PropertyAccessor
    GetInetHeaders = olkPA.GetProperty(PR_TRANSPORT_MESSAGE_HEADERS)
    Set olkPA = Nothing
End Function

Private Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
    ' Purpose: Determines if an attachment is a hidden attachment.
    ' Written: 7/12/2012
    ' Author:  David Lee
    ' Outlook: 2007 and later
    Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
    Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
    On Error Resume Next
    Set olkPA = olkAtt.PropertyAccessor
    varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
    IsHiddenAttachment = (varTemp <> "")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for nbnamp, a reader who asked for a mashup of features from revs 3, 7, and 23. In nbnamp’s own words

Is there a way to mix rev.3, rev.7, and rev.23?
From rev.3: export emails from folder and its subfolders
From rev7: the data to be added to the bottom of an existing spreadsheet
And from rev.23: connect to an already open spreadsheet

This revision does all of those things.

Nbnamp will follow the instructions from the original post to add this code to Outlook.

'On the next line edit the name of the workbook
Const WBK_NAME = "Book1"
'On the next line edit the name of the worksheet
Const WKS_NAME = "Sheet1"
Const MACRO_NAME = "Export Messages to Excel (Rev 47)"
 
Dim excApp As Object, _
    excWkb As Object, _
    excWks As Object, _
    intVer As Integer, _
    lngMsg As Long
 
Sub ExportMessagesToExcel()
    On Error Resume Next
    Set excApp = GetObject(, "Excel.Application")
    If TypeName(excApp) = "Nothing" Then
        MsgBox "Excel is not open.  Operation aborted.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        Set excWkb = excApp.Workbooks.Item(WBK_NAME)
        If TypeName(excWkb) = "Nothing" Then
            MsgBox "The workbook " & WBK_NAME & " is not open.  Operation aborted.", vbCritical + vbOKOnly, MACRO_NAME
        Else
            On Error GoTo 0
            lngMsg = 0
            intVer = GetOutlookVersion()
            Set excWks = excWkb.Worksheets(WKS_NAME)
            ProcessFolder Application.ActiveExplorer.CurrentFolder
            excWkb.Save
            Set excWks = Nothing
            Set excWkb = Nothing
            Set excApp = Nothing
            MsgBox "Process complete.  A total of " & lngMsg & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
        End If
    End If
    On Error GoTo 0
End Sub
 
Private Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
    Dim olkMsg As Object, olkSub As Outlook.MAPIFolder, lngRow As Long
    lngRow = excWks.UsedRange.rows.Count
    lngRow = lngRow + 1
    'Write messages to spreadsheet
    For Each olkMsg In olkFld.Items
        'Only export messages, not receipts or appointment requests, etc.
        If olkMsg.Class = olMail Then
            'Add a row for each field in the message you want to export
            excWks.Cells(lngRow, 1) = GetSMTPAddress(olkMsg, intVer)
            excWks.Cells(lngRow, 2) = olkMsg.To
            excWks.Cells(lngRow, 3) = olkMsg.ReceivedTime
            excWks.Cells(lngRow, 4) = olkMsg.Subject
            excWks.Cells(lngRow, 5) = olkMsg.Body
            lngRow = lngRow + 1
            lngMsg = lngMsg + 1
        End If
    Next
    Set olkMsg = Nothing
    For Each olkSub In olkFld.Folders
        ProcessFolder olkSub
    Next
    Set olkssub = Nothing
End Sub
 
Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function
 
Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function
 
Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

I put this revision together for Arun Kumar who needs to export data from the body of certain messages. The script writes the data to an existing spreadsheet and avoids exporting items twice by tagging processed items with a category.

Arun will use the instructions from the original post to add this code to Outlook.

'On the next line, edit the path to the workbook the data will be exported to
Const WORKBOOK_NAME = "c:\users\arun\documents\testarea\arun_kumar.xlsx"
'On the next line, edit the name of the category the script will assign to items that it has processed
Const CATEGORY_NAME = "Arun"
'On the next line, edit the path to the root folder the attachments are to be saved under
Const ROOT_DIR = "D:\Work"
Const MACRO_NAME = "Export Messages to Excel (Rev 48)"

Private Sub ExportMessagesToExcel()
    Dim olkFld As Object, _
        olkRng As Object, _
        olkMsg As Object, _
        olkAtt As Object, _
        objFSO As Object, _
        excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        lngCnt As Long, _
        intVersion As Integer, _
        strTemp As String, _
        arrLines As Variant, _
        varLine As Variant, _
        strDat As String, _
        arrTmp As Variant, _
        datBeg As Date, _
        datEnd As Date, _
        strExt As String, _
        strDir As String, _
        strOrd As String
    'Write messages to spreadsheet
    strDat = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
    arrTmp = Split(strDat, "to")
    datBeg = IIf(IsDate(arrTmp(0)), arrTmp(0), Date) & " 12:00am"
    datEnd = IIf(IsDate(arrTmp(1)), arrTmp(1), Date) & " 11:59pm"
    Set olkFld = Application.ActiveExplorer.CurrentFolder
    Set olkRng = olkFld.Items.Restrict("[ReceivedTime] >= '" & Format(datBeg, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
    If olkRng.Count > 0 Then
        intVersion = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Open(WORKBOOK_NAME)
        Set excWks = excWkb.Worksheets(1)
        lngRow = excWks.UsedRange.rows.Count + 1
        Set objFSO = CreateObject("Scripting.FileSystemObject")
        For Each olkMsg In olkRng
            'Only export messages, not receipts or appointment requests, etc.
            If olkMsg.Class = olMail Then
                If Not IsTagged(olkMsg.Categories) Then
                    'Add a row for each field in the message you want to export
                    excWks.Cells(lngRow, 1) = olkMsg.ReceivedTime
                    excWks.Cells(lngRow, 2) = GetSMTPAddress(olkMsg, intVersion)
                    arrLines = Split(olkMsg.Body, vbCrLf)
                    For Each varLine In arrLines
                        strTemp = Trim(varLine)
                        If Left(strTemp, 10) = "Order No: " Then
                            strOrd = Mid(strTemp, 11)
                            excWks.Cells(lngRow, 3) = Mid(strTemp, 11)
                        Else
                            If Left(strTemp, 9) = "Loan No: " Then
                                excWks.Cells(lngRow, 4) = Mid(strTemp, 10)
                            Else
                                If Left(strTemp, 15) = "Borrower Name: " Then
                                    excWks.Cells(lngRow, 5) = Mid(strTemp, 16)
                                    bolComments = True
                                Else
                                    If Left(strTemp, 18) = "Property Address: " Then
                                        excWks.Cells(lngRow, 6) = Mid(strTemp, 19)
                                    Else
                                        If Left(strTemp, 16) = "Vendor Product: " Then
                                            excWks.Cells(lngRow, 7) = Mid(strTemp, 17)
                                        End If
                                    End If
                                End If
                            End If
                        End If
                    Next
                    'Save the Word attachment, if there is one
                    For Each olkAtt In olkMsg.Attachments
                        strExt = objFSO.GetExtensionName(olkAtt.Filename)
                        Select Case strExt
                            Case "doc", "docx"
                                strDir = ROOT_DIR & "\" & MonthName(Month(olkMsg.ReceivedTime), True)
                                If Not objFSO.FolderExists(strDir) Then objFSO.CreateFolder strDir
                                strDir = strDir & "\" & StrZero(Day(olkMsg.ReceivedTime), 2)
                                If Not objFSO.FolderExists(strDir) Then objFSO.CreateFolder strDir
                                strDir = strDir & "\" & strOrd
                                If Not objFSO.FolderExists(strDir) Then objFSO.CreateFolder strDir
                                olkAtt.SaveAsFile strDir & "\" & olkAtt.Filename
                        End Select
                    Next
                    'Tag the message so it isn't processed a second time
                    If Len(olkMsg.Categories) = 0 Then
                        olkMsg.Categories = CATEGORY_NAME
                    Else
                        olkMsg.Categories = olkMsg.Categories & "," & CATEGORY_NAME
                    End If
                    olkMsg.Save
                    lngRow = lngRow + 1
                    lngCnt = lngCnt + 1
                End If
            End If
        Next
        Set olkMsg = Nothing
        excWks.Columns("A:G").AutoFit
        excWkb.Close True
        Set excWks = Nothing
        Set excWkb = Nothing
        Set excApp = Nothing
    Else
        MsgBox "Operation cancelled.  There are no items in the range " & datBeg & " - " & datEnd, vbInformation + vbOKOnly, MACRO_NAME
    End If
    MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
End Sub

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

Private Function IsTagged(strCats As String) As Boolean
    Dim arrCats As Variant, varItem As Variant
    arrCats = Split(strCats, ",")
    For Each varItem In arrCats
        If varItem = CATEGORY_NAME Then
            IsTagged = True
            Exit For
        End If
    Next
End Function

Private Function StrZero(varNumber As Variant, intLength As Integer) As String
    ' Purpose: Pad a number with zeroes to the given length and return it as a string.'
    ' Written: 4/24/2009'
    ' Author:  TechnicLee'
    ' Outlook: All versions'
    Dim intItemLength As Integer
    If IsNumeric(varNumber) Then
        intItemLength = Len(CStr(Int(varNumber)))
        If intItemLength < intLength Then
            StrZero = String(intLength - intItemLength, "0") & varNumber
        Else
            StrZero = varNumber
        End If
    Else
        StrZero = varNumber
    End If
End Function 

I put this revision together for Anders who needs to export all the email addresses from the bodies of certain messages. The script writes the sender’s address to column A and the harvested addresses to separate columns beginning in column B. The solution saves the data from each run to a new spreadsheet.

Anders will use the instructions from the original post to add this code to Outlook.

Sub ExportAddresses()
    Const MACRO_NAME = "Export Addresses"
    Dim olkItm As Object, strBuf As String, arrAdr As Variant, varAdr As Variant, lngRow As Long, lngCol As Long, strFil As String, intVer As Integer
    Dim excApp As Object, excWkb As Object, excWks As Object
    strFil = InputBox("Enter a filename (including path) to save the exported messages to.", "Export Messages to Excel")
    If strFil = "" Then
        MsgBox "You must enter the name of a file to save the output to.  Processing cancelled.", vbCritical + vbOKOnly, MACRO_NAME
    Else
        intVer = GetOutlookVersion()
        Set excApp = CreateObject("Excel.Application")
        Set excWkb = excApp.Workbooks.Add
        Set excWks = excWkb.Worksheets(1)
        lngRow = 1
        For Each olkItm In Application.ActiveExplorer.CurrentFolder.Items
            If olkItm.Class = olMail Then
                strBuf = FindString(olkItm.Body, "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b")
                If strBuf <> "Not found" Then
                    excWks.Cells(lngRow, 1) = GetSMTPAddress(olkItm, intVer)
                    lngCol = 2
                    arrAdr = Split(strBuf, "|")
                    For Each varAdr In arrAdr
                        excWks.Cells(lngRow, lngCol) = varAdr
                        lngCol = lngCol + 1
                    Next
                    excWks.Columns("A:" & NumberToLetter(lngCol)).AutoFit
                    lngRow = lngRow + 1
                End If
            End If
        Next
        Set excWks = Nothing
        excWkb.SaveAs strFil
        excWkb.Close False
        Set excWkb = Nothing
        Set excApp = Nothing
        Set olkItm = Nothing
        MsgBox "Processing complete.", vbInformation + vbOKOnly, MACRO_NAME
    End If
End Sub

Private Function FindString(strText As String, strFind As String) As String
    Dim objRegEx As Object, colMatches As Object, objMatch As Object
    Set objRegEx = CreateObject("VBscript.RegExp")
    With objRegEx
        .IgnoreCase = True
        .Global = True
        .Pattern = strFind
        Set colMatches = .Execute(strText)
    End With
    If colMatches.Count > 0 Then
        For Each objMatch In colMatches
            FindString = FindString & objMatch.Value & "|"
        Next
        FindString = Left(FindString, Len(FindString) - 1)
    Else
        FindString = "Not found"
    End If
    Set objRegEx = Nothing
    Set colMatches = Nothing
    Set objMatch = Nothing
End Function

Private Function NumberToLetter(ByVal intNumber As Integer) As String
    Dim intTmp As Integer
    If intNumber <= 26 Then
        NumberToLetter = Chr(64 + intNumber)
    Else
        NumberToLetter = Chr(64 + Int(intNumber / 26)) & Chr(64 + intNumber Mod 26)
    End If
End Function

Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
    Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
    On Error Resume Next
    Select Case intOutlookVersion
        Case Is < 14
            If Item.SenderEmailType = "EX" Then
                GetSMTPAddress = SMTP2007(Item)
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
        Case Else
            Set olkSnd = Item.Sender
            If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                Set olkEnt = olkSnd.GetExchangeUser
                GetSMTPAddress = olkEnt.PrimarySmtpAddress
            Else
                GetSMTPAddress = Item.SenderEmailAddress
            End If
    End Select
    On Error GoTo 0
    Set olkPrp = Nothing
    Set olkSnd = Nothing
    Set olkEnt = Nothing
End Function

Private Function GetOutlookVersion() As Integer
    Dim arrVer As Variant
    arrVer = Split(Outlook.Version, ".")
    GetOutlookVersion = arrVer(0)
End Function

Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
    Dim olkPA As Outlook.PropertyAccessor
    On Error Resume Next
    Set olkPA = olkMsg.PropertyAccessor
    SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
    On Error GoTo 0
    Set olkPA = Nothing
End Function

2,832 comments on “Exporting Outlook Messages to Excel

  1. Hi David!

    I can’t even begin to tell you how helpful your code has been to me. I have learned so much from just working with what you have provided and trying to make adjustments here and there.

    I was hoping you could help me with something. I need a code that allows for a date range and appends to an existing workbook without over-writing it.

    Thank you for your time in advance and keep up the fantastic work!

    • Hi, John.

      Thanks. I’m glad the code is useful.

      You don’t mention what data you want to export from each message. Rev 34 is a good starting point. It exports messages from a date range and writes them to an existing spreadsheet.

  2. Hi David,

    I need to find the way to get the mails from folder/subfolder to exported [subject,Received time and sender] along with count of mails which have same subject.

    1.Select a folder. The macro will process that folder and all sub-folders under it.
    2.When we run the macro Enter the path and filename of the file the macro will export the data to. The macro will create this file.
    3.Enter a date range with time. The macro will only export messages that fall inside the date range [e.g: 8/10/2015 6:00 AM to 8/10/2015 4:00PM]

    Thanks
    Adi

    • Hi David,

      Please suggest for getting excel report that gives us number of count of mails[same subject] per day for a period[monthly] in mailbox[folder].

      1.Select a folder. The macro will process that folder and all sub-folders under it.
      2.When we run the macro Enter the path and file name of the file the macro will export the data to. The macro will create this file.
      3.Enter a date range with specific to time. The macro will only export messages that fall inside the date range [e.g: 8/10/2015 XX:00 PM to 8/10/2015 YY:00 PM]
      4. Final report should have subject,Received time ,sender,Number of count[with same subject]

      e.g:
      Usage Report 8/7/2015 22:31 admin@in.com
      Usage Report 8/7/2015 19:10 admin@in.com
      Usage Report 8/7/2015 18:00 admin@in.com

      Output will be:-
      Subject Received Sender total
      Usage Report 8/7/2015 22:31 admin@in.com 3

      Thanks in advance
      Adi

    • Hi David,

      Update to earlier request,Please suggest for getting excel report[analysis] that gives us number of count of mails[same subject] per day for a period[monthly] in mailbox[folder].

      1.Select a folder. The macro will process that folder and all sub-folders under it.
      2.When we run the macro Enter the path and file name of the file the macro will export the data to. The macro will create this file.
      3.Enter a date range with specific to time. The macro will only export messages that fall inside the date range [e.g: 8/10/2015 XX:00 PM to 8/10/2015 YY:00 PM]
      4. Final report should have subject,Received time ,sender,Number of count om each day[with same subject]

      Output will be:-
      Email Subject: last sent sender 1-Aug 2-Aug 3-Aug 4-Aug 5-Aug 6-Aug 7-Aug 8-Aug

      Usage Report 8/7/2015 22:31 admin@in.com 4 5 6 8 9 10 11 14
      Usage Report1 8/7/2015 12:31 admin@in.com 4 5 6 8 9 10 11 14
      Usage Report2 8/7/2015 06:31 admin@in.com 4 5 6 8 9 10 11 14

      Thanks in advance
      Adi

    • Hi, Adi.

      I want to be clear on what you want. Is this the only output you want or do you want both this and the output you described in your previous post?

  3. Dear David Lee,
    I found the VBA code very useful and I use it very often in my workload. I know you are very busy these, however, may I ask for your assistance in modifying the code Rev 40? I would like to incorporate date ranges (like in the Rev 34, export messages based on a date range for entire mailbox, including PST folders) and the 2nd issue, is there a way increase number of attachment to be saved and hyperlinked to the attachments of each exported message? Currently, if I run the code and if a message has to more than 22 attachments, the code triggers error (Run-time error ‘1004’: Application-defined or object-defined error) and stalls exporting messages at with error balloon lngCol = 27 hovering in the code “excWks.Hyperlinks.Add Anchor:=excWks.Range(NumberToLetter(lngCol) & lngRow), Address:=”FILE:\\” & strAFN, TextToDisplay:=olkAtt.FileName” and does not further export messages into the defined CONST EXCEL_WORKSHEET.. I was able to bypass the error triggering by adding a line in “On Error Resume Next”, it works, but the excessive attachments (if number of attachments are more than 22) are not saved to CONST EXPORT_FOLDER and not recorded in the CONST EXCEL_WORKSHEET. Is there another way to avoid the error triggering?

    • Hi, Dan.

      Let’s take care of the run-time error first. Replace the NumberToLetter function you have now with the version below. Once you’re done that please try the solution again.

      Function NumberToLetter(ByVal intVal As Integer) As String
          Dim intL1 As Integer, intL2 As Integer, intL3 As Integer
          intL1 = intVal
          intL2 = 0
          intL3 = 0
          Do While intL1 > 26
              intL2 = intL2 + 1
              If intL2 > 26 Then
                  intL3 = intL3 + 1
                  intL2 = 1
              End If
              intL1 = intL1 - 26
          Loop
          If intL3 > 0 Then
              NumberToLetter = Chr(intL3 + 64)
          End If
          If intL2 > 0 Then
              NumberToLetter = NumberToLetter & Chr(intL2 + 64)
          End If
          If intL1 > 0 Then
              NumberToLetter = NumberToLetter & Chr(intL1 + 64)
          End If
      End Function
      
    • Dear David Lee,
      The code worked like a charm. I ran it and it produced the report with all attachment (in one of the messages it produced over 23, I think that one I have the only message with that many attachments).
      How about the ability to incorporate date ranges?

    • Hi, Dan.

      I think this will do it. Please be sure to include the revised NumberToLetter function.

      '--> Declare some constants
      'On the next line, edit the path on the next line.  This is the folder the code will save the attachments to.  Make sure the path ends with a \
      Const EXPORT_FOLDER = "C:\Users\David\Documents\TestArea\chanakkiyan\files\"
      'On the next line, edit the path to the spreadsheet you want to export to.
      Const EXCEL_WORKSHEET = "C:\Users\David\Documents\TestArea\chanakkiyan\chanakkiyan.xlsx"
      Const MACRO_NAME = "Export Messages to Excel (Rev 40)"
        
      Dim excApp As Object, _
          excWkb As Object, _
          excWks As Object, _
          objFSO As Object, _
          intVer As Integer, _
          lngRow As Long, _
          datBeg As Date, _
          datEnd As Date
       
      Sub ExportController()
          Dim olkSto As Object, _
              olkFld As Object
       
          '--> Initialize some variables
          Set objFSO = CreateObject("Scripting.FileSystemObject")
          intVer = GetOutlookVersion()
          
          '--> Get a date range from the user.  Parse what they enter into starting and ending dates.
          strDateRange = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
          arrTemp = Split(strDateRange, "to")
          datBeg = IIF(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
          datEnd = IIF(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
           
          '--> Connect to Excel
          Set excApp = CreateObject("Excel.Application")
          Set excWkb = excApp.Workbooks.Open(EXCEL_WORKSHEET)
          Set excWks = excWkb.Worksheets(1)
          lngRow = excWks.UsedRange.Rows.Count + 1
           
          '--> Main routine
          For Each olkSto In Session.Stores
              Set olkFld = olkSto.GetRootFolder
              ExportMessagesToExcel olkFld
          Next
          excWks.Columns("A:ZZ").AutoFit
           
          '--> Save and close the workbook
          excWkb.Close True
           
          '--> Destroy all objects
          Set olkSto = Nothing
          Set olkFld = Nothing
          Set objFSO = Nothing
          Set excWks = Nothing
          Set excWkb = Nothing
          Set excApp = Nothing
           
          '--> Notify the user that the process is finished
          MsgBox "Export complete.", vbInformation + vbOKOnly, MACRO_NAME
      End Sub
       
      Private Sub ExportMessagesToExcel(olkFld As Outlook.MAPIFolder)
          '--> Declare some variables
          Dim olkMsg As Object, _
              olkSub As Object, _
              olkAtt As Object, _
              lngCol As Long, _
              strAFN As String, _
              arrTmp As Variant
                
          '--> Main routine
          'Only process folders containing emails, not contacts, calendars, tasks, or notes.
          If olkFld.DefaultItemType = olMailItem Then
              Set olkLst = olkFld.Items.Restrict("[ReceivedTime] >= '" & " " & Format(datStart, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
              For Each olkMsg In olkLst
                  If olkMsg.Class = olMail Then
                      lngCol = 5
                      'Add a row for each field in the message you want to export
                      excWks.Cells(lngRow, 1) = olkFld.FolderPath
                      excWks.Cells(lngRow, 2) = olkMsg.Subject
                      excWks.Cells(lngRow, 3) = olkMsg.ReceivedTime
                      excWks.Cells(lngRow, 4) = GetSMTPAddress(olkMsg, intVer)
                      For Each olkAtt In olkMsg.Attachments
                          If (Not IsHiddenAttachment(olkAtt)) And (Not olkAtt.Type = olOLE) Then
                              strAFN = EXPORT_FOLDER & objFSO.GetBaseName(olkAtt.fileName) & Format(olkMsg.ReceivedTime, "yyyy-mm-dd-hh-nn-ss") & "." & objFSO.GetExtensionName(olkAtt.fileName)
                              olkAtt.SaveAsFile strAFN
                              excWks.Hyperlinks.Add Anchor:=excWks.Range(NumberToLetter(lngCol) & lngRow), Address:="FILE:\\" & strAFN, TextToDisplay:=olkAtt.fileName
                              lngCol = lngCol + 1
                          End If
                      Next
                      lngRow = lngRow + 1
                  End If
              Next
              'Process all the subfolders under this folder
              For Each olkSub In olkFld.Folders
                  ExportMessagesToExcel olkSub
              Next
          End If
       
          '--> Destroy all objects
          Set olkMsg = Nothing
          Set olkSub = Nothing
          Set olkAtt = Nothing
      End Sub
        
      Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
          Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
          On Error Resume Next
          Select Case intOutlookVersion
              Case Is < 14
                  If Item.SenderEmailType = "EX" Then
                      GetSMTPAddress = SMTP2007(Item)
                  Else
                      GetSMTPAddress = Item.SenderEmailAddress
                  End If
              Case Else
                  Set olkSnd = Item.Sender
                  If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                      Set olkEnt = olkSnd.GetExchangeUser
                      GetSMTPAddress = olkEnt.PrimarySmtpAddress
                  Else
                      GetSMTPAddress = Item.SenderEmailAddress
                  End If
          End Select
          On Error GoTo 0
          Set olkPrp = Nothing
          Set olkSnd = Nothing
          Set olkEnt = Nothing
      End Function
        
      Private Function GetOutlookVersion() As Integer
          Dim arrVer As Variant
          arrVer = Split(Outlook.Version, ".")
          GetOutlookVersion = arrVer(0)
      End Function
        
      Private Function SMTP2007(olkMsg As Outlook.MailItem) As String
          Dim olkPA As Outlook.PropertyAccessor
          On Error Resume Next
          Set olkPA = olkMsg.PropertyAccessor
          SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
          On Error GoTo 0
          Set olkPA = Nothing
      End Function
        
      Private Function IsHiddenAttachment(olkAtt As Outlook.Attachment) As Boolean
          Const PR_ATTACH_CONTENT_ID = "http://schemas.microsoft.com/mapi/proptag/0x3712001E"
          Dim olkPA As Outlook.PropertyAccessor, varTemp As Variant
          On Error Resume Next
          Set olkPA = olkAtt.PropertyAccessor
          varTemp = olkPA.GetProperty(PR_ATTACH_CONTENT_ID)
          IsHiddenAttachment = (varTemp <> "")
          On Error GoTo 0
          Set olkPA = Nothing
      End Function
      
  4. Hi David,

    I have emails where the body looks like this:

    ———————————————————————————————————-
    Node name : Server1
    Incident Detected : 07/28/2015:12:09:31
    Incident Reported : 07/28/2015:12:09:31
    Application : MSSQL$SQL2008
    Message Group : MSS_Fault
    Message Object : Failover
    Severity : critical
    Message Text : EventID: 0x00004A4B (19019) – [sqsrvres] CheckServiceAlive: Service is dead
    ———————————————————————————————————-
    I would like to get Node name, Incident Detected, Message Group, Severity and Message Text with them all having there own column, in addition to the subject fielld and the received date.

    Can you also show me exactly what would need to be changed if I decided to add or remove information within the email?

    Thanks in advance,
    Joe B.

    • Hi, Joe.

      This should take care of the messages you described.

      Const MACRO_NAME = "Export Messages to Excel (Rev 14)"
       
      Sub ExportMessagesToExcel()
          Dim olkMsg As Object, _
              excApp As Object, _
              excWkb As Object, _
              excWks As Object, _
              intRow As Integer, _
              intCnt As Integer, _
              strFil As String, _
              strTmp As String, _
              arrLin As Variant, _
              varLin As Variant
          strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
          If strFil <> "" Then
              Set excApp = CreateObject("Excel.Application")
              Set excWkb = excApp.Workbooks.Add()
              Set excWks = excWkb.ActiveSheet
              'Write Excel Column Headers
              With excWks
                  .Cells(1, 1) = "Received"
                  .Cells(1, 2) = "Subject"
                  .Cells(1, 3) = "Node Name"
                  .Cells(1, 4) = "Incident Detected"
                  .Cells(1, 5) = "Message Group"
                  .Cells(1, 6) = "Severity"
                  .Cells(1, 7) = "Message Text"
              End With
              intRow = 2
              'Write messages to spreadsheet
              For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
                  'Only export messages, not receipts or appointment requests, etc.
                  If olkMsg.Class = olMail Then
                      'Add a row for each field in the message you want to export
                      excWks.Cells(intRow, 1) = olkMsg.ReceivedTime
                      excWks.Cells(intRow, 2) = olkMsg.Subject
                      arrLin = Split(olkMsg.Body, vbCrLf)
                      For Each varLin In arrLin
                          strTmp = Trim(varLin)
                          If Left(strTmp, 10) = "Node name:" Then
                              excWks.Cells(intRow, 3) = Trim(Mid(strTmp, 11))
                          ElseIf Left(strTmp, 18) = "Incident Detected:" Then
                              excWks.Cells(intRow, 4) = Trim(Mid(strTmp, 19))
                          ElseIf Left(strTmp, 14) = "Message Group:" Then
                              excWks.Cells(intRow, 5) = Trim(Mid(strTmp, 15))
                          ElseIf Left(strTmp, 9) = "Severity:" Then
                              excWks.Cells(intRow, 6) = Trim(Mid(strTmp, 10))
                          ElseIf Left(strTmp, 13) = "Message Text:" Then
                              excWks.Cells(intRow, 7) = Trim(Mid(strTmp, 14))
                          End If
                      Next
                      intRow = intRow + 1
                      intCnt = intCnt + 1
                  End If
              Next
              Set olkMsg = Nothing
              excWkb.SaveAs strFil
              excWkb.Close
          End If
          Set excWks = Nothing
          Set excWkb = Nothing
          Set excApp = Nothing
          MsgBox "Process complete.  A total of " & intCnt & " messages were exported.", vbInformation + vbOKOnly, "Export messages to Excel"
      End Sub
      

      To change what the macro pulls from a message, edit these lines.

      If Left(strTmp, 10) = "Node name:" Then
          excWks.Cells(intRow, 3) = Trim(Mid(strTmp, 11))
      ElseIf Left(strTmp, 18) = "Incident Detected:" Then
          excWks.Cells(intRow, 4) = Trim(Mid(strTmp, 19))
      ElseIf Left(strTmp, 14) = "Message Group:" Then
          excWks.Cells(intRow, 5) = Trim(Mid(strTmp, 15))
      ElseIf Left(strTmp, 9) = "Severity:" Then
          excWks.Cells(intRow, 6) = Trim(Mid(strTmp, 10))
      ElseIf Left(strTmp, 13) = "Message Text:" Then
          excWks.Cells(intRow, 7) = Trim(Mid(strTmp, 14))
      End If
      

      For each bit of data you want to pull, get the label (i.e. the text up to the first colon) and the length of that label. For example, for “node name” the label is “Node name:”. That string is 10 characters long. You’ll then construct an “IF” statement comparing the leftmost x characters (where x is the length of the label string) to the label. If the strings match, then you’ll use the MID function to pull the text beginning 1 character beyond the label through the end of the line and insert it into the correct cell in the spreadsheet. In other words

      'Grab the leftmost 10 characters of the line and compare it to the literal value "Node name:"
      If Left(strTmp, 10) = "Node name:" Then
          'Grab the text on the line beginning at character position 11 and insert it into column C of the current line in the spreadsheet
          excWks.Cells(intRow, 3) = Trim(Mid(strTmp, 11))
      

      Does that make sense?

  5. wondered if you could help me. from excel i want to search through the sent items in outlook to see if a specific email has been sent and if so display the time and date that the email was sent. is this possible from excel?

    • Hi, Gary.

      Yes, that’s possible. What search criteria do you want to use to locate the email?

    • Gary,

      The complete subject (i.e. an exact match) or a portion of the subject (e.g. the subject begins with or the subject contains)? Case sensitive or case immaterial?

    • the whole subject is held within a variable called MessSubject at present so thats what i’ll be using to search with. basicly im sending an email via excel, im trying to open outlook if its closed and then once the email has sent display the date & time that it is actually sent. presently if outlook isnt open it sits in the outbox and because it hasnt registered as being sent i have another piece of code that check and if it didnt send it sends again. my check isnt working at all so im sending everything more than once.

      so basicly i need to be able to send an email via excel, open outlook in the background if it is closed check that the email did send then display date and time and if outlook wasnt already open previously then close outlook.

      sorry posted this on the wrong comment last time

    • the whole subject is held within a variable called MessSubject at present so thats what i’ll be using to search with. basicly im sending an email via excel, im trying to open outlook if its closed and then once the email has sent display the date & time that it is actually sent. presently if outlook isnt open it sits in the outbox and because it hasnt registered as being sent i have another piece of code that check and if it didnt send it sends again. my check isnt working at all so im sending everything more than once.

      so basicly i need to be able to send an email via excel, open outlook in the background if it is closed check that the email did send then display date and time and if outlook wasnt already open previously then close outlook.

      sorry posted this on the wrong comment last time

    • Hi, Gary.

      This should do it. I wrote this as a black-box (i.e. completely self-contained) function. From a programming perspective that makes it easy to integrate with existing solutions. You can implement the function by adding a command like the following in an Excel macro.

      Application.ActiveSheet.Cells(1,2) = FindItemInOutlook(Application.ActiveSheet.Cells(1,1)
      

      However, if you need to process a large number of items at once, then from a performance perspective the black-box approach is going to be slower than writing it as in-line code. The reason for this is that the black-box function opens and closes Outlook each time it’s called. In-line code would open Outlook once, process all the items, then close it. The difference in performance is likely to be negligible so long as you aren’t processing lots of items at once. My recommendation is to test it for yourself and decide if it seems too slow. If it does, then I can redo it as in-line code.

      Here’s the code. I’m assuming that you know how to add the code to Excel and integrate it into your solution. If not, then I can provide instructions and help you with integratation. Just let me know.

      Function FindItemInOutlook(strSubject As String) As String
          Dim olkApp As Object, _
              olkSes As Object, _
              olkFol As Object, _
              olkItm As Object, _
              bolOpn As Boolean
          strSubject = Replace(strSubject, "'", "''")
          strSubject = Replace(strSubject, Chr(34), Chr(34) & String(2, Chr(34)))
          Set olkApp = GetObject(, "Outlook.Application")
          If TypeName(olkApp) = "Nothing" Then
              Set olkApp = CreateObject("Outlook.Application")
              Set olkSes = olkApp.GetNamespace("MAPI")
              olkSes.Logon olkApp.DefaultProfileName
              bolOpn = True
          Else
              Set olkSes = olkApp.Session
          End If
          Set olkFol = olkSes.GetDefaultFolder(olFolderSentMail)
          Set olkItm = olkFol.Items.Find("[Subject] = '" & strSubject & "'")
          If TypeName(olkItm) <> "Nothing" Then
              FindItemInOutlook = olkItm.SentOn
          End If
          If bolOpn Then
              olkSes.Logoff
          End If
          Set olkItm = Nothing
          Set olkFol = Nothing
          Set olkSes = Nothing
          Set olkApp = Nothing
      End Function
      
    • Thanks for the reply, am i right in thinking that this doesnt open outlook first? if not how can i open outlook also how can i close it afterwards if outlook wasnt already open?

    • i get an error when running this at this piece of code
      Set olkApp = GetObject(, “Outlook.Application”)

      error is “run -time error ‘429’:
      ActiveX component cant create object

      outlook trys to open but a window pops up saying

      Enterprise vault outlook addin could not establish a connection to your exchange mailbox.

      when outlook is open the code works fine

    • Gary,

      Yes, the code should connect to Outlook if it’s running or launch it if it’s not. I’m not familiar with “Enterprise Vault”, so I can’t speak to that. Add the command

      On Error Resume Next
      

      just below the Dim statement. Let’s see if that gets you past the other error.

  6. Hi there,

    Its me again, i have a small issue which I was hoping you could shed some light on for me. As you prob remember, I am using a version which exports to excel, then subtotals by sender. I export the catagories, the received and reply times, etc; I also have the option to process sub folders. Its working on some of my folders but on a certain mailbox and all sets of folders in that mailbox i get the message Run-Time error
    ‘-314310393 (ed440107)’ The operation failed. I then hit debug and it highlights
    ‘Write messages to spreadsheet
    Set olkLst = olkFld.Items.Restrict(“[ReceivedTime] >= ‘” & Format(datBeg, “dd/mm/yy h:nn AMPM”) & “‘” & ” AND [ReceivedTime] <= '" & Format(datEnd, "dd/mm/yy h:nn AMPM") & "'")
    For Each olkMsg In olkLst

    Are you able to assist? I would be eternally grateful :)

    Please see below the code I am using

    Const MACRO_NAME = &quot;Export Message to Excel - Ed's Version&quot;
    Const xlAscending = 1
    Const xlYes = 1
    Const xlCount = -4112
     
    Dim excApp As Object, _
        excWkb As Object, _
        excWks As Object, _
        lngRow As Long, _
        lngCnt As Long, _
        intVer As Integer, _
        datBeg As Date, _
        datEnd As Date
     
    Sub ExportMessagesToExcel()
        Dim strFil As String, bolSub As Boolean, strRng As String, arrTmp As Variant
        strFil = InputBox(&quot;Enter a filename (including path) to save the exported messages to.&quot;, MACRO_NAME)
        If strFil  "" Then
            strRng = InputBox("Enter the date range of the messages to export in the form ""dd/mm/yyyy to dd/mm/yyyy""", MACRO_NAME, Date &amp; " to " &amp; Date)
            arrTmp = Split(strRng, "to")
            datBeg = IIf(IsDate(arrTmp(0)), arrTmp(0), Date) &amp; " 12:00am"
            datEnd = IIf(IsDate(arrTmp(1)), arrTmp(1), Date) &amp; " 11:59pm"
            bolSub = IIf(MsgBox("Do you want to process subfolders?", vbQuestion + vbYesNo, MACRO_NAME) = vbYes, True, False)
            intVer = GetOutlookVersion()
            Set excApp = CreateObject("Excel.Application")
            Set excWkb = excApp.Workbooks.Add()
            Set excWks = excWkb.ActiveSheet
            'Write Excel Column Headers
            With excWks
                .Cells(1, 1) = "Sender Domain"
                .Cells(1, 2) = "Sender Address"
                .Cells(1, 3) = "Sender Name"
                .Cells(1, 4) = "Subject"
                .Cells(1, 5) = "date"
                .Cells(1, 6) = "time"
                .Cells(1, 7) = "Reply Type"
                .Cells(1, 8) = "Reply Date"
                .Cells(1, 9) = "folder"
                .Cells(1, 10) = "Catagory"
            End With
            ProcessFolder Application.ActiveExplorer.CurrentFolder, bolSub
            excApp.DisplayAlerts = False
            excWks.Range("A1:J" &amp; lngRow).Sort Key1:=excWks.Range("A1"), Order1:=xlAscending, Header:=xlYes
            excWks.Range("A1:J" &amp; lngRow).Subtotal GroupBy:=1, Function:=xlCount, TotalList:=Array(1, 1)
            excWks.Columns("A:J").AutoFit
            excWks.Outline.ShowLevels RowLevels:=2, ColumnLevels:=1
            excWkb.SaveAs strFil
            excWkb.Close
        End If
        Set excWks = Nothing
        Set excWkb = Nothing
        excApp.Quit
        Set excApp = Nothing
        MsgBox "Process complete.  A total of " &amp; lngCnt &amp; " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
    End Sub
      
    Sub ProcessFolder(olkFld As Outlook.MAPIFolder, bolSub As Boolean)
        Dim olkMsg As Object, olkSub As Outlook.MAPIFolder, olkLst As Outlook.Items
        lngRow = excWks.UsedRange.Rows.Count + 1
        'Write messages to spreadsheet
        Set olkLst = olkFld.Items.Restrict("[ReceivedTime] &gt;= '" &amp; Format(datBeg, "dd/mm/yy h:nn AMPM") &amp; "'" &amp; " AND [ReceivedTime]  0 Then
                    excWks.Cells(lngRow, 1) = Mid(excWks.Cells(lngRow, 2).Value, InStr(1, excWks.Cells(lngRow, 2).Value, "@") + 1)
                End If
                excWks.Cells(lngRow, 3) = olkMsg.SenderName
                excWks.Cells(lngRow, 4) = olkMsg.Subject
                excWks.Cells(lngRow, 7) = GetLastVerb(olkMsg)
                excWks.Cells(lngRow, 5) = Format(olkMsg.ReceivedTime, "dd/mm/yy")
                excWks.Cells(lngRow, 6) = Format(olkMsg.ReceivedTime, "hh:mm")
                If InStr(1, LCase(excWks.Cells(lngRow, 7).Value), "reply") &gt; 0 Then
                    excWks.Cells(lngRow, 8) = GetLastVerbTime(olkMsg)
                    excWks.Cells(lngRow, 9) = olkMsg.Parent.FolderPath
                End If
                excWks.Cells(lngRow, 10) = olkMsg.Categories
                lngRow = lngRow + 1
                lngCnt = lngCnt + 1
            End If
        Next
        Set olkMsg = Nothing
        If bolSub Then
            For Each olkSub In olkFld.Folders
                ProcessFolder olkSub, bolSub
            Next
        End If
        Set olkSub = Nothing
    End Sub
    
    Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
        Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
        On Error Resume Next
        Select Case intOutlookVersion
            Case Is &lt; 14
                If Item.SenderEmailType = &quot;EX&quot; Then
                    GetSMTPAddress = SMTP2007(Item)
                Else
                    GetSMTPAddress = Item.SenderEmailAddress
                End If
            Case Else
                Set olkSnd = Item.Sender
                If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                    Set olkEnt = olkSnd.GetExchangeUser
                    GetSMTPAddress = olkEnt.PrimarySmtpAddress
                Else
                    GetSMTPAddress = Item.SenderEmailAddress
                End If
        End Select
        On Error GoTo 0
        Set olkPrp = Nothing
        Set olkSnd = Nothing
        Set olkEnt = Nothing
    End Function
     
    Function GetOutlookVersion() As Integer
        Dim arrVer As Variant
        arrVer = Split(Outlook.Version, &quot;.&quot;)
        GetOutlookVersion = arrVer(0)
    End Function
     
    Function SMTP2007(olkMsg As Outlook.MailItem) As String
        Dim olkPA As Outlook.PropertyAccessor
        On Error Resume Next
        Set olkPA = olkMsg.PropertyAccessor
        SMTP2007 = olkPA.GetProperty(&quot;http://schemas.microsoft.com/mapi/proptag/0x5D01001E&quot;)
        On Error GoTo 0
        Set olkPA = Nothing
    End Function
    
    Function GetLastVerb(olkMsg As Outlook.MailItem) As String
        Dim intVerb As Integer
        intVerb = GetProperty(olkMsg, &quot;http://schemas.microsoft.com/mapi/proptag/0x10810003&quot;)
        Select Case intVerb
            Case 102
                GetLastVerb = &quot;Reply to Sender&quot;
            Case 103
                GetLastVerb = &quot;Reply to All&quot;
            Case 104
                GetLastVerb = &quot;Forward&quot;
            Case 108
                GetLastVerb = &quot;Reply to Forward&quot;
            Case Else
                GetLastVerb = &quot;&quot;
        End Select
    End Function
    
    Function GetLastVerbTime(olkItm As Object) As Date
        GetLastVerbTime = GetDateProperty(olkItm, &quot;http://schemas.microsoft.com/mapi/proptag/0x10820040&quot;)
    End Function
    
    Public Function GetProperty(olkItm As Object, strPropName As String) As Variant
        Dim olkPA As Outlook.PropertyAccessor
        Set olkPA = olkItm.PropertyAccessor
        GetProperty = olkPA.GetProperty(strPropName)
        Set olkPA = Nothing
    End Function
    
    Public Function GetDateProperty(olkItm As Object, strPropName As String) As Date
        Dim olkPA As Outlook.PropertyAccessor
        Set olkPA = olkItm.PropertyAccessor
        GetDateProperty = olkPA.UTCToLocalTime(olkPA.GetProperty(strPropName))
        Set olkPA = Nothing
    End Function
    
    • Hi, Ed.

      If the code works for folders in your mailbox but not in another mailbox, then there must be something different about that mailbox. What’s different about it? Are you sure you have the necessary permissions to access it?

    • the whole subject is held within a variable called MessSubject at present so thats what i’ll be using to search with. basicly im sending an email via excel, im trying to open outlook if its closed and then once the email has sent display the date & time that it is actually sent. presently if outlook isnt open it sits in the outbox and because it hasnt registered as being sent i have another piece of code that check and if it didnt send it sends again. my check isnt working at all so im sending everything more than once.

      so basicly i need to be able to send an email via excel, open outlook in the background if it is closed check that the email did send then display date and time and if outlook wasnt already open previously then close outlook.

  7. Hello David,

    I am looking for a solution on this problem (already tried different revisions above, but couldn’t make it work):

    I need to export email address of sender and name of sender (From) from a folder i choose in outlook. When I run this macro, it should ask me for a certain excel file destination , where the data should be placed (this way, everytime I run this macro in outlook, the excel file will be updated with data exported from the chosen outlook folder).

    Sorry for not really clear post, I hope you can figure it out and give me some advice. Thank you in advance!

    Marco

    • Hi, Marco.

      This should do it. Please follow the instructions from the original post to add the code to Outlook.

      Const MACRO_NAME = "Export Messages to Excel (Rev Marco)"
       
      Sub ExportMessagesToExcel()
          Dim olkMsg As Object, _
              excApp As Object, _
              excWkb As Object, _
              excWks As Object, _
              lngRow As Long, _
              lngExp As Long, _
              intVer As Integer, _
              strFil As String
          strFil = InputBox("Enter the path to the Excel workbook you want to export to.", MACRO_NAME)
          If strFil = "" Then
              MsgBox "You did not enter a file path.  Export cancelled.", vbExclamation + vbOKOnly, MACRO_NAME
          Else
              intVer = GetOutlookVersion()
              Set excApp = CreateObject("Excel.Application")
              Set excWkb = excApp.Workbooks.Open(strFil)
              Set excWks = excWkb.Worksheets(1)
              lngRow = excWks.UsedRange.Rows.count + 1
              'Write messages to spreadsheet
              For Each olkMsg In Application.ActiveExplorer.CurrentFolder.Items
                  'Only export messages, not receipts or appointment requests, etc.
                  If olkMsg.Class = olMail Then
                      'Add a row for each field in the message you want to export
                      excWks.Cells(lngRow, 1) = GetSMTPAddress(olkMsg, intVer)
                      excWks.Cells(lngRow, 2) = olkMsg.SenderName
                      lngRow = lngRow + 1
                      lngExp = lngExp + 1
                  End If
              Next
              excWkb.Close True
              Set excWks = Nothing
              Set excWkb = Nothing
              Set excApp = Nothing
              MsgBox "Process complete.  A total of " & lngExp & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
          End If
          Set olkMsg = Nothing
      End Sub
       
      Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
          Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
          On Error Resume Next
          Select Case intOutlookVersion
              Case Is < 14
                  If Item.SenderEmailType = "EX" Then
                      GetSMTPAddress = SMTP2007(Item)
                  Else
                      GetSMTPAddress = Item.SenderEmailAddress
                  End If
              Case Else
                  Set olkSnd = Item.Sender
                  If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                      Set olkEnt = olkSnd.GetExchangeUser
                      GetSMTPAddress = olkEnt.PrimarySmtpAddress
                  Else
                      GetSMTPAddress = Item.SenderEmailAddress
                  End If
          End Select
          On Error GoTo 0
          Set olkPrp = Nothing
          Set olkSnd = Nothing
          Set olkEnt = Nothing
      End Function
       
      Function GetOutlookVersion() As Integer
          Dim arrVer As Variant
          arrVer = Split(Outlook.Version, ".")
          GetOutlookVersion = arrVer(0)
      End Function
       
      Function SMTP2007(olkMsg As Outlook.MailItem) As String
          Dim olkPA As Outlook.PropertyAccessor
          On Error Resume Next
          Set olkPA = olkMsg.PropertyAccessor
          SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
          On Error GoTo 0
          Set olkPA = Nothing
      End Function
      
    • Hello David and thank you!

      I wasnt at work for a week, so I tested the macro just now.
      When I enter the path, it shows an error, stating its unable to find the Excel file.

      Is it possible to change the macro to the way I would not need to enter the whole path, but just some sort of “Browse for folder” function will be used to do that and to choose the file.

      Thank you very much!

      Marco

    • Hi, Marco.

      Yes, that’s possible. Add this code to that you already have.

      Function SelectFile(strCap As String, strBut As String, strFol As String, strFlt As String) As String
          Const msoFileDialogFilePicker = 3
          Dim objDlg As Object, _
              varFil As Variant, _
              excApp As Object, _
              arrFlt As Variant, _
              varFlt As Variant, _
              arrTmp As Variant, _
              intPos As Integer
          intPos = 1
          Set excApp = CreateObject("Excel.Application")
          arrFlt = Split(strFlt, "|")
          Set objDlg = excApp.FileDialog(msoFileDialogFilePicker)
          With objDlg
              .AllowMultiSelect = False
              .ButtonName = strBut
              For Each varFlt In arrFlt
                  arrTmp = Split(varFlt, ",")
                  .Filters.Add arrTmp(0), arrTmp(1), intPos
                  intPos = intPos + 1
              Next
              .InitialFileName = strFol
              .InitialView = msoFileDialogViewDetails
              .Title = strCap
              .Show
              
              For Each varFil In .SelectedItems
                  SelectFile = varFil
              Next varFil
          End With
          
          Set excApp = Nothing
          Set objDlg = Nothing
      End Function
      

      Now, change this line (line 12)

      strFil = InputBox("Enter the path to the Excel workbook you want to export to.", MACRO_NAME)
      

      to

      strFil = SelectFile("Select a Spreadsheet", "Select", "C:\", "Spreadsheets,*.xls*")
      
  8. Dear David,
    thank you so much for the code! I’m using it several times a week but now it has stopped working.

    Outlook says “Type missmatch. Run-Time error ’13’.”
    The error is in row
    excWks.Columns(“A:” & NumberToLetter(lngCol)).AutoFit

    Sometimes it works and sometimes not, I can’t see when and why it’s working and don’t.

    Can you please help me once again?

    • Hi, Anders.

      When the code throws this error, what is the value of lngCol? You can determine that by mousing over the variable name in that line.

    • Good morning, Anders.

      Please replace the NumberToLetter function you have now with the version below. There was a flaw in the original version that caused it to fail after a certain point. This version corrects that.

      Function NumberToLetter(ByVal intCol As Integer) As String
         Dim intAlpha As Integer
         Dim intRemainder As Integer
         intAlpha = Int(intCol / 27)
         intRemainder = intCol - (intAlpha * 26)
         If intAlpha > 0 Then
            NumberToLetter = Chr(intAlpha + 64)
         End If
         If intRemainder > 0 Then
            NumberToLetter = NumberToLetter & Chr(intRemainder + 64)
         End If
      End Function
      
    • Thanks David for your fast reply!
      By this update, the e-mails that don’t contains any more e-mail-adresses than the sender’s don’t exports to the Excel-file. Can you please help me to get all the e-mails exported even if they don’t contains any more e-mailadresses to be exported, the first column (A) should contain all e-mailadresses from my senders.

    • Hi, Anders.

      I’m not sure I understand. Are you saying that when you swapped the NumberToLetter function for the new version the code stopped exporting all the messages?

  9. Hi,

    Is there a way to check if a email has been sent and has a reply ? I have a list in an excel with over 600 client names and i must send for each client an email to several colleagues (i am doing this in automatic mode) but… i must check that i have sent an email with the client name in the subject and check if i have a reply for each email i sent. The emails that i send and the replies are going in a shared office inbox so there is one place to look in but is eye blowing and i miss some emails :D .

    Any help is welcomed.

    Thank you.

  10. Hi David,

    Great work! Thank you so much for all the codes. They are great. I was wondering if I wanted to export to Access instead of Excel how would I accomplish it?

    Thank you!

    Sobair

    • Hi, Sobair.

      Find my my reply to Mr Naeem dated December 20, 2013 @ 5:38 am. That code has what you’re looking for.

    • Thank you so much, David!

      Unfortunately, I ran into issues running it. This what I changed the code to:
      Const DBNAME = “C:\Users\sosmanzai\Documents\sobair.accdb” which give me an error not finding it and when I debug the following code line is highlighted:
      adoCon.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & DBNAME & “;Persist Security Info=False;”

      Apologies for my ignorance. I am just not an experienced VB coder.

      Thank you kindly for your help!

      Sobair

    • Hi, Sobair.

      Did the database already exist? Unlike the Excel version, the Access version doesn’t create the database. It expects the database to already exist. I suppose I could create a version that creates the database, but that version does not do that.

    • Hi David,

      May I please ask for another favor? For Revision 4, is it possible to have a column in excel that would write the name of the folder the email came from?

      I greatly appreciate your help. Thank you!

      Sobair

    • Hi, Sobair.

      Sure. Add this line somewhere between lines 38 – 42. Change the “X” to the column number you want the information to appear in.

      excWks.Cells(intRow, X) = olkMsg.Parent.FolderPath
      
    • Hi David,

      Thank you kindly and muchly for your help! :)

      The Access database does not exist.

      David, I know you are a busy person and I do really appreciate your time but would you be so kind and guide me how to modify version 4 to include emails from sub-folders of the folder selected, to append to an existing sheet, and to link to original email to be able to open it if needed.

      Thank you so much for your time and help in advance!

      Sobair

    • Hi, Sobair.

      This should do it. If the hyperlinks from the items in the spreadsheet to the original items in Outlook don’t work, then it’s because you don’t have the Outlook protocol handler installed. Outlook 2007 and later doesn’t automatically install the Outlook protocol handler (which allows linking to Outlook items and folders) like earlier versions of Outlook did. You have to edit the registry and add an entry to enable it. Here’s a link to a page with details and a .reg file you can download to speed up the process. Pay attention to the instructions on the page about editing the .reg file. Also, if you have Outlook 2010 you’ll have to make another edit, changing the references to Office12 to Office14.

      Const MACRO_NAME = "Export Messages to Excel (Rev Sobair)"
       
      Dim excApp As Object, _
          excWkb As Object, _
          excWks As Object, _
          intVer As Integer, _
          lngMsg As Long, _
          lngRow As Long, _
          datBeg As Date, _
          datEnd As Date
       
      Sub ExportMessagesToExcel()
          Dim strFil As String, strDat As String, arrTemp As Variant, objFSO As Object
          strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
          If strFil <> "" Then
              strDat = InputBox("Enter the date range of the messages to export in the form ""mm/dd/yyyy to mm/dd/yyyy""", MACRO_NAME, Date & " to " & Date)
              arrTemp = Split(strDat, "to")
              datBeg = IIf(IsDate(arrTemp(0)), arrTemp(0), Date) & " 12:00am"
              datEnd = IIf(IsDate(arrTemp(1)), arrTemp(1), Date) & " 11:59pm"
              lngMsg = 0
              intVer = GetOutlookVersion()
              Set objFSO = CreateObject("Scripting.FileSystemObject")
              Set excApp = CreateObject("Excel.Application")
              If objFSO.FileExists(strFil) Then
                  Set excWkb = excApp.Workbooks.Open(strFil)
                  Set excWks = excWkb.Worksheets(1)
              Else
                  Set excWkb = excApp.Workbooks.Add()
                  Set excWks = excWkb.Worksheets(1)
                  With excWks
                      .Cells(1, 1) = "Subject"
                      .Cells(1, 2) = "Received"
                      .Cells(1, 3) = "Sender"
                  End With
                  excWkb.SaveAs strFil
              End If
              lngRow = excWks.UsedRange.Rows.count + 1
              ProcessFolder Application.ActiveExplorer.CurrentFolder
          End If
          excWks.Columns("A:C").AutoFit
          excWkb.Close True
          Set excWks = Nothing
          Set excWkb = Nothing
          excApp.Quit
          Set excApp = Nothing
          MsgBox "Process complete.  A total of " & lngMsg & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
      End Sub
       
      Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
          Dim olkLst As Object, _
              olkMsg As Object, _
              olkSub As Object
          If olkFld.DefaultItemType = olMailItem Then
              Set olkLst = olkFld.Items.Restrict("[ReceivedTime] >= '" & Format(datBeg, "ddddd h:nn AMPM") & "'" & " AND [ReceivedTime] <= '" & Format(datEnd, "ddddd h:nn AMPM") & "'")
              'Write messages to spreadsheet
              For Each olkMsg In olkLst
                  'Only export messages, not receipts or appointment requests, etc.
                  If olkMsg.Class = olMail Then
                      'Add a row for each field in the message you want to export
                      excWks.Hyperlinks.Add Anchor:=excWks.Range("A" & lngRow), Address:="outlook:" & olkMsg.EntryID, TextToDisplay:=IIf(olkMsg.Subject = "", "Blank", olkMsg.Subject)
                      'excWks.Cells(lngRow, 1) = olkMsg.Subject
                      excWks.Cells(lngRow, 2) = olkMsg.ReceivedTime
                      excWks.Cells(lngRow, 3) = GetSMTPAddress(olkMsg, intVer)
                      lngRow = lngRow + 1
                      lngMsg = lngMsg + 1
                  End If
              Next
              Set olkMsg = Nothing
          End If
          For Each olkSub In olkFld.Folders
              ProcessFolder olkSub
          Next
          Set olkSub = Nothing
      End Sub
       
      Private Function GetSMTPAddress(Item As Outlook.MailItem, intOutlookVersion As Integer) As String
          Dim olkSnd As Outlook.AddressEntry, olkEnt As Object
          On Error Resume Next
          Select Case intOutlookVersion
              Case Is < 14
                  If Item.SenderEmailType = "EX" Then
                      GetSMTPAddress = SMTP2007(Item)
                  Else
                      GetSMTPAddress = Item.SenderEmailAddress
                  End If
              Case Else
                  Set olkSnd = Item.Sender
                  If olkSnd.AddressEntryUserType = olExchangeUserAddressEntry Then
                      Set olkEnt = olkSnd.GetExchangeUser
                      GetSMTPAddress = olkEnt.PrimarySmtpAddress
                  Else
                      GetSMTPAddress = Item.SenderEmailAddress
                  End If
          End Select
          On Error GoTo 0
          Set olkPrp = Nothing
          Set olkSnd = Nothing
          Set olkEnt = Nothing
      End Function
       
      Function GetOutlookVersion() As Integer
          Dim arrVer As Variant
          arrVer = Split(Outlook.Version, ".")
          GetOutlookVersion = arrVer(0)
      End Function
       
      Function SMTP2007(olkMsg As Outlook.MailItem) As String
          Dim olkPA As Outlook.PropertyAccessor
          On Error Resume Next
          Set olkPA = olkMsg.PropertyAccessor
          SMTP2007 = olkPA.GetProperty("http://schemas.microsoft.com/mapi/proptag/0x5D01001E")
          On Error GoTo 0
          Set olkPA = Nothing
      End Function
      
    • Hi David,

      Thank you very much for the revised code. I really appreciate it.
      After Copying and pasting the code and running it, I got the following: Compile error: User-defined type not defined. When I clicked on OK, on line 13 the following got highlighted: , objFSO As FileSystemObject

      Thanks,
      Sobair

    • Hi David,

      Great, many thanks! Unfortunately, the code is not appending to an existing sheet. It is asking if the existing file to be replaced. If click YES, it will replaces the file. Would be so kind and take a look.
      I would like to run this code everyday and add the new emails to the existing sheet.

      Thanks,
      Sobair

    • Hi, Sobair.

      I’ve run the code over and over and it never exhibits that behavior for me. Do you perhaps have the workbook open? If it were open, then Excel wouldn’t be able to write to it. If you don’t have the workbook open, then please check Task Manager to see if there are open instances of Excel. If there are, then kill them all and try running the code again.

    • Hi David,

      Thank you so much for your guidance. I tried what you have suggested, unfortunately, it did not work which is okay as I do not need to append after all. But it is strange as to why it is working for you and not for me.

      You have been a super great help and I cannot thank you enough for helping me to achieve my goal. Your blog is fantastic and please continue being awesome!

      Thank you! Thank you!

      Sobair

  11. I’ve just used your code to obtain email addresses from outlook email bodies, very impressive, thank you.
    Chris

  12. Hi David. Amazing Work that you have done. I have another scenario that I think has not been covered. I get back a receipt from a company when a credit card transaction goes through. I want to export to Excel the Billing Information. The email looks sort of like this:

    Billing Information Shipping Information
    John Doe Jane Doe
    123 Main STreet 334 South Street
    Huffman, TX 77336 Chicago, IL 60659

    281-361-4637
    abc@abc.com

    I would like to export just the billing information to an excel spreadsheet and also separate the City, State and Zip.

    Thanks so much,

    Yoni

  13. Can you please modify the code to achieve the foll:
    1. The email body that is copies from Outlook to Excel should maintain new line breaks e.g. in excel when typing inside a cell we have to press Alt+Enter to goto new line.When email body is copied from Outlook to Excel the line breaks are removed. Copying email body as HTML does not help as HTML code is copied to excel

    2. Only selected email messages are exported to Excel (not all emails in a folder)

    3.Email sent date is in dd/mm/yyyy format and does not include time in am/pm

    4. EMail ACCOUNT NAME name is copied to excel (in situations where there is one profile in outlook and that profile contains several email accounts each with a seperate pst file)

    Thanks

    • Hi, Goutam Dharewa.

      I think this will do it. Please try this code and let me know if it does what you wanted.

      Sub ExportMessagesToExcel()
          Const MACRO_NAME = "Export Messages to Excel (ver Goutam Dharewa)"
          Dim olkMsg As Object, _
              excApp As Object, _
              excWkb As Object, _
              excWks As Object, _
              lngRow As Long, _
              lngCnt As Long, _
              strFil As String
          strFil = InputBox("Enter a filename (including path) to save the exported messages to.", MACRO_NAME)
          If strFil <> "" Then
              Set excApp = CreateObject("Excel.Application")
              Set excWkb = excApp.Workbooks.Add()
              Set excWks = excWkb.ActiveSheet
              'Write Excel Column Headers
              With excWks
                  .Cells(1, 1) = "Date Sent"
                  .Cells(1, 2) = "Received By"
                  .Cells(1, 3) = "Subject"
                  .Cells(1, 4) = "Body"
              End With
              lngRow = 2
              'Write messages to spreadsheet
              For Each olkMsg In Application.ActiveExplorer.Selection
                  'Only export messages, not receipts or appointment requests, etc.
                  If olkMsg.Class = olMail Then
                      'Add a row for each field in the message you want to export
                      excWks.Cells(lngRow, 1) = Format(olkMsg.SentOn, "dd/mm/yyyy")
                      excWks.Cells(lngRow, 2) = AccountReceivedThrough(olkMsg)
                      excWks.Cells(lngRow, 3) = olkMsg.Subject
                      excWks.Cells(lngRow, 4) = olkMsg.Body
                      lngRow = lngRow + 1
                      lngCnt = lngCnt + 1
                  End If
              Next
              Set olkMsg = Nothing
              excWkb.SaveAs strFil
              excWkb.Close
          End If
          Set excWks = Nothing
          Set excWkb = Nothing
          Set excApp = Nothing
          MsgBox "Process complete.  A total of " & lngCnt & " messages were exported.", vbInformation + vbOKOnly, MACRO_NAME
      End Sub
      
      Function AccountReceivedThrough(olkMsg As Outlook.MailItem) As String
          Const PR_ACCOUNT_RECEIVED_THROUGH = "http://schemas.microsoft.com/mapi/proptag/0x0076001E"
          Dim olkPA As Outlook.PropertyAccessor
          Set olkPA = olkMsg.PropertyAccessor
          AccountReceivedThrough = olkPA.GetProperty(PR_ACCOUNT_RECEIVED_THROUGH)
          Set olkPA = Nothing
      End Function
      
  14. Dear David Lee,
    regaring Revision 3, please, is it possible to display also name of folder and subfolder name in the exported file?
    Thanks a lot for Your help.

  15. Would it be possible to give an example oh how to get each of the scraped fields in the same row but sequential columns and to continue in that way for each email in a folder and it subfolders? I’d like to be able to sort by subject or date or sender for keeping historical emails rather than unarchive and search my pst files..

    • Hi, K. Mclean.

      I’m not sure I understand. All of the code examples here have display one message per row with each field in its own column. Are you asking for a version that has one record per column with each row containing a single field? Something like

                Message1          Message2         Message3
      Subject
      Sender 
      Received
      

      If that’s not what you want, then please help me understand. An example would be helpful.

  16. Hi David-
    Thank you for this! I have two questions-

    1) Will this code work on custom search folders? I’m trying to extract metadata from every email regardless of folder and the only way I could think was to build a search folder in Outlook returning all received emails and run the macro with it selected.

    2) The macro runs & creates the extract file- but after running I keep getting “Excel is waiting for another application to complete an OLE action” notifications & it prevents from running again.

    Thank you again!

    Kyle

    • Hi, Kyle.

      Which version of the script are you using? If it’s one of the revisions, which one?

    • Update- I ran revision 3 and it worked. I kept getting OLE messages afterwards & ended up going into task manager where it appeared I had two Excel processes running- I ended one and the messages stopped. Any ideas?

      Thanks
      Kyle

    • Hi, Kyle.

      Yes, the code works with search folders. That said, you should make one change to the code in rev 3 if you plan to use it with a search folder. Search folders don’t have sub-folders, so we need to remove the code in the ProcessFolder subroutine that processes them. Replace the ProcessFolder subroutine from rev 3 with the version below and you should be in business.

      I don’t know why you’d get an OLE error. I’d need to know the exact error to dig into that.

      Sub ProcessFolder(olkFld As Outlook.MAPIFolder)
          Dim olkMsg As Object, intRow As Integer
          intRow = excWks.UsedRange.Rows.count
          intRow = intRow + 1
          'Write messages to spreadsheet
          For Each olkMsg In olkFld.Items
              'Only export messages, not receipts or appointment requests, etc.
              If olkMsg.Class = olMail Then
                  'Add a row for each field in the message you want to export
                  excWks.Cells(intRow, 1) = olkMsg.Subject
                  excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
                  excWks.Cells(intRow, 3) = GetSMTPAddress(olkMsg, intVersion)
                  intRow = intRow + 1
                  intMessages = intMessages + 1
              End If
          Next
          Set olkMsg = Nothing
      End Sub
      
  17. Hi is it possible to capture and export email from a specific person using macro? I’ve been searching the net but I can’t get a possible solution.

    • Hi, Kathrine.

      Sure. What data do you want to export for each message? How do you want the process to work? For example, do you want to export all the messages in a given folder from that person?

  18. Hi David,
    I am looking out for exporting only few fields from outlook message which is very much similar to Revision 29 & 42. I want to export email id of sender & recipient who were CC’d, Email subject, email sent date/time. I used revision 29 & 42 however it does not give me email address for receipient marked in CC.

    Seeking your kind help here.

    Regards,
    Dave

    • Hi, Dave.

      Add the function below to the code for either rev 29 or 42.

      Function GetAddr(olkMsg As Outlook.MailItem, strTyp As String) As String
          Dim olkRec As Outlook.Recipient
          For Each olkRec In olkMsg.Recipients
              Select Case LCase(strTyp)
                  Case "to"
                      If olkRec.Type = olTo Then
                          GetAddr = GetAddr & olkRec.Address & ", "
                      End If
                  Case "cc"
                      If olkRec.Type = olCC Then
                          GetAddr = GetAddr & olkRec.Address & ", "
                      End If
              End Select
          Next
          If Len(GetAddr) > 0 Then
              GetAddr = Left(GetAddr, Len(GetAddr) - 2)
          End If
      End Function
      

      With that function added, change this line (from rev 42)

      .Cells(intRow, 6) = olkMsg.CC
      

      to

      .Cells(intRow, 6) = GetAddr(olkMsg, "cc")
      

      The function returns a comma-delimited list of the CC recipient’s addresses.

  19. Hi David, What can i do to get revision 46 to display the IP information of the sender. I would like to use the csv on kibana

    • Hi, Barbara.

      Doing that will be difficult. Outlook messages don’t store the sender’s IP address in a property. That address is normally in the message’s internet header, but it isn’t consistently marked, so positively identifying it will be a bit of a challenge. One approach would be to extract all the IP addresses from the internet header. The last one in line should be the sender’s IP, but there’s no guarantee it will be. Internet headers vary a lot from email system to email system. It’s entirely possible that a given header will have an IP address lower in the header.

    • Thank you David for your response. I decided to try out a perl script to extract the content on the header.

    • You’re welcome, Barbara.

      If you don’t mind sharing, I’d be interested in hearing how that goes. It’s not a matter of language (i.e. VBA versus Perl), it’s a matter of being able to pick the IP you want out of the IPs that appear in the header. If they were all tagged and if all email systems used the same set of tags, or if the address was consistently in the same location, then this would be easy. If you find a working Perl solution, then I’d like to see what logic the author uses to always get the correct IP.

    • I was able to create a grok parser that can parse everything you need from an email.
      Here is an example:-
      grok {
      named_captures_only => true
      match => [“message”, “%{DATA:comma}Delivered-To: %{DATA:email}Received: by %{IPV4:receivedbyip} with SMTP id %{DATA:receivedbysmtpid} %{SPACE}%{DATA:timestamp} X-Received: by %{IPV4:xreceivedbyip} with SMTP id %{DATA:xsmtpid}; %{SPACE}%{DATA:xreceivedtimestamp} Return-Path: %{DATA:returnemail} Received: from %{DATA:receivedemail}%{SPACE}Received-SPF: %{DATA:receivedspf}%{SPACE} client-ip=%{DATA:clientip};%{SPACE}Authentication-Results: %{DATA:authentication}%{SPACE}[%{DATA:clientip1}]%{SPACE}Date: %{DATA:date}%{SPACE}Subject: %{DATA:subject}%{SPACE}Content-Type: %{DATA:content}” ]
      }

  20. Hi David,

    Thank you for all your effort. They are so valuable!

    My question is about exporting the messages as “pdf” files (or printing them with pdf printer), and linking them from the excel file? Is it possible? I’m trying to export all the messages with all the subfolders with the columns “date (as dd/mm/yyyy, name of the day), time (with reference, e.g. gmt+2), sender, to, cc, subject, replied on”.

    Thanks in advance for you valuable help,

    Erman

    • Hi, Erman.

      With the exception of “replied on”, this looks doable. Outlook doesn’t keep track of when a message was replied to. Instead, it stores the last verb (e.g. reply, reply all, forward) used. Using last verb to determine if and when a message was replied to is unreliable. Here’s why. Open a message and click reply. Now, cancel that reply and re-open the original message. It will say that you replied when in fact you never sent that reply. The only other way to determine if and when you replied is to find all related messages and check to see if one of them is a reply. It’s possible, but depending on the number of messages in your mailbox (to include personal folders), finding the related messages and making that determination could take awhile.

      What version of Outlook are you using?

    • Hi David,

      Thank you for your response, I’m using Outlook 2013.
      By the way is there any way to export messages as pdf? I would like to add this code into one of your macros.

      Thanks,
      Erman

    • Hi, Erman.

      You’re welcome.

      Yes, it’s possible to export a message to PDF. The code below does just that. You can call this from one or my other macros. the routine to call is SaveMessagesAsPDF. Pass it the message to save and the path to save it to. For example

      SaveMessageToPDF Application.ActiveExplorer.Selection(1), "c:\users\erman\documents"
      

      would save the currently selected message in PDF format to your My Documents folder.

      Sub SaveMessageAsPDF(olkMsg As Outlook.MailItem, strPth As String)
          Const wdExportAllDocument = 0
          Const wdExportDocumentContent = 0
          Const wdExportOptimizeForPrint = 0
          Const wdExportCreateNoBookmarks = 0
          Const wdExportFormatPDF = 17
          Dim wrdApp As Object, wrdDoc As Object, strFil As String, strTmp As String
          If Right(strPth, 1) <> "\" Then strPth = strPth & "\"
          strTmp = Environ("TEMP") & "\SaveMessageAsPDF.rtf"
          olkMsg.SaveAs strTmp, olRTF
          strFil = strPth & RemoveIllegalCharacters(olkMsg.Subject) & ".pdf"
          Set wrdApp = CreateObject("Word.Application")
          Set wrdDoc = wrdApp.Documents.Open(strTmp)
          wrdApp.ActiveDocument.ExportAsFixedFormat OutputFileName:= _
              strFil, ExportFormat:=wdExportFormatPDF, _
              OpenAfterExport:=False, OptimizeFor:=wdExportOptimizeForPrint, _
              Range:=wdExportAllDocument, From:=0, To:=0, Item:= _
              wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _
              CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _
              BitmapMissingFonts:=True, UseISO19005_1:=False
          wrdDoc.Close False
          Set wrdDoc = Nothing
          Set wrdApp = Nothing
      End Sub
      
      Function RemoveIllegalCharacters(strValue As String) As String
          ' Purpose: Remove characters that cannot be in a filename from a string.'
          ' Written: 4/24/2009'
          ' Author:  David Lee'
          ' Outlook: All versions'
          Dim objRegEx As Object
          Set objRegEx = CreateObject("VBscript.RegExp")
          With objRegEx
              .IgnoreCase = True
              .Global = True
              .Pattern = "[<>:""\\\/|?*]+"
              RemoveIllegalCharacters = objRegEx.Replace(strValue, "")
          End With
          Set objRegEx = Nothing
      End Function
      

      Note: This code is a modified version of fellow Outlook MVP Diane Poremsky’s excellent solution which is available at this Slipstick.com page.

    • David, please disregard my previous comment, because I’ve managed to achieve.

      I’ve changed “SaveMessageAsPDF olkMsg” and put this under the “ProcessFolder” loop (just below the counter). Then It started to print every message as pdf files. But this time, surprisingly, process counts for “winword.exe” went to infinite (equal to the e-mail count). So my computer become unresponsive. Is there any slow but responsive way to do that? Opening these every time with winword process and printing them is an expensive way to do this for every message. As I understand, all the processes are kept in the memory during all the exportation phase and there is also nothing defined for them to be killed just after the successfull exportation.

    • Hi, Erman.

      I’m not sure why that occurs sometimes. This line

      Set wrdApp = Nothing
      

      should close each instance of Word. Since it’s not, you can add this line

      wrdApp.Quit
      

      which will terminate Word. You’ll need to add that line immediately before

      Set wrdApp = Nothing
      
  21. Hi David,

    Great work here and I thought I had the correct solution with #48 but this is the one that doe snot work.

    I am looking for a solution that can export certain fields and data form the body of th email and populate an excel sheet. All straight forward but I don’t want to copy duplicates and therefore #48 tagged each time to completed the macro and therefore would not allow you to do this twice.

    When I copy the code it does not show as a macro?

    Can you help at all please.

    • Hi, Darren.

      Thanks!

      So you’ve copied the code and added it to Outlook, but when you go to run the macro you don’t see ExportMessagesToExcel in the list of available macros. Correct? If so, then are any of the lines of code colored red?

    • Hi David, Thank you for your quick reply. I have managed to amend the code as follows and it now appears as a macro I can run and works! I think it was the Private sub to sub change that worked?

      The changing of the categorisation is brilliant, can I run a separate macro that looks for this category and then moves them all into a specific outlook folder called processed etc.?

      Thanks again!

      Const WORKBOOK_NAME = “C:\Users\dbooker\Desktop\Wormcount.xlsx”
      Const CATEGORY_NAME = “Arun”
      Const ROOT_DIR = “C:\Users\dbooker\Desktop”
      Const MACRO_NAME = “Sub ExportMessagesToExcel()”
      Sub ExportMessagesToExcel()

    • Hi, Darren.

      I’m glad the solution is working for you.

      Yes, it’s possible to use another macro to find the items in a given category and move them all to another folder. Assuming that you have Outlook 2007 or later, then a simpler solution might be to create a quick step that does that. Are you familiar with quick steps?

    • Hi David,

      I do have Outlook 2010 but not familiar with Quick step?

      Are you able to give me a quick steer on where to look?

      My thoughts are to move all emails that have had the category changed into a specified location by a manual option.

      Thank you again!

      Darren

    • Darren,

      Quick Steps are in the “Quick Steps” group on the ribbon when you have the “Home” tab selected. Click the “Create New” option and Outlook will launch a wizard that walks you through the process.

    • Hi David,

      I can add a category using quick steps but not move it to a folder based upon a type of category.

      I have used the previous VBA that processes the email and puts it into Excel, this then changes the category to ARUN.

      I then need to start a macro or something that says… For all messages that have a category of “ARUN” move to a specific folder.

      Any of course any that have not been processed will remain in the original folder.

      Hope that makes sense!

      Many thanks, Darren

    • Hi, Darren.

      You’re correct. A quick action won’t find items. Sorry, I often forget that other folks don’t work the way I do. I categorize all of my messages and keep my inbox organized by category. That allows me to select an entire category and perform an action on it (e.g. moving all the messages in a category to a folder). If you don’t organize by category and don’t want to organize by category, then I can provide a macro that finds all the items belonging to a category and move them to a folder.

  22. I can’t get r36 to work. I need the body also, but when I run the original, I get the sender, date, etc., correctly, but when I add body, I only get the body. Same with your revision 36 – I lose the sender, date, etc, but get the body. Ideally, I need r4 and r36 combined, but I would love to get the subject, date, sender and body regardless. Any help greatly appreciated!!

    • Hi, Jay.

      What version of Office are you running and on what version of Windows? Do you get any error messages when you run the code?

  23. David,

    Is it possible to write a macro that automatically runs on the first of each month for the previous month, based on date received? The macro would need to run two searches based off of text in the subject line, sum the results of each search, and then email the totals of each search to a group of people. Can this be done? I am a novice and any help with the coding would be appreciated.

    Currently using Outlook 2007, but in the near future will be upgrading to 2010 or 2013.

    • Hi, Nick.

      Yes, that’s possible. The best solution would be to use a script that runs outside of Outlook (i.e. VBscript instead of an Outlook macro). That allows you to scedule the script using Windows Task Scheduler. I’ll need to know what you want to search for in the subject line and what to sum in order to create a solution.

    • David,

      Here is a novice question, how do I access the Windows Task Scheduler?

      First Search: Voicemail Message (Unavailable > 103)
      I need this to show the total number of messages with this in the subject for the previous month.

      Second Search: 3 Subjects: Voicemail Message (Unavailable > USO), Voicemail Message (Unavailable > ESO), and Voicemail Message (Unavailable > ASO)
      I need a sum of all of those messages within the previous month. Could we have the total for each subject and then sum them all?

      Thanks for the help. I am a novice but want to learn more about how to do this kind of stuff.

    • Nick,

      Open the Control Panel and select Administrative Tools. When the Administrative Tools window opens you should see Task Scheduler. Windows Task Scheduler allows you to run Windows tools, programs, and scripts on a schedule. Outlook does not have a built-in way to schedule a macro. There are ways to do it, but they require additional code. It’s simpler and more practical to use Windows task scheduler rather than adding more code to Outlook.

    • Thanks David! I found it. I will probably have more questions once a solution is created.

      Thanks Again!

    • Nick,

      One additional question. Does the solution need to search the entire mailbox (i.e. all folders to include .PST files) or only certain folders? If it’s the latter, then what folders?

    • Hi, Nick.

      Here’s my solution. Please test it out and let me know if it does what you wanted. Follow these instructions to use this solution.

      1. Open Notepad
      2. Copy the code below and paste it into Notepad
      3. Edit the email address the message resulting message is to go to
      4. Save the file. Name it as desired. The file extension must be .vbs
      5. To test the solution, double-click the script
      6. If the solution works as desired, then the next step is to create a scheduled task that runs the script on a schedule
      Const olMailItem = 0
      Const olFolderInbox = 6
      Const PROP_TAG = "http://schemas.microsoft.com/mapi/proptag/"
      Dim olkApp, olkSes, olkMsg, olkFld, lngC1, lngC2, lngC3, lngC4, datBeg, datEnd
      lngC1 = 0
      lngC2 = 0
      lngC3 = 0
      lngC4 = 0
      datEnd = DateAdd("d", -Day(Date), Date)
      datBeg = DateAdd("d", -(Day(datEnd) - 1), datEnd)
      Set olkApp = CreateObject("Outlook.Application")
      Set olkSes = olkApp.GetNamespace("MAPI")
      olkSes.Logon olkApp.DefaultProfileName
      Set olkFld = olkSes.GetDefaultFolder(olFolderInbox)
      lngC1 = GetMatchingItemCount(olkFld, "@SQL=((""urn:schemas:httpmail:datereceived"" >= '" & datBeg & " 12:00 AM' AND ""urn:schemas:httpmail:datereceived"" <= '" & datEnd & " 11:59 PM') AND ""urn:schemas:httpmail:subject"" LIKE '%Voicemail Message (Unavailable > 103)%')")
      lngC2 = GetMatchingItemCount(olkFld, "@SQL=((""urn:schemas:httpmail:datereceived"" >= '" & datBeg & " 12:00 AM' AND ""urn:schemas:httpmail:datereceived"" <= '" & datEnd & " 11:59 PM') AND ""urn:schemas:httpmail:subject"" LIKE '%Voicemail Message (Unavailable > USO)%')")
      lngC3 = GetMatchingItemCount(olkFld, "@SQL=((""urn:schemas:httpmail:datereceived"" >= '" & datBeg & " 12:00 AM' AND ""urn:schemas:httpmail:datereceived"" <= '" & datEnd & " 11:59 PM') AND ""urn:schemas:httpmail:subject"" LIKE '%Voicemail Message (Unavailable > ESO)%')")
      lngC4 = GetMatchingItemCount(olkFld, "@SQL=((""urn:schemas:httpmail:datereceived"" >= '" & datBeg & " 12:00 AM' AND ""urn:schemas:httpmail:datereceived"" <= '" & datEnd & " 11:59 PM') AND ""urn:schemas:httpmail:subject"" LIKE '%Voicemail Message (Unavailable > ASO)%')")
      Set olkMsg = olkApp.CreateItem(olMailItem)
      With olkMsg
          'Edit the email address on the next line
          .To = "someone@company.com"
          .Subject = "Counts for " & MonthName(Month(datBeg), False) & " " & Year(datBeg)
          .HTMLBody = "Voicemail Message (Unavailable > 103) = " & lngC1 & "<br>" & _
                      "Voicemail Message (Unavailable > USO) = " & lngC2 & "<br>" & _
                      "Voicemail Message (Unavailable > ESO) = " & lngC3 & "<br>" & _
                      "Voicemail Message (Unavailable > ASO) = " & lngC4 & "<br>" & _
                      "Total of USO/ESO/ASO = " & (lngC2 + lngC3 + lngC4)
          .Send
      End With
      Set olkFld = Nothing
      Set olkMsg = Nothing
      Set olkSes = Nothing
      Set olkApp = Nothing
      
      Function GetMatchingItemCount(olkFld, strFlt)
          Dim olkTab
          Set olkTab = olkFld.GetTable(strFlt)
          GetMatchingItemCount = CLng(olkTab.GetRowCount)
          Set olkTab = Nothing
      End Function
      

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s