Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-01-2020, 12:00 AM
TimTDP TimTDP is offline Extract sender email address in bulk Windows 7 64bit Extract sender email address in bulk Office 2010 32bit
Advanced Beginner
Extract sender email address in bulk
 
Join Date: Apr 2012
Posts: 42
TimTDP is on a distinguished road
Default Extract sender email address in bulk

I have to return a work computer
Before I do, I want to save the sender's email address of emails in my inbox
How can I do this in bulk (automated, or as near as possible. Not one by one! )



Any suggestions on how to do this?

Thanks
Reply With Quote
  #2  
Old 04-01-2020, 02:27 AM
gmayor's Avatar
gmayor gmayor is offline Extract sender email address in bulk Windows 10 Extract sender email address in bulk Office 2016
Expert
 
Join Date: Aug 2014
Posts: 4,101
gmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud ofgmayor has much to be proud of
Default

You can do it with Outlook macros. Be warned that it will take a while to run if you have a lot of emails and/or sub folders. It has now been running on my own Outlook inbox with its many sub folders for around 15 minutes and has written thousands of addresses and is still adding addresses to the worksheet.

The following will write the sender name and sender e-mail address to an existing Excel workbook named in the macro, which must be pre-configured to have two columns each with a header row i.e. Name and Address.

Run the macro. Select Inbox and go for lunch.

Normally I would use a progress indicator for something like this but Outlook doesn't make it easy to present such an indicator in a forum post, so I would suggest having the worksheet opened in Excel so you can check on progress. I have trapped the obvious errors, but it is possible in thousands of e-mails to find some name/address with an obscure format that the process will baulk at.

There will inevitably be duplicates. You can use Excel to eliminate those later, or the process would be even slower.

Code:
Option Explicit

Sub GetAddresses()
'Graham Mayor - https://www.gmayor.com - Last updated - 01 Apr 2020 
Dim cFolders As Collection
Dim olFolder As Outlook.Folder
Dim SubFolder As Folder
Dim olNS As Outlook.NameSpace
    Set cFolders = New Collection
    Set olNS = GetNamespace("MAPI")
    cFolders.Add olNS.PickFolder
    Do While cFolders.Count > 0
        Set olFolder = cFolders(1)
        cFolders.Remove 1
        ProcessFolder olFolder
        For Each SubFolder In olFolder.folders
            cFolders.Add SubFolder
            DoEvents
        Next SubFolder
    Loop
lbl_Exit:
    Set olFolder = Nothing
    Set SubFolder = Nothing
    Set cFolders = Nothing
    Set olNS = Nothing
    Exit Sub
err_Handler:
    GoTo lbl_Exit
End Sub

Private Sub ProcessFolder(iFolder As Folder)
'Graham Mayor - https://www.gmayor.com - Last updated - 01 Apr 2020 
Const strWB As String = "D:\Path\WorkbookName.xlsx"    'the path to the workbook (which must exist)
Const strSheet As String = "Sheet1"
Dim i As Long
Dim olItem As Object
Dim strSender As String
    If iFolder.items.Count > 0 Then
        For i = 1 To iFolder.items.Count
            Set olItem = iFolder.items(i)
            If TypeName(olItem) = "MailItem" Then
                strSender = Replace(olItem.sender, "'", "")
                strSender = Replace(strSender, Chr(34), "")
                WriteToWorksheet strWB, strsheet, strSender & "', '" & olItem.SenderEmailAddress
                DoEvents
            End If
        Next i
    End If
lbl_Exit:
    Set olItem = Nothing
    Exit Sub
End Sub

Public Function WriteToWorksheet(strWorkbook As String, _
                                 strRange As String, _
                                 strValues As String)
Dim ConnectionString As String
Dim strSQL As String
Dim CN As Object
    ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                       "Data Source=" & strWorkbook & ";" & _
                       "Extended Properties=""Excel 12.0 Xml;HDR=YES;"";"
    strSQL = "INSERT INTO [" & strRange & "$] VALUES('" & strValues & "')"
    Set CN = CreateObject("ADODB.Connection")
    Call CN.Open(ConnectionString)
    Call CN.Execute(strSQL, , 1 Or 128)
    CN.Close
    Set CN = Nothing
lbl_Exit:
    Exit Function
End Function
__________________
Graham Mayor - MS MVP (Word) (2002-2019)
Visit my web site for more programming tips and ready made processes www.gmayor.com
Reply With Quote
  #3  
Old 04-11-2020, 03:21 AM
TimTDP TimTDP is offline Extract sender email address in bulk Windows 7 64bit Extract sender email address in bulk Office 2010 32bit
Advanced Beginner
Extract sender email address in bulk
 
Join Date: Apr 2012
Posts: 42
TimTDP is on a distinguished road
Default

Thanks very much
Works like a charm!

Sorry for late response
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I prevent certain users from seeing email address of certain users in the recipient/sender field sevenpcb Outlook 0 08-21-2017 10:11 AM
Extract sender email address in bulk Need to extract two word domains from a list (BULK) Maxwell314 Excel 3 12-08-2014 06:17 PM
Need to extract domain names containing only specific words (MAJOR BULK) Maxwell314 Excel 4 12-08-2014 05:10 PM
Extract sender email address in bulk Moving email addresses in bulk to an address book Engywook Outlook 2 05-19-2010 05:32 PM
Extract sender email address in bulk Extract email address from field zssteen Excel 1 06-19-2009 02:32 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:50 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft