Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Export email from Outlook to Access

Posted on 2011-09-27
6
Medium Priority
?
373 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

This article describes how to import Lotus Notes Contacts into Outlook 2016, 2013, 2010 and 2007 etc. with a few manual steps. You can easily export and migrate Lotus Notes contacts into Microsoft Outlook without having to use any third party tools.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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