?
Solved

Export email from Outlook to Access

Posted on 2011-09-27
6
Medium Priority
?
363 Views
Last Modified: 2013-02-15
Hi Experts,

how can I programatically copy emails from a specific folder i Outlook (with attachments) to an Access table with VB.NET (alt. in Access or Outlook)

Thanks!

0
Comment
Question by:PerryDaynac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 36708409
If exchange based, link to the exchange folder, and you will have these as tables (will depand on version of Access - I have a hunch support for linking to exchange was dropped in Access 2010)

Kelvin
0
 
LVL 2

Accepted Solution

by:
jdc1944 earned 2000 total points
ID: 36708719
Not actually given this a go myself but i do have a guide from TechRepublic which details how to export from Outlook to Access in a programmatic way.

The main points from that guide are below....

In Outlook, press Alt+F11 to launch the Visual Basic Editor (VBE). Choose Module from the Insert menu and then enter the code shown below.

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

Open in new window


Don’t run the macro yet. If you are using an existing database 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 code uses a data source name (DSN) to connect to the database, unless you want to write your own connection string.To create a DSN in Windows XP, follow the steps below.
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). Your settings will reflect your environment; you don’t have to recreate ours exactly.
9. Click OK twice to create the DSN, which is an internal connection to the target file.

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 ExportMailByFolder4. Click Run.

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.
0
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 36710839
See my Access Archon article on this topic (storing Outlook email messages in an Access table):

http://www.helenfeddema.com/Files/accarch102.zip
0
 

Author Comment

by:PerryDaynac
ID: 36716276
jdc1944:
Your solution works fine (I had to set a referense to MS ActiveX Data Objects 2.8 Library).
However I have not figured out how to get the attachment (pdf, wordfile) into the access database.
Any Ideas?

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
If you troubleshoot Outlook for clients, you may want to know a bit more about the OST file before doing your next job. IMAP can cause a lot of drama if removed in the accounts without backing up.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question