Outlook output to array -> Excel & re-format

[Dr. Demento]

In using an awesome sub by Greg Thatcher (found here), it does a great job at extracting the information, but the output leaves much to be desired.

I'm wondering if someone could help in two ways:
1) point me the the direction where I could write the output first to an array (for speed/efficiency sake given that it extracts info about every email you own) and then to Excel; the more generic, the better as I'm wanting to alter multiple bits of his code to output to Excel rather than an Outlook email message.
2) the current format has information spread out all over the place. I would like the output to be set up like a table (with each row being a different email/item and each column being consistent - Subject, Last Modification Time, Message Class, etc). Again, a point in the right direction would be awesome.

Thanks much y'all.

I originally posted this thread here.

 

[Diane Poremsky]

It looks like he is using one line per field - not sure it would be any better putting it in table format unless you use HTML.
To format it in tables, you'd need to use something like this and write it to an html message.
Report = Report & "<table><tr><th>Subject: </th><th>MessageClass:</th></tr>"
Report = Report & "<tr><td>" & rowValues(1) & "</td><td>"& rowValues(2) & &"</td></tr>"

After all the records are written, use report = report & "</table>"


But if you want it in Excel, I'd write it there directly. I have a macro here -http://www.slipstick.com/developer/code-samples/macro-export-outlook-fields-excel/ - that works on the selected messages and puts them in one message per line recordset format. It writes one record at a time which might not be any faster.

the macro at http://www.slipstick.com/developer/code-samples/working-items-folder-selected-items/ shows how to change from working with selected message to looping through all messages in the folder.

Use the code from Greg's that walks the folders and add the fields that you need:

<snipped>

Dim SubFolders As Outlook.Folders
Dim SubFolder As Outlook.Folder

Set SubFolders = CurrentFolder.Folders
For Each SubFolder In SubFolders

' loop through the messages writing to excel one line at a time
Set objItems = SubFolder.Items

For Each olItem In objItems

strColB = olItem.SenderName
strColC = olItem.SenderEmailAddress
strColD = olItem.Body
strColE = olItem.To
strColF = olItem.ReceivedTime


'write them in the excel sheet
xlSheet.Range("B" & rCount) = strColB
xlSheet.Range("c" & rCount) = strColC
xlSheet.Range("d" & rCount) = strColD
xlSheet.Range("e" & rCount) = strColE
xlSheet.Range("f" & rCount) = strColF
'Next row
rCount = rCount + 1

next ' message

Next SubFolder


<snipped>

 

[Dr. Demento]

Thanks so much, Diane! I will definitely look at those articles.