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.

17 comments
zw0503
zw0503

Worked like a charm fixed some issues and changed the field to my needs.

I now use it with MSSQL and MYSQL for users that have replied to a mail they got from our webapplication.

I can now process external mail filtered on subject within the application.

Thanks

HelpKippers
HelpKippers

Hi Susan and Everyone I am a total novice at this stuff. The IT department of the company I work for is messing around with my queries so I hope you will be able to help me. I have tried this thread but I must be missing something as its not working. I am working on outlook 2003 and access 2003. My problem is that I need my emails to automatically go to an existing access table that is used to evaluate and reference each email individually. I???m not really code illiterate so need to know what I need to change on the code to suit my setup. Please please help my deadline is running out quickly. Thanks

abdulaal
abdulaal

Hi, I did manual export to Access, but email date was not exported!!, any ideas?

mahboop
mahboop

Is this code applicable for MS Access 2007? because some parts isn't working Thanks

dikomix
dikomix

Hello. Please how about automatic exporting to a .pst file? It is posible?

dikomix
dikomix

Hello. Thanks for this. Please, it is posible export a folder using automatic export in a .pst file?

maarten.Casier
maarten.Casier

When running this code I get often a warning that the "Run-time error '13': Type mismatch. This results in failing the procedure. I don't find the error. Anybody can help?

pjd
pjd

Rather than import the emails to the database from Outlook, I want to have a command button on an Access form that would simply allow a browse to the folder (which the code did with no problem)and set up a link to the email folder with the link displayed in a textbox that could be dclicked to open the Outlook folder. Any suggestions?

luke.mckean
luke.mckean

Hi there - thanks for the above. I am trying to export a .csv attachment from a specific e-mail folder (as above) into an existing excel spreadsheet? I think the example above will help me however when I try and run it I get an error message stating - Compile Error: User-defined type not defined. Any ideas how I can fix this and also create the macro stated aboce? Cheers Luke

kbsudhir
kbsudhir

Hi Susan, Thanks for the post, it was just great. But I have a question here. Can we save the whole of email message in the access 2003, instead of its attributes as you have shown here. Basically it is important for me save the whole mail as it is and retrive it as a documented proof. Creating a database will be easiest eay for me retrive the data even after a years time. Is it possible, if yes can you please show a demo here on the same. Thanks For The Great Post Sudhir

clintonc
clintonc

How about exporting to a directory?

JNE
JNE

Thank you Susan! A very stimulating article. It prompts me to think that a great sequel would demonstrate applying VBA to Outlook forms data. My scenario is contractors who routinely mail a custom OL form to provide "case status update" or "case closed" data. Often the form has labeled fields in the standard message body and an additional tab containing more fields. It would be great to automatically draw the data out of these 'transactions', into a file for import into Excel, or directly create new Access DB records. Would you care to consider presenting a sequel that could outshine the original? (Long live alternatives to web forms.) J.Eddy

rthomas
rthomas

Good stuff, but a couple of steps are missing. In the VB editor, using the menu option Tools...References, "Microsoft Outlook xx.0 Object Library", and "Microsoft ActiveX Data Objects 2.x library must be checked (the x's, which are version numbers, will vary depending on which version of Outlook and Windows you have installed. Thanks for the article!

martipe1
martipe1

I wanted to try this method, but when I first selected "Export to a file" and clicked next nothing happened. Any ideas on what can it be wrong? Thanks

wizard57m-cnet
wizard57m-cnet

rather than adding a comment to an OLD thread. Outlook 2003 and Access 2003 are now 2 generations behind. You might be able to find an answer to your particular issues in the archived Microsoft Knowledge Base. Search through those, as well as posting a new question in Q&A.

Larry
Larry

I hardly believe that microsoft accidentally left out the "sent" and "received" dates in their "export" wizard. And yet they are not exported or even mentioned at any point! What is that all about? Well, I guess I will have to code a solution. Thanks MS.

dmosher
dmosher

How do I select the correct version of Microsoft Outlook xx.0 Object Library and Microsoft ActiveX Data Objects 2.x library?