• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

Export email from Outlook to Access

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
PerryDaynac
Asked:
PerryDaynac
1 Solution
 
Kelvin SparksCommented:
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
 
jdc1944Commented:
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
 
Helen FeddemaCommented:
See my Access Archon article on this topic (storing Outlook email messages in an Access table):

http://www.helenfeddema.com/Files/accarch102.zip
0
 
PerryDaynacAuthor Commented:
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now