Software

How do I... Export e-mail messages to Access using VBA?

Exporting Outlook folders and their contents is a common and simple enough task most of the time. The Import And Export wizard handles the process nicely. But as easy as it is to use, the wizard is a one-time deal. You can't append records to an existing file using the wizard. If you want to export frequently to the same database, or if you just want to take the process out of user hands, use VBA to automate the export.Let's look at the wizard approach first. Then, we'll put VBA to work to handle the task programmatically.

Note: This information is also available as a PDF download.

A manual export

Outlook supports many export possibilities, including exporting e-mail messages to Access (and Excel). To use the wizard, from inside Outlook, complete these steps:

  1. Choose Import And Export from the File menu.
  2. To export to Access or Excel, select the Export To A File option and click Next.
  3. Choose Microsoft Access (or Microsoft Excel) and click Next.
  4. Select the folder you want to export, as shown in Figure A, and click Next. If you select a folder with subfolders, the wizard also exports the items in the subfolders.
  5. In the following window, click Browse to specify the location of the exported data and click Next. You don't have to specify a file, just the path. The wizard creates the appropriate file in the folder you specify.
  6. If you want, you can map specific properties to columns in the target file before clicking Finish. Depending on the number of e-mail messages in the folder, the process could take a moment or two.

Figure A: Select the folder you intend to export.

An automated export

Automating the process via a macro (a VBA function) is more complicated but certainly doable. In Outlook, press Alt+F11 to launch the Visual Basic Editor (VBE). Choose Module from the Insert menu and then enter the code shown in Listing A. Listing A

Sub ExportMailByFolder()

'Export specified fields from each mail

'item in selected folder.

Dim ns As Outlook.NameSpace

Dim objFolder As Outlook.MAPIFolder

Set ns = GetNamespace("MAPI")

Set objFolder = ns.PickFolder

Dim adoConn As ADODB.Connection

Dim adoRS As ADODB.Recordset

Dim intCounter As Integer

Set adoConn = CreateObject("ADODB.Connection")

Set adoRS = CreateObject("ADODB.Recordset")

'DSN and target file must exist.

adoConn.Open "DSN=OutlookData;"

adoRS.Open "SELECT * FROM email", adoConn, _

adOpenDynamic, adLockOptimistic

'Cycle through selected folder.

For intCounter = objFolder.Items.Count To 1 Step -1

With objFolder.Items(intCounter)

'Copy property value to corresponding fields

'in target file.

If .Class = olMail Then

adoRS.AddNew

adoRS("Subject") = .Subject

adoRS("Body") = .Body

adoRS("FromName") = .SenderName

adoRS("ToName") = .To

adoRS("FromAddress") = .SenderEmailAddress

adoRS("FromType") = .SenderEmailType

adoRS("CCName") = .CC

adoRS("BCCName") = .BCC

adoRS("Importance") = .Importance

adoRS("Sensitivity") = .Sensitivity

adoRS.Update

End If

End With

Next

adoRS.Close

Set adoRS = Nothing

Set adoConn = Nothing

Set ns = Nothing

Set objFolder = Nothing

End Sub

Don't run the macro yet. Unlike with the manual process, you need to create the target file first. The easiest way to do so is to simply run the wizard and then use the resulting file. It'll create the database (or workbook) much more quickly than you can. You can also work with an existing database. In that case, add a new table named email with the appropriate fields, which should all be text fields except for Body, which requires a Memo field.

The field names in the Access table aren't predefined; you can name them anything you like. This code uses the field names generated by the Import And Export wizard. The e-mail properties are predefined. You can get those from the code listing. Name the table anything you like, but be sure to update the Recordset object's Open method statement appropriately.

The code uses a data source name (DSN) to connect to the database. Feel free to write a connection string to open the Connection object instead. A DSN lets you open a connection when you don't have the expertise to write the appropriate connection string. Even if you do, the DSN is still easier. To create a DSN in Windows XP, follow the steps below. (Instructions may vary a bit for other versions.)

  1. From the Start menu, choose Control Panel.
  2. Double-click Administrative Tools
  3. Double-click Data Sources (ODBC).
  4. In the User DSN tab, choose MS Access Database and click Add.
  5. In the next window, choose MS Access Driver (.mdb) and click Finish.
  6. In the ODBC Microsoft Access Setup window, name the DSN OutlookData. You can name it anything you like, but be sure to update the Connection object's Open method in the code appropriately.
  7. Click Select in the Database section and in the resulting window, locate and select the database to which you're exporting. In this case, it's an .mdb file named FamilyMail.mdb in a folder named C off the C:\ root directory.
  8. Select the None option in the System Database section (if necessary). Figure B shows the settings for OutlookData. Your settings will reflect your environment; you don't have to re-create ours exactly.
  9. Click OK twice to create the DSN, which is an internal connection to the target file.

Figure B: Use a DSN for an easy connection.

After you create the DSN, return to Outlook and run the macro as follows:

  1. From the Tools menu, choose Macro.
  2. Select Macro from the resulting submenu.
  3. Choose ExportMailByFolder, as shown in Figure C.
  4. Click Run.

Figure C: Highlight the ExportMailByFolder.
When Outlook displays the Select Folder dialog box, choose the folder that contains the e-mail messages you want to export, as shown in Figure D. The function handles subfolders just as the wizard does. Then, click OK and the For loop copies the e-mail property values (subject, body, sender, and so on) to the target file, our Access database file, FamilyMail.mdb. This macro doesn't delete the e-mail messages; it only exports them. The connection works even with the target file (an Access database) open. If the email table is open, you must close and reopen it to see the newly imported data.

Figure D: The macro prompts you to select the folder you want to export.

Noteworthy extras

Most of the properties export meaningful data. In contrast, the Sensitivity and Importance properties export the values listed in Table A. For a more flexible function, you can replace the If statement with a Select Case statement and check for the current folder's class: olMail, olAppointment, olContact, olNote, and olTask. Regardless of what class you export, export only the values you need. For instance, if an e-mail's sensitivity, importance, or the sender's e-mail type isn't important, don't export that value. If you display the Select Folder dialog box (Figure D) at other times, move the first several lines — through the Set objFolder = ns.PickFolder statement — to another function. That way, you can reuse that bit of code for other tasks. The current code contains no error handling. You'll want to test it thoroughly in your environment and accommodate potential errors. For instance, failing to select a folder will generate an error. Table A

Property Value Constant
Sensitivity (olSensitivity) 0 olNormal
1 olPersonal
2 olPrivate
3 olConfidential
Importance (olImportance) 0 olImportanceLow
1 olImportanceNormal
2 olImportanceHigh


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@setel.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