Gandalf's notebook: Exporting Outlook Messages to Excel

From http://techniclee.wordpress.com/

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 2003 and later.

Adding the code to Outlook.

Start Outlook

Press ALT+F11 to open the Visual Basic Editor

If not already expanded, expand Microsoft Office Outlook Objects

If not already expanded, expand Modules

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.

Copy the code from the code snippet box and paste it into the right-hand pane of Outlook’s VB Editor window

Click the diskette icon on the toolbar to save the changes

Close the VB Editor

Sub ExportMessagesToExcel()
   Dim olkMsg As Outlook.MailItem, _
       excApp As Object, _
       excWkb As Object, _
       excWks As Object, _
       intRow 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
       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.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(intRow, 1) = olkMsg.Subject
               excWks.Cells(intRow, 2) = olkMsg.ReceivedTime
               excWks.Cells(intRow, 3) = olkMsg.SenderEmailAddress
               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

Using the Code.

With Outlook open select one or more messages from any folder.

Run the macro.

When prompted enter a filename to save the export to. You can cancel the export by not entering anything.

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).