Software

Quickly export Outlook e-mail items to Excel

Exporting e-mail messages from Outlook to Excel is easier than you might think. This sample VBA code automates the process, allowing you to specify exactly which fields to copy.

Office makes collaborating between applications easy, which is good news when you have data in one application but you need it in another. For instance, you might want to export a list of e-mail messages about a specific project, or from a specific person, to Excel for quick sorting, formatting, or more likely, to share with a non-Office application. The good news is that the process is simple to automate — a bit of VBA code and a way to execute it gets the job done. (This article's sample code was written for Office 2003, but it should work as is or with minor adjustments in Office 2000 and 2002.)

Note: This information is also available as a PDF download, along with a BAS file containing the sample code.

Adding the export code

Working from Outlook, launch the Visual Basic Editor (VBE) by pressing Alt + F11. Choose Module from the Insert menu and enter the VBA code shown in Listing A. Next, reference the Excel object library. To do so, choose References from the Tools menu (while still in the VBE) and check Microsoft Excel 11.0 Object Library, as shown in Figure A. Then, click OK to return to the VBE.

Figure A: Update the library references to include Excel's object library.

object library

Listing A: ExportToExcel()

Sub ExportToExcel()
  On Error GoTo ErrHandler
  Dim appExcel As Excel.Application  Dim wkb As Excel.Workbook

Dim wks As Excel.Worksheet

Dim rng As Excel.Range

Dim strSheet As String

Dim strPath As String

Dim intRowCounter As Integer

Dim intColumnCounter As Integer

Dim msg As Outlook.MailItem

Dim nms As Outlook.NameSpace

Dim fld As Outlook.MAPIFolder

Dim itm As Object
    strSheet = "OutlookItems.xls"  strPath = "C:Examples\"

strSheet = strPath & strSheet

Debug.Print strSheet
  'Select export folder
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder
  'Handle potential errors with Select Folder dialog box.
If fld Is Nothing Then

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

"Error"

Exit Sub

ElseIf fld.DefaultItemType <> olMailItem Then

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

"Error"

Exit Sub

ElseIf fld.Items.Count = 0 Then

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

"Error"

Exit Sub

End If
  'Open and activate Excel workbook.
Set appExcel = CreateObject("Excel.Application")

appExcel.Workbooks.Open (strSheet)

Set wkb = appExcel.ActiveWorkbook

Set wks = wkb.Sheets(1)

wks.Activate

appExcel.Application.Visible = True
  'Copy field items in mail folder.
For Each itm In fld.Items

intColumnCounter = 1

Set msg = itm

intRowCounter = intRowCounter + 1

Set rng = wks.Cells(intRowCounter, intColumnCounter)

rng.Value = msg.To

intColumnCounter = intColumnCounter + 1

Set rng = wks.Cells(intRowCounter, intColumnCounter)

rng.Value = msg.SenderEmailAddress

intColumnCounter = intColumnCounter + 1

Set rng = wks.Cells(intRowCounter, intColumnCounter)

rng.Value = msg.Subject

intColumnCounter = intColumnCounter + 1

Set rng = wks.Cells(intRowCounter, intColumnCounter)

rng.Value = msg.SentOn

intColumnCounter = intColumnCounter + 1

Set rng = wks.Cells(intRowCounter, intColumnCounter)

rng.Value = msg.ReceivedTime

Next itm
  Set appExcel = Nothing  Set wkb = Nothing

Set wks = Nothing

Set rng = Nothing

Set msg = Nothing

Set nms = Nothing

Set fld = Nothing

Set itm = Nothing
  Exit Sub
ErrHandler:  If Err.Number = 1004 Then

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

"Error"

Else

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

"Error"

End If

Set appExcel = Nothing

Set wkb = Nothing

Set wks = Nothing

Set rng = Nothing

Set msg = Nothing

Set nms = Nothing

Set fld = Nothing

Set itm = Nothing
End Sub

Using the code to export

To execute the code, position the cursor inside the procedure and press F5. Instruct your users to choose Macro from the Tools menu (inside Outlook), select Macros, select ExportToExcel in the resulting dialog box, and click Run. If they use this technique often, consider adding a button to the toolbar.

After declaring a few variables, the code displays the Select Folder dialog shown in Figure B.

Figure B: Select the folder that contains the messages you want to export.

selecting a folder

Select a folder and click OK. Next, the code handles the following potential errors:

  • The user clicks Cancel to close the Select Folder dialog box.
  • The user selects a non-mail folder
  • The mail folder contains no mail items

Then, the code identifies and opens an Excel workbook. In this sample code, the workbook must exist. You should update this code to accommodate your system and Excel workbook. This is also a good spot for further automating the technique by allowing users to select an existing workbook or to create a new one. For our purposes, hard coding the workbook simplifies the process.

The For Each loop is the heart of this exporting technique. There are two counters, intRowCounter and intColumnCounter. As the code inserts field values from the current message, the code updates intColumnCounter. Once the code has inserted all of the current message items, it updates intRowCounter. Without these counters, the code would write over each value in A1.

The code now inserts the first field item in A1. You can offset that by specifying a starting value for one or both counters to allow for headers or to append records instead of writing over existing values. In addition, this sample code copies only a few fields: To, SenderEmailAddress, Subject, SentOn, and ReceivedTime, as shown in Figure C.

Figure C: The macro has copied the items in the specified folder to Excel.

exported messages

You can add as many fields as you need. Just be sure to include a column update statement for each field you want to copy. For instance, if you want to export the actual message text, you can add the following code:

    Set rng = wks.Cells(intRowCounter, intColumnCounter)    rng.Value = msg.Body
intColumnCounter = intColumnCounter + 1

Note that this process doesn't transfer all the characters perfectly. You might see a few phantom characters in your Excel workbook. Also, keep in mind that the code doesn't do any formatting — you'll have to adjust column widths and so on manually or add code to take care of the task.

If you notice that a specific field generates an error if the field is empty, use an If statement in the form:

If msg.field <> "" Then rng.Value = msg.field

to handle that error. None of the fields in the sample code poses a problem if the field is empty. The code simply leaves the appropriate cell in Excel blank.

After the Next statement and before setting all the object variables to Nothing, you might want to add code that handles the open Excel workbook in some way. For instance, you might save it and then close it.

The error handling routine is generic and simple. Be sure to test this code thoroughly and enhance it accordingly.


Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at ssharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks