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.
Listing A: 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
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
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.
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.
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 email@example.com.