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:
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
‘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