Extracting all mail addresses from all folders

Status
Not open for further replies.

ofw62

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Up front: I know how to extract email addresses from 1/one folder using File->Open->Import/Export.
This is limited to 1 folder only, but, AFAIK it does not include any subfolders, hence I should do the same action for each and every folder and each and every account, which is quite a bit of a workload.

Are there any easier ways?

Thanks.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
You'd need to use VBA to export all subfolders at once. I have this macro but haven't yet added a version that walks all of the folders. As written, this macro adds the next export to the same sheet as the first - this could be changed if you wanted 1 sheet per folder. This handles multiline fields properly.
Macro to Export Outlook Fields to Excel

Another options which can be a tad faster than going through the menu (and you can add to one sheet or make new sheets), is setting a list view with all of the fields you want to export then using select all, copy and paste into Excel. This works great on single line fields, does not work as well with body or multiline address fields.
The No-Export way to use Outlook data in Excel

if it's something you do infrequently, time-wise, copy and paste would be the best use of your time - make a custom view with the fields you need and save it then apply to all folders. If you need to do it often, taking the time to perfect a macro can save time in the long run.
 

ofw62

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Many thanks Diane.
Many of the mails include a list of email addresses (not hidden, not in a BCC field, or something). The email addresses show up in the 'To:' field

Basically it is just a 'one-off' session, so to say.

As for VBA - I am not familiar with that, to be honest. At best I may copy-paste VBA code from Internet for some-Excel-macro, but that is about it.

I just enabled 'Developer' tab in Outlook, clicked on VBA button and Insert - obviously it is all nice and clean.
Am using Office Home & Business 2016 for PC with latest updates.

Copy-pasted the code from:
https://www.slipstick.com/macros/CopyToExcel-selectedfolder.txt

save, file exit VBA and gave it a try.

It looks nice, really.

I am afraid there are one or two drawbacks though.

Probably the easiest one to solve may be the date-time format, which is defaulted to US whereas in Outlook it is EUR style.
Outlook: dd-mm-yyyy
Macro: mm-dd-yyyy

a more complex issue is that the macro pulls out the addresses in the 'To:' field alright, but often the name of a person, not the email address, which is behind it.
In the to-field it may say: Diane Poremsky, but not the actual email address under the contact card, the address with '@' in it, so to say.

Maybe this plainly isn't possible?

Oh, I have not yet tried your other solutions.
 

ofw62

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Later:
I believe also the default solution, i.e. File->Open->Import/Export, does not offer to export the real email address, instead it also export the 'alias' only, i.e. the name of the person, not the email address.
Regretfully so.
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Later:
I believe also the default solution, i.e. File->Open->Import/Export, does not offer to export the real email address, instead it also export the 'alias' only, i.e. the name of the person, not the email address.
Regretfully so.
Correct - it gets the display name, which may or may not include the email address.

The instructions at How to display the sender's email address in Outlook will show how to add the email address field to the view (for senders) and i have macros that add recipients addresses (as one long string, if there are multiple recipients) - with the fields in the view, copy and paste will work.


In the macro, the date is grabbed with strColF = olItem.ReceivedTime - its not formatted, so changing the Excel format should work. If not, you can change the format in the code -
strColF = format(olItem.ReceivedTime, "dd/mm/yyyy")

Sender address is this field:
strColB = olItem.SenderEmailAddress

Recipient addresses need a bit more code - you need to use the recipient collection to grab them.

Dim Recipients As Outlook.Recipients
Set Recipients = olItem.Recipients
For i = Recipients.count To 1 Step -1
recip$ = Recipients.item(i).Address
recipadd = recip$ & ";" & recipadd
Next i
The put the string recipadd in a cell.
 

ofw62

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Hi Diane,
Sorry for the confusion, but it involves the receivers email addresses, not the senders.
Specifically I often receive mails with multiple names in the 'To:' field.
I want to extract those.

I am afraid I would not know where to put the above code in the script.
Using Outlook 2016/POP3.

Maybe I need to remove line 54 uptil and incl line 59 (exchange) and put the above instead..?

No idea.

Thanks
=
 

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
you definitely don't need the Exchange stuff- that gets the sender's smtp when both the sender and recipient are on exchange.

use the code in the text file and replace the exchange block with

Code:
Dim Recipients As Outlook.Recipients
Dim i As Long
Dim RecipAdd As String
Dim recip 'As Recipient
RecipAdd = ""
Set Recipients = olItem.Recipients
For i = Recipients.Count To 1 Step -1
recip = Recipients.Item(i).Address
Debug.Print recip
RecipAdd = recip & ";" & RecipAdd
Next i

then add
xlSheet.Range("g" & rCount) = RecipAdd
after the other xlsheet ranges.
 

ofw62

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Thank you.
Now I get a warning, security, I tried various options regarding macro security settings.

=
Option Explicit
Sub CopyToExcel()
Dim xlApp As Object
Dim xlWB As Object
Dim xlSheet As Object
Dim rCount As Long
Dim bXStarted As Boolean
Dim enviro As String
Dim strPath As String

Dim currentExplorer As Explorer
Dim Selection As Selection
Dim olItem As Outlook.MailItem
Dim obj As Object
Dim strColB, strColC, strColD, strColE, strColF As String

' Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
strPath = enviro & "\Documents\test.xlsx"
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
Application.StatusBar = "Please wait while Excel source is opened ... "
Set xlApp = CreateObject("Excel.Application")
bXStarted = True
End If
On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Sheet1")
' Process the message record

On Error Resume Next
'Find the next empty line of the worksheet
rCount = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
'needed for Exchange 2016. Remove if causing blank lines.
rCount = rCount + 1

' get the values from outlook
Set currentExplorer = Application.ActiveExplorer
Set Selection = currentExplorer.Selection
For Each obj In Selection

Set olItem = obj

'collect the fields
strColB = olItem.SenderName
strColC = olItem.SenderEmailAddress
strColD = olItem.Body
strColE = olItem.To
strColF = Format(olItem.ReceivedTime, "dd-mm-yyyy")

Dim Recipients As Outlook.Recipients
Dim i As Long
Dim RecipAdd As String
Dim recip 'As Recipient
RecipAdd = ""
Set Recipients = olItem.Recipients
For i = Recipients.Count To 1 Step -1
recip = Recipients.Item(i).Address
Debug.Print recip
RecipAdd = recip & ";" & RecipAdd
Next i

If InStr(1, strColC, "/") > 0 Then
' if exchange, get smtp address
Select Case recip.AddressEntry.AddressEntryUserType
Case OlAddressEntryUserType.olExchangeUserAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColC = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olOutlookContactAddressEntry
Set olEU = recip.AddressEntry.GetExchangeUser
If Not (olEU Is Nothing) Then
strColC = olEU.PrimarySmtpAddress
End If
Case OlAddressEntryUserType.olExchangeDistributionListAddressEntry
Set oEDL = recip.AddressEntry.GetExchangeDistributionList
If Not (oEDL Is Nothing) Then
strColC = olEU.PrimarySmtpAddress
End If
End Select
End If
' End Exchange section

'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
xlSheet.Range("g" & rCount) = RecipAdd

'Next row
rCount = rCount + 1

Next

xlWB.Close 1
If bXStarted Then
xlApp.Quit
End If

Set olItem = Nothing
Set obj = Nothing
Set currentExplorer = Nothing
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
End Sub

=
 

Attachments

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
you need to go into outlook's file, options, trust center and enable macros (last option) then restart outlook. After it is fully tested, you can sign it with a digital signature and change the setting to notify.

you don't want to sign it until you are finished testing and editing - otherwise you need to remove the signature and re-sign it after every change.

How to use Outlook's VBA Editor
 

ofw62

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
Oops .., I did set the stuff in trust center but ..
did not restart Outlook.

I am encountering an error though, sorry.

Any suggestions?

=
p.s. if you want me to post the code, let me know.
 

Attachments

Diane Poremsky

Senior Member
Outlook version
Outlook 2016 32 bit
Email Account
Office 365 Exchange
That message says the workbook doesn't exist at that path. This macro doesn't create a new workbook, you need to create it before you start.
 
Status
Not open for further replies.
Thread starter Similar threads Forum Replies Date
D Outlook VBA error extracting property data from GetRules collection Outlook VBA and Custom Forms 10
B Extracting email addresses from a folder - how to also get the name of the person the address is for? Using Outlook 5
M Extracting cell data from table Outlook VBA and Custom Forms 2
A Extracting only one recipient from Msgitem.To Outlook VBA and Custom Forms 7
Z Default VBA code for extracting data from email (Outlook) to Excel? Outlook VBA and Custom Forms 1
V Extracting user-defined details from a public folder Outlook VBA and Custom Forms 2
M Oh please read---> Extracting email web leads on to excel spread sheet Using Outlook 3
J Need Help Extracting Email Content into Excel Specifically the Attachment Name Using Outlook 3
D Extracting Location info from All Day Event in mailboxes into webpage Exchange Server Administration 3
K Help with extracting data from a windows mobile device Using Outlook 5
E Extracting CC Email Address Using VBA Outlook VBA and Custom Forms 2
K Extracting Calendar information from a OST file? Exchange Server Administration 5
ThinkToday Calculate reply time of outlook mail Using Outlook 1
B Outlook 2010 Opening Mail Attachment Using Outlook 2
e_a_g_l_e_p_i Is it possible it set the fonts used to read incoming mail Using Outlook 11
D Mail in Sent Items Gone Using Outlook 13
M Managing Gmail "All Mail" sync issues with IMAP Using Outlook 1
GregS Outlook 2016 Sent Mail absent from Sent Mail or Sent Items Folders Using Outlook 8
GregS Why is Outlook 2016 mail in Outlook.com? Using Outlook 1
S Outlook not receiving mail Using Outlook 2
Commodore Outlook (2013) issues with icloud.com mail Using Outlook 0
GregS Outlook 2016 iPhone won't download Outlook 2016 Mail. Using Outlook 0
A Arthur needs help with 2007 Outlook e-mail Using Outlook.com accounts in Outlook 3
oliv- How to distinguish whether mail is incoming or outgoing? Outlook VBA and Custom Forms 2
W Receiving mail for account that has been deleted Using Outlook 2
H In outlook 365 POP mail Inbox, email takes a long time or never arrives. Using Outlook 1
Z Add text to auto-forwarded e-mail Outlook VBA and Custom Forms 4
K HOW TO FIX AOL MAIL ISSUES? Using Outlook 0
O How to prevent CC from showing when creating a new mail? Using Outlook 1
I Junk Mail and PersonMetadata Using Outlook 2
T 3 months of archived e-mail is missing Using Outlook 1
B Outlook - Mail from safe senders list being sent to Spam Folder Using Outlook 0
G Detect Google Drive and OneDrive links in incoming mail Outlook VBA and Custom Forms 3
M Sent mail not showing in Sent Items folder; but they can be found with Search Using Outlook 3
H Outlook 2016 sent over 30 copies of an e-mail with attachments Using Outlook 1
T Missing text in e-mail message after "(Please keep confidential)" Using Outlook 3
Victor_50 Outlook 2013 Custom Contact Form starts with "E-mail 2" Outlook VBA and Custom Forms 2
E Mail sorting view issue Using Outlook 1
D after delete mail, open the next one Outlook VBA and Custom Forms 0
RBLampert Assigning a newly (re)created e-mail account to a specific .pst data file Using Outlook 2
B Clear Offline Items (Mail Folder) via VBA Outlook VBA and Custom Forms 1
M Moving mail to another folder is much slower than before (Office365) Using Outlook 0
A Outlook macro to create search folder with mail categories as criteria Outlook VBA and Custom Forms 3
N VBA Script to Open highlighted e-mail and Edit Message Outlook VBA and Custom Forms 5
M Auto-export mail to Excel Outlook VBA and Custom Forms 2
M Sent mail not showing in my sent folder Using Outlook 9
B iCloud Option to sync contacts, mail, calendar and tasks Using Outlook 5
O Using .OST and .PST mail thru different providers Using Outlook 5
CWM030 Connected accounts mail moved to trash? Exchange Server Administration 16
N Tracking Mail items being moved to folders Outlook VBA and Custom Forms 5
Similar threads


















































Top