?
Solved

Export Outlook 2003 emails with Date and Address

Posted on 2007-08-10
9
Medium Priority
?
252 Views
Last Modified: 2013-11-05
I'm looking for some way to export emails from Outlook 2003

1. If i use Outlook's export feature, it will export the email addresses, but not the date
2. if I link to Outlook from Access and import, I get the dates, but the email addresses don't always show up:  instead of "john.doe@hotmail.com"  it will just export "John Doe".   I need the address

I also need the email body.  I've looked at the EML files which seem to have all i need, but I don't know how to parse them.

the ideal solution would also create one-to-many tables for the To: CC: addresses (i.e. if an email was sent to multiple ppl).  ideally it would also be automated so i wouldn't have to do any dragging / dropping.   this may be a lot to ask though so if i dont get a solution that answers all of this, it might work.


0
Comment
Question by:spathiphylum
  • 5
  • 4
9 Comments
 
LVL 76

Expert Comment

by:David Lee
ID: 19674578
Hi, spathiphylum.

Using a bit of scripting you can export and import if you want to, but you can also move the data into Access directly from Outlook without the need to export/import.  Using an ADODB connection object we can write directly to an Access table.  It's very simple.  Here's an example I wrote for another question.

Sub ExportAddressesToDatabase()
    Dim olkItems As Outlook.Items, _
        olkMessage As Outlook.MailItem, _
        adoCon As ADODB.Connection
    Set adoCon = CreateObject("ADODB.Connection")
    'Replace the connection string on the following line with the connection string to the SQL table
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\eeTesting\CleverBob.mdb;Persist Security Info=False"
    Set olkItems = Application.ActiveExplorer.CurrentFolder.Items
    For Each olkMessage In olkItems
        'Replace the name of the table and field in the following line to those used in your SQL database
        adoCon.Execute "INSERT INTO MyAddresses (Address) VALUES (" & Chr(34) & olkMessage.SenderEmailAddress & Chr(34) & ")"
    Next
    adoCon.Close
    Set adoCon = Nothing
    Set olkMessage = Nothing
    Set olkItems = Nothing
    MsgBox "All done exporting addresses."
End Sub

This particular routine exports the sender's address of each message in the selected folder.  It's simple enough to modify this code to export anything you want from the message and put it into one or more tables.
0
 

Author Comment

by:spathiphylum
ID: 19676845
Great.  I'll try it out.  So, what are the other field names for message body, CC addresses, and date?  

Is there a reference somewhere which shows all properties that i could access?

Also, it would be nice (though not necessary) if I could run this code from Access rather than changing Outlook.  Possible?
0
 
LVL 76

Accepted Solution

by:
David Lee earned 2000 total points
ID: 19676938
Yes, there is a reference, both online (http://msdn2.microsoft.com/en-us/library/aa210946(office.11).aspx) and in Outlook's built-in help.

Yes, with a minor modification you could run this code from inside Access.  If you did that, then you could write to the Access tables more directly if you wanted.  I'm not an Access expert, so I don't have an example of doing that.  Here's a revised version of the code above modified to run from inside Access.

Sub ExportAddressesToDatabase()
    Dim olkApp As Outlook.Application, _
        olkItems As Outlook.Items, _
        olkMessage As Outlook.MailItem, _
        adoCon As ADODB.Connection
    Set adoCon = CreateObject("ADODB.Connection")
    'Replace the connection string on the following line with the connection string to the SQL table
    adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\eeTesting\CleverBob.mdb;Persist Security Info=False"
    'Assumes that Outlook is already open and running
    Set olkApp = GetObject(,"Outlook.Application")
    Set olkItems = OpenMAPIFolder("\SomeFolderPath").Items
    For Each olkMessage In olkItems
        'Replace the name of the table and field in the following line to those used in your SQL database
        adoCon.Execute "INSERT INTO MyAddresses (Address) VALUES (" & Chr(34) & olkMessage.SenderEmailAddress & Chr(34) & ")"
    Next
    adoCon.Close
    Set adoCon = Nothing
    Set olkMessage = Nothing
    Set olkItems = Nothing
    Set olkApp = Nothing
    MsgBox "All done exporting addresses."
End Sub

'Credit where credit is due.
'The code below is not mine.  I found it somewhere on the internet but do
'not remember where or who the author is.  The original author(s) deserves all
'the credit for these functions.
Function OpenMAPIFolder(szPath)
    Dim app, ns, flr, szDir, i
    Set flr = Nothing
    Set app = CreateObject("Outlook.Application")
    If Left(szPath, Len("\")) = "\" Then
        szPath = Mid(szPath, Len("\") + 1)
    Else
        Set flr = app.ActiveExplorer.CurrentFolder
    End If
    While szPath <> ""
        i = InStr(szPath, "\")
        If i Then
            szDir = Left(szPath, i - 1)
            szPath = Mid(szPath, i + Len("\"))
        Else
            szDir = szPath
            szPath = ""
        End If
        If IsNothing(flr) Then
            Set ns = app.GetNamespace("MAPI")
            Set flr = ns.Folders(szDir)
        Else
            Set flr = flr.Folders(szDir)
        End If
    Wend
    Set OpenMAPIFolder = flr
End Function

Function IsNothing(obj)
  If TypeName(obj) = "Nothing" Then
    IsNothing = True
  Else
    IsNothing = False
  End If
End Function
0
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.

 

Author Comment

by:spathiphylum
ID: 19685201
When I go to the MSDN link, it shows a bunch of properties, but I don't see Date among them.  Am I missing something?
0
 
LVL 76

Expert Comment

by:David Lee
ID: 19685274
There's more than one date, really date and time, property.  CreationTime, ReceivedTime, LastModificationTime, etc.  Although these property names don't contain the word "date" they in fact contain both the date and time.  There is no property called Date.
0
 

Author Comment

by:spathiphylum
ID: 19686354
Ok.  Getting close!  

I used these three fields: EmailAddress, ReceivedDate, Body.  It works for items in the Inbox, but not Sent Items.    When I put it on Sent Items I use the "To" property, but as the documentation describes, that's only the display name (i.e. "John Doe" instead of "john.doe@email.com").   Any way to get the email address(es)?  

The documentation you pointed me to (http://msdn2.microsoft.com/en-us/library/aa220513(office.11).aspx)  alludes to the Recipents collection, but I'm not sure what how to use it


0
 
LVL 76

Expert Comment

by:David Lee
ID: 19686443
The Recipients collection contains the details of each individual addressee.  You'd use it like this:

Dim olkRecipient As Outlook.Recipient
For Each olkRecipient in olkMessage.Recipients
    'Code to process each recipient
    'The e-mail address property is called Address, i.e. olkRecipient.Address
Next
0
 

Author Comment

by:spathiphylum
ID: 19687104
great.  thanks!
0
 
LVL 76

Expert Comment

by:David Lee
ID: 19687899
You're welcome.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I discuss my selections of the Top Four free Outlook OST File Viewers available. Open, view and read even damaged OST files by using these tools. They all provide a clear preview of all data such as emails, notes, tasks, calendars, e…
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.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Suggested Courses

807 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