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.
Similar threads
Thread starter Title Forum Replies Date
e_a_g_l_e_p_i Need clarification on 2-Step Verification for Gmail using Outlook 2021 Using Outlook 4
L Opening People Outlook 2021 Using Outlook 0
e_a_g_l_e_p_i Outlook 2021 not letting me setup my Gmail using pop Using Outlook 1
Geldner Problem submitting SPAM using Outlook VBA Form Outlook VBA and Custom Forms 2
P VBA to add email address to Outlook 365 rule Outlook VBA and Custom Forms 0
M Outlook 2016 outlook vba to look into shared mailbox Outlook VBA and Custom Forms 0
P Can no longer sync Outlook with iPhone calendar after iPhone update to 17.1.1 Using Outlook 2
O Outlook - Switch from Exchange to IMAP Using Outlook 0
e_a_g_l_e_p_i Is it possible to have a reminder in Outlook 2021 for every 90 days Using Outlook 3
farrissf Outlook 2016 Optimizing Email Searches in Outlook 2016: Seeking Insights on Quick Search vs Advanced Search Features Using Outlook 0
C Advanced search terms for "Outlook Data File" Using Outlook 1
N Reply to Outlook messages by moving messages to a specific Outlook folder Outlook VBA and Custom Forms 1
O How to find out the domain and server settings that my Outlook is using? Using Outlook 2
A Outlook 365 (OutLook For Mac)Move "On My Computer" Folder Items From Old To New Mac Computer Using Outlook 3
H Integrating Alexa & Outlook Pro 2021 Using Outlook 2
Z Automatically adjust Outlook Reading Pane from bottom to right depending on portrait or landscape window Using Outlook 1
Rupert Dragwater Background colors not saving in Outlook 365 Using Outlook 15
petunia Outlook tasks module sunsetting? Exchange Server Administration 3
G Save emails as msg file from Outlook Web AddIn (Office JS) Outlook VBA and Custom Forms 0
D Outlook VBA forward the selected email to the original sender’s email ID (including the email used in TO, CC Field) from the email chain Outlook VBA and Custom Forms 3
U Outlook 2021 not showing contact cards in Searches Using Outlook 1
C Outlook - Macro to block senders domain - Macro Fix Outlook VBA and Custom Forms 2
H Outlook 365 O365 outlook calendar item editing Using Outlook 1
J Outlook 365 html inline images Using Outlook 0
Rupert Dragwater How to get Outlook 365 to open from websites Using Outlook 5
S Why do I have to close and reopen Outlook for macros to work? Outlook VBA and Custom Forms 2
J Outlook 2021 ScanPST errors (yet again ... sorry): repair button missing Outlook 2021 Using Outlook 0
HarvMan Outlook 365 - Rule to Move an Incoming Message to Another Folder Using Outlook 4
K Moved pst to new computer, now Gmail not coming into Outlook Using Outlook 7
S Email Macros to go to a SHARED Outlook mailbox Draft folder...NOT my personal Outlook Draft folder Using Outlook 2
F Running Scripts in Outlook 2021 Using Outlook 0
Nufc1980 Outlook "Please treat this as private label" auto added to some emails - Help. Using Outlook 3
S Outlook 2019 Custom outlook Add-in using Visual Studio Outlook VBA and Custom Forms 0
V Outlook macros no longer run until VB editor is opened Outlook VBA and Custom Forms 0
R Outlook 365 How to integrate a third-party app with Outlook to track email and sms? Using Outlook 2
e_a_g_l_e_p_i I can't believe what I am seeing when trying to install Outlook 2021 Using Outlook 9
Kika Melo Outlook Calendar deleted appointments not in Deleted Items folder Using Outlook 3
P How to get a QR code for automatic signin with Outlook for iOS Using Outlook 5
J Migrating Outlook Using Outlook 1
Retired Geek Outlook for the MAC with Yahoo accounts now very broken Using Outlook 9
S Outlook 2002- "Send" button has disappeared. Help please. Using Outlook 1
L How Stop Outlook Nag Messages Using Outlook 1
TomHuckstep Remove Send/Receive All Folders (IMAP/POP) button from Outlook 365 Ribbon Using Outlook 2
L I Cannot Sign Into My Outlook Account? Outlook VBA and Custom Forms 0
icacream Outlook 2021 - Google calendar in the peek Using Outlook 0
e_a_g_l_e_p_i Question about installing my Gmail account on my iPhone but still getting messages downloaded to my desktop Outlook. Using Outlook 3
F Want to add second email to Outlook for business use Using Outlook 4
kburrows Outlook Email Body Text Disappears/Overlaps, Folders Switch Around when You Hover, Excel Opens Randomly and Runs in the Background - Profile Corrupt? Using Outlook 0
M using excel to sort outlook appointment items Outlook VBA and Custom Forms 4
e_a_g_l_e_p_i MY Outlook 2021 changed the format of the shortcuts for mail, calendar etc. Using Outlook 10

Similar threads

Top