retrieving data from mails(outlook) using excel/vba

NOTE: this needs the Outlook object library referenced in VBA - tools - references - tick Outlook 9.0 (or 10.0 if you're running XP) Object Library.

OK first you need to set the main 'profile' folder that the subfolder containing these emails is in - in the below code it is set as "Personal Folders" - you need to establish the folder index.

Then do the same for the folder actually containing the emails
eg

Set myfolder2 = myfolder.Folders("Form Receipt")

where "Form receipt" is the folder name.

This is working off the assumption that Form Receipt is a folder off the main profile folder - if it's a subfolder of a subfolder then you will need to set myfolder2 as that subfolder and then introduce a myfolder3 etc... until you have the root to the folder you need.

The below code then loops through each message of myfolder2 (or 3 etc as described) and checks the subject, if the subject doesn't match that of a form submission then it jumps to the next message, else it takes the sender name and places it in column A, the subject and places it in Column B and finally the body of the email and places that in Column C.

n determines the first row in which the data will be inserted - each time you move to the next mail item n is increased by 1, so the next email details will go in row 2 etc...

This should at least get you started

Thanks a bunch

-- now at least i got a point from where to start

I can read/get your code easily -- just producing that myself without deeper klnowledge would have taken me ages. I think I can go from here.

Thanks again,

Ike

I realise this is a very old thread, but it is almost exactly what I am trying to do, and I am having problems trying to implement this code.

Perhaps this has changed with Office 2007, but my code gets stuck on:


I am trying to reference to a folder "Form Receipt" which is a subfolder to the Inbox.

When I run the code I get an error:

Run-time error '-2147352567 (80020009)'

Automation error
Exception occured.

Click to expand...

This occurs on the 3rd loop of the Do loop. Perhaps coincidentally, I have only 3 sub folders to my inbox, and Form Receipt is the 3rd folder to this.


What am I doing wrong? Or has Office changed too much?

Set myfolder = myfolders.Item
Set myfolder2 = myfolder.Folders("Form Receipt")

Delete the highlighted in red above and add these lines:

Set myfolder2 = myfolder2.Folders("Inbox")
Set myfolder3 = myfolder2.Folders("Form Receipt")

Your first folder level is "Personal Folders" your second is "Inbox" so you have to go a third level to get to "Form Receipt"

Thanks for this thread it describes what I want to do, however when I try to access a message item sendername I'm prompted to provide permission to access that field. (Excel VBA 2003). I don't want this to happen. Can I suppress it ?

I am trying to run this but getting:
Run-time error '440':
Array index out of bounds

I created a Form Receipt folder right under my Inbox (it is the only subfolder). I referenced Microsoft Outlook 15.0 Object Library.

It is getting caught at:
Do Until myfolders.Item = "Personal Folders"

What am I doing wrong?