Export Outlook 2003 emails with Date and Address

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.


spathiphylumAsked:
Who is Participating?
 
David LeeConnect With a Mentor Commented:
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
 
David LeeCommented:
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
 
spathiphylumAuthor Commented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
spathiphylumAuthor Commented:
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
 
David LeeCommented:
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
 
spathiphylumAuthor Commented:
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
 
David LeeCommented:
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
 
spathiphylumAuthor Commented:
great.  thanks!
0
 
David LeeCommented:
You're welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.