Get Outlook Data from Chosen Folder

Earlier I had shown how to get data from Microsoft Outlook. The most frequently asked question was: how to get data from Inbox folder or how to get data from email items in a specific folder. Most people were not able to code the path to the relevant folder. This is quite easy though. Inspite of that, I created an easier method to get the data from Outlook from any folder. We’ll allow the user to select or choose the folder! Watch the video tutorial below:

Choose Folder in Outlook to get data into Excel worksheet

Here’s the complete macro code to get data from chosen Outlook folder into an Excel workbook:

Option Explicit

Sub getDataFromOutlookChoiceFolder()

‘Declare required variables
Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Long

‘Set the values of the object variables

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace(“MAPI”)
Set Folder = OutlookNamespace.pickfolder

‘Go to Tools –> References –> Activate the Microsoft Object Library

‘Check if the folder has emails else exist the procedure

If Folder.Items.Count = 0 Then
MsgBox “No emails. Exiting procedure!”
Exit Sub
End If

i = 1

‘Before getting data from Outlook delete all data from worksheet including the range names

Dim rngName As Name
Sheet1.Cells.Clear
For Each rngName In ActiveWorkbook.Names
rngName.Delete
Next

Import Data from Outlook

‘Assign the relevant ranges with relevant names and headers

Range(“A1”).Name = “email_Subject”
Range(“A1”) = “EmailSubject”
Range(“B1”).Name = “email_Date”
Range(“B1”) = “Email Date”
Range(“C1”).Name = “email_Sender”
Range(“C1”) = “Email Sender”
Range(“D1”).Name = “email_Body”
Range(“D1”) = “Email Body”
Range(“E1”).Name = “email_Receipt_Date”
Range(“email_Receipt_Date”).Value = InputBox(“Enter Receipt Date like 20-mar-2020”)

‘Now get data from every email in the selected folder

For Each OutlookMail In Folder.Items
If OutlookMail.ReceivedTime >= Range(“email_Receipt_Date”).Value Then
Range(“email_Subject”).Offset(i, 0).Value = OutlookMail.Subject
Range(“email_Subject”).Offset(i, 0).Columns.AutoFit
Range(“email_Subject”).Offset(i, 0).VerticalAlignment = xlTop
Range(“email_Date”).Offset(i, 0).Value = OutlookMail.ReceivedTime
Range(“email_Date”).Offset(i, 0).Columns.AutoFit
Range(“email_Date”).Offset(i, 0).VerticalAlignment = xlTop
Range(“email_Sender”).Offset(i, 0).Value = OutlookMail.SenderName
Range(“email_Sender”).Offset(i, 0).Columns.AutoFit
Range(“email_Sender”).Offset(i, 0).VerticalAlignment = xlTop
Range(“email_Body”).Offset(i, 0).Value = OutlookMail.Body
Range(“email_Body”).Offset(i, 0).Columns.AutoFit
Range(“email_Body”).Offset(i, 0).VerticalAlignment = xlTop

i = i + 1
End If
Next OutlookMail

‘Clean up the memory for better performance

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

Useful Reference to extract data from MS Outlook