Outlook output to array -> Excel & re-format

Status
Not open for further replies.

Dr. Demento

Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
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

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
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

Member
Outlook version
Outlook 2010 32 bit
Email Account
IMAP
Thanks so much, Diane! I will definitely look at those articles.
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
N User is trying to COPY and PASTE text document in outlook. But the output of copied text is an image. Using Outlook 5
J Outlook 2010 Outlook/Win7 Client logging Event ID 36887 Using Outlook 0
R How to get the Items object of the default mailbox of a specific account in a multiple account Outlook? Outlook VBA and Custom Forms 0
T Why does outlook 2010 convert only some forum notifications to plain text? Using Outlook 0
M In Outlook Calendar remove the buttons: 'Today' and '<' (Back a day) and '>' (Forward a day) that are below the Ribbon and above the calendar display. Using Outlook 0
J Moved many emails to Outlook external folder, need to delete on Gmail server Using Outlook 13
P Scheduling a meeting in Outlook Outlook Wishlist 4
I Error saving screenshots in a custom form in outlook 2016, outlook 365 - ok in outlook 2013, outlook 2010 Outlook VBA and Custom Forms 5
R Outlook 2019 Using Outlook 0
N Outlook Has Become Sluggish Using Outlook 14
HarvMan Outlook 365 Calendar Using Outlook 12
A calendar invites sent from Microsoft Outlook to Mac Outlook is not working Calendar Printing Assistant 2
L Outlook 2016 Contacts missing in Outlook 365 Using Outlook 1
R seperate read layout to design in outlook 2016..Help!! Outlook VBA and Custom Forms 3
A Outlook replies not using "delivered to" address in From Using Outlook 1
GregS Why is Outlook 2016 mail in Outlook.com? Using Outlook 1
G Recurring tasks break links Outlook 2016 Using Outlook 5
L Outlook saved email templates Using Outlook 1
P Outlook for Mac - emails are not sent out beginning last week Using Outlook 1
V Gmail in Outlook Using Outlook 0
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
S Verwendung von Outlook 2007 Using Outlook 0
T "Words In Recipient's Address" Rule Causes Outlook To Stop Processing Rules Using Outlook 3
J Outlook 2013 Change color of text in data fields of contacts in Outlook 2013? Using Outlook 3
T Pictures degrade each time an Outlook item is edited and re-saved Using Outlook 1
Y Outlook 2013 Stop Outlook from automatically assigning categories to Tasks Using Outlook 0
A How to get body of all emails in outlook 2016 to view in blue color Using Outlook 1
J Edit auto-complete list in Outlook 2016+/365? Using Outlook 0
O Export Outlook calendar appointments by filters and date range Outlook VBA and Custom Forms 1
S Outlook not receiving mail Using Outlook 1
GregS Outlook 365 blocked by Google popup Using Outlook 2
T upgrade from outlook.com to Outlook Using Outlook 2
R Outlook Autoforward rule do not work for NDR messages Using Outlook 1
Commodore Outlook (2013) issues with icloud.com mail Using Outlook 0
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
D Add date next to day name in Outlook Today calendar view Using Outlook 1
D iCloud Add-in not working in Outlook 2013 and Outlook 2016 After Windows Upgrade & iCloud Upgrade Using Outlook 2
GregS Outlook 2016 iPhone won't download Outlook 2016 Mail. Using Outlook 0
E How to display "Change Folder" in Change Default Email Delivery Location in Exchange Outlook 2016 Using Outlook 1
M Extract all links from Outlook email, send to Excel Using Outlook 2
O Synchronising contacts Outlook 365 with Google v.v. Using Outlook 4
B Outlook 2003 email sending & receiving suddenly stopped working Using Outlook 3
C Put Day of Year into Outlook Calendar Outlook VBA and Custom Forms 2
David in Mississippi New (Feb 2020) install of Outlook 365 - Cannot specify PST for different accounts Using Outlook 0
M Outlook 2010 Problem with OutLook 2010 32 bit, after Windows Auto Update Using Outlook 3
O Email not leaving Outbox when using Excel VBA to sync Outlook account Outlook VBA and Custom Forms 4
B Outlook and iPhone Sync Using Outlook 3
A How to open a specific link automatically with outlook 2016 Outlook VBA and Custom Forms 5
H Upon opening Outlook, make my popmail inbox open instead of outlook.com inbox Using Outlook 1
J can't sync outlook calendar contacts with iphone ipad Using Outlook 4
Similar threads


















































Top