Solved

Export email from Outlook to Access

Posted on 2011-09-27
6
329 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
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 500 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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now