How to extract mail items from multiple folders and shared mailboxes?

Status
Not open for further replies.

sn152

Member
Outlook version
Email Account
Exchange Server
Hi All,

I have the below code which will extract the mail items from an outlook folder to an excel file. When I run this macro, it will ask me to pick a folder.
Instead of that, is there a way to extract mail items from multiple folders and shared mailboxes? Please help me with this.

Code:
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 fld As Outlook.MAPIFolder
 Dim nms As Outlook.NameSpace
 Dim currentExplorer As Explorer
 Dim Selection As Selection
 Dim olItem As Outlook.MailItem
 Dim obj As Object
 Dim strColB, strColC, strColD, strColE, strColF, strColG, strColH, strColI As String
               
Set nms = Application.GetNamespace("MAPI")
Set fld = nms.PickFolder

If fld Is Nothing Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
ElseIf fld.DefaultItemType <> olMailItem Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
ElseIf fld.Items.Count = 0 Then
MsgBox "There are no mail messages to export", vbOKOnly, "Error"
Exit Sub
End If

' Get Excel set up
enviro = CStr(Environ("USERPROFILE"))
'the path of the workbook
 strPath = "C:\Users\ABC\Desktop\Book1.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 fld.Items

    Set olItem = obj
   
 'collect the fields
   
    strColB = olItem.SenderName
    strColC = olItem.SenderEmailAddress
    strColD = olItem.Subject
    strColE = olItem.To
    strColF = olItem.ReceivedTime
    strColG = olItem.Categories
    strColH = olItem.FlagRequest
    strColI = olItem.Parent

' Get the Exchange address
' if not using Exchange, this block can be removed
 Dim olEU As Outlook.ExchangeUser
 Dim oEDL As Outlook.ExchangeDistributionList
 Dim recip As Outlook.Recipient
 Set recip = Application.Session.CreateRecipient(strColC)

 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) = strColG
  xlSheet.Range("h" & rCount) = strColH
  xlSheet.Range("i" & rCount) = strColI
 
'Next row
  rCount = rCount + 1

 Next

     xlWB.Close 1
     If bXStarted Then
         xlApp.Quit
     End If
     Set nms = Nothing
     Set olItem = Nothing
     Set obj = Nothing
     Set currentExplorer = Nothing
     Set xlApp = Nothing
     Set xlWB = Nothing
     Set xlSheet = Nothing
 End Sub
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
D ISOmacro to extract active mail senders name and email, CC, Subject line, and filename of attachments and import them into premade excel spread sheet Outlook VBA and Custom Forms 2
T vba extract data from msg file as attachment file of mail message Outlook VBA and Custom Forms 1
C Wishlist Extract or scan new email addresses from out of office replies. Leads from OOO replies Using Outlook 1
M Extract "Date sent" from emails (saved to folder using drag and drop) Outlook VBA and Custom Forms 1
S Unable to extract text from an Outlook email message Using Outlook 2
S Macro to extract and modify links from emails Outlook VBA and Custom Forms 3
S Macro to extract email addresses of recipients in current drafted email and put into clipboard Outlook VBA and Custom Forms 2
C Macro to extract sender name & subject line of incoming emails to single txt file Outlook VBA and Custom Forms 3
N Extract Outlook emails to excel Outlook VBA and Custom Forms 2
M Extract all links from Outlook email, send to Excel Using Outlook 2
T Extract Data From Outlook Tasks Using Outlook 0
T Extract Data From Outlook Tasks Using Outlook 0
V extract users of a particular department Outlook VBA and Custom Forms 1
J Outlook 2013 Extract Flag Completed dates to Excel Macro Outlook VBA and Custom Forms 16
K Extract email address from body and auto-reply outlook Using Outlook 1
R Trying to extract information between two symbols from outlook subject Using Outlook 2
K Extract email to excel from a specific sender Outlook VBA and Custom Forms 3
O VBA to extract email (fields and body) to Excel Outlook VBA and Custom Forms 14
P Recover / Extract Rules from standalone PST file creating RWZ file Using Outlook 2
B Extract Dates for Appointment Item in Body of email Outlook VBA and Custom Forms 10
D Need to extract a line from a word attachment, and add it to the subject line Outlook VBA and Custom Forms 3
E Extract excel files from outlook Outlook VBA and Custom Forms 2
K extract certain text from an Outlook Email Message Outlook VBA and Custom Forms 2
D VBA Script to extract text matching specific criteria Outlook VBA and Custom Forms 1
M Extract text in existing message body for use in newmail items Using Outlook 17
M HELP--Extract Data from 2003 outlook transfer to excel spreadsheet Using Outlook 1
M VBA Code to extract data from an Outlook Form Using Outlook 0
M Extract attachments with a script Using Outlook 0
M HELP - Can't open outlook... How can I extract my Emails that I had in folders Using Outlook 3
H Extract emails from Outlokk 2007 email body Using Outlook 0
K Extract Global Address List Using Outlook 1
R Saving Outlook Email As Text File Extract Outlook VBA and Custom Forms 2
N Programming to extract automatically extract attachments Outlook VBA and Custom Forms 3
S How to extract outlook calendar data. Outlook VBA and Custom Forms 3
? outlook attachment Extract File ??? Outlook VBA and Custom Forms 1
N How to extract date and time stamp from messsages Outlook VBA and Custom Forms 6
V Extract Subject,Sent From, Message from mailbox to Excel Outlook VBA and Custom Forms 5
S Automatically extract attachments? Outlook VBA and Custom Forms 1
I How to extract email addresses from TO or CC line of a particular email Outlook VBA and Custom Forms 2
X Run macro automatically when a mail appears in the sent folder Using Outlook 5
K How can I delete an e-mail from Outlook Using Outlook 1
L Help: set flag for sent mail to check if received an answer Outlook VBA and Custom Forms 2
A Macro Mail Alert Using Outlook 4
e_a_g_l_e_p_i MY Outlook 2021 changed the format of the shortcuts for mail, calendar etc. Using Outlook 10
Z Outlook 365 Automatically assign categories to incoming mail in a shared folder Round Robin Outlook VBA and Custom Forms 1
W Outlook 365 I am getting the "Either there is no default mail client" error when I try to send an email on excel Office 365 Using Outlook 1
D Gmail mail is being delivered to a different email inbox in Outlook App 2021 Using Outlook 2
P What is your strategy for dealing with SPAM and Junk Mail? Using Outlook 1
C Code to move mail with certain attachment name? Does Not work Outlook VBA and Custom Forms 3
T 1:1 Datatransfer from incoming mail body to customs form body Outlook VBA and Custom Forms 0

Similar threads

Back
Top