Extract mail details from Outlook 2000+ to Excel

[PangoKen]

Hi

I have created a report that will copy all email details in a outlook
folder (receivedtime, LastModificationTime, sender name etc.) to
excel. Currently the code asks me to select a folder to report on, but
I would like to extend this to include sub-folders as well. Can anyone
tell me an easy way to specify that sub-folders are included (and that
sub-folders named 'Completed' are excluded)?

The code I'm using to select the folder and work with the info is:

' 1) ask me to select a folder containing received mail
Set ns = Application.Session
Set fld = ns.PickFolder
If Not fld Is Nothing Then

'a) count the mail items in the folder
intTotalItems = fld.Items.Count

'3) set the location of the storage file and
' create the Excel worksheet
' **code removed**

'b) identify which folder we are working with
ws.Cells(1, 1) = "Items in folders: " & fld

'4) loop through all mails in the selected Outlook folder

intRow = 4
For Each itm In fld.Items
DoEvents
If itm.Class = olMail Then

' 5 )write all the senders email adr, senders name, emails
' subject received time into this single created file
' fill in the rows with the message data
Set msg = itm
' this next line doesn't work with outlook 2000 but
' does with outlook 2003
' ws.Cells(intRow, 1) = msg.SenderEmailAddress
ws.Cells(intRow, 2) = msg.SenderName
ws.Cells(intRow, 3) = msg.Subject
ws.Cells(intRow, 5) = msg.ReceivedTime
ws.Cells(intRow, 6) = msg.LastModificationTime
etc.

BTW this message was also posted in
microsoft.public.office.developer.outlook.vba but due to the higher
number of subscribers I've posted it here as well...

thank you for your help

Lee

 

[Eric Legault [MVP - Outlook]]

There's no real "easy" way to specifiy sub-folders. If you know the starting
folder, you can then loop through MyStartingFolder.Folders to recursively get
each level. Within each level you'd then need to evaluate the folder's name
to determine if you want to process it or not. Essentially you'd need
something like:

Sub MyProc()
'Get starting folder
Dim objInbox As Outlook.MAPIFolder
Dim objNS As Outlook.NameSpace

Set objNS = Application.GetNamespace("MAPI")
Set objInbox = objNS.GetDefaultFolder(olFolderInbox)

MyFolderEnumerator objInbox

Set objInbox = Nothing
Set objNS = Nothing
End Sub

Sub MyFolderEnumerator(CurrentFolder As Outlook.MAPIFolder)
Dim objSubFolder As Outlook.MAPIFolder

For Each objSubFolder In CurrentFolder.Folders
Select Case objSubFolder.Name
Case "FolderA", "FolderB"
'Skip
Case Else
'Process
MyProcessingProc objSubFolder
'You also need to process all subfolders (if any) in the
current sub-folder by
'calling this procedure recursively
MyFolderEnumerator objSubFolder
End Select
Next

Set objSubFolder = Nothing
End Sub

Sub MyProcessingProc(ProcessThisFolder As Outlook.MAPIFolder)
Debug.Print ProcessThisFolder.FolderPath
End Sub

What I can't figure out of the top of my head is how to stop processing at a
certain level in the hierarchy (one level would be easy I believe). Anyway,
this should give you some guidance.