spathiphylum
asked on
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.
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.
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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.
ASKER
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
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
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
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
ASKER
great. thanks!
You're welcome.
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.Connec
'Replace the connection string on the following line with the connection string to the SQL table
adoCon.Open "Provider=Microsoft.Jet.OL
Set olkItems = Application.ActiveExplorer
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.SenderEmailAddr
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.