Parse email messages to Excel/Access

[paradox34690]

Hi everyone!

This is my first post here on VBAX (yay me) and I'll just be honest, I have a task and I have NO IDEA how hard/easy this is going to be, but was hoping to get some pointers here.

I can usually be found on vbforums.com or trolling various forums to find out how to do things; I've developed multiple VB applications for the company I'm working for, have made many-a-VBA macro in Excel that have a myriad of functions (data archiving, emailing schedules out, automatic calculations, etc). I'm saying all this so I'm not viewed as a complete noob.

Now that we have that out of the way:

I've taken on a project of going through about 5000 email messages, gathering all the data in these messages, and then putting this data into a spreadsheet so it can be used as a metric tracker. The problem is that doing it manually takes FOREVER; There simply HAS to be a better way.

Is there a method to go through an archived PST file, look in a folder in that PST file, and extract all the data from each message?

I'll be more than happy to post a copy of the template that is used as well as a final "product" of what these emails typically look like when they're completed if needed; I just want to know how feasible it is.

Thanks! :hi:

[JP2112]

Is there a method to go through an archived PST file, look in a folder in that PST file, and extract all the data from each message?

Yes, yes and yes. A .pst file is just a message store, you would iterate it the same way you would iterate any mailbox (assuming it is added to your profile). The exact method depends on your Outlook version.

Ex:


Sub TestLoop()
Dim inboxFolder As Outlook.MAPIFolder
Dim i As Long
Set inboxFolder = Session.GetDefaultFolder(olFolderInbox)

For i = 1 To inboxFolder.Items.Count
MsgBox inboxFolder.Items.Item(i).Subject
Next i
End Sub


You would use a method like this to iterate through the folder, collect information about each email and write it to a range in Excel: http://www.jpsoftwaretech.com/export-outlook-tasks-to-excel/

[paradox34690]

JP, You said the exact method depends on which version of Outlook being used. My corporate environment is using Office 2010 with an Exchange backend.

Some of the folders I'll be working with are (as I said) PST files; other messages that I will need to process are messages of another user (shared email account for us managers).

Will working with an exhange environment or opening another users messages make things more difficult?

Secondly, is it possible to have the macro coded in such a way so that it knows exactly where it's looking for?

I haven't had a chance to get started on this yet; was going to today, but lack-o-sleep is kicking my butt....

[JP2112]

Will working with an exhange environment or opening another users messages make things more difficult?

As long as you have delegate permission on those users, it should be simple.


Secondly, is it possible to have the macro coded in such a way so that it knows exactly where it's looking for?

You can hardcode the path so it looks in only one place, if you know the messages are all in one folder.

[paradox34690]

I do, and I do. :D Thank you so much for your pointers! I'm probably going to get started on this either tonight or at some point in time over the weekend (glutton for punishment/workaholic here)

Thanks again!