VBA code to retrieve outlook emails!!!

Outlook is popular email application to handle business emails. Thus, we use it for couple of hours to handle various email processing task in daily routine. Sometime, it is very time consuming to process certain emails received from specific sender or group. However, we can retrieve emails details to excel using a excel vba to code. Which can be very helpful to process such emails. Even, we can make a tracker while processing emails specifically processed for transaction kind of activities.

In this topic, we will see how to retrieve outlook emails detail from Inbox folder into excel workbook.

Before writing the macro add reference to outlook library as shown in below steps:

Step1:

Step2:

Now, we are ready to code below excel macro to retrieve outlook email details from Inbox folder.

Option Explicit

Sub Retrieve_Emails_From_Inbox()
' Author: Dreams24
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

'Declare Variables
Dim olApp As Outlook.Application
Dim olNs As Outlook.Namespace
Dim olFolder As Outlook.MAPIFolder
Dim olMail As Outlook.MailItem
Dim i As Long
Dim olDate As Date
Dim shtName As String
Dim lRow As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = False
    
'Pass Sheet name where you want email details
shtName = "Sheet1"

Set olApp = New Outlook.Application
Set olNs = olApp.GetNamespace("MAPI")

'Pass date to variable for Received time
olDate = Date ' e.g. olDate = "10/01/2017"

ThisWorkbook.Sheets(shtName).Activate
Range("A1:D" & ActiveSheet.Rows.Count).Clear
Range("A1") = "Subject"
Range("B1") = "Received Time"
Range("C1") = "Sender Name"
Range("D1") = "Email Body"

Set olFolder = olNs.GetDefaultFolder(olFolderInbox)
    
'For loop to check email item for Received time(Date)
    For i = olFolder.Items.Count To 1 Step -1
    Application.StatusBar = "Processing email item (" & i & ")..."
        If TypeOf olFolder.Items(i) Is MailItem Then
            Set olMail = olFolder.Items(i)
            
            'if condition to check Received time
            If InStr(olMail.ReceivedTime, olDate) > 0 Then
            
                'Variable to get last row count in activesheet
                lRow = Range("A" & ActiveSheet.Rows.Count).End(xlUp).Row
                
                'Pass values to column A, B, C and D in Activesheet
                Range("A" & lRow + 1) = olMail.Subject
                Range("B" & lRow + 1) = olMail.ReceivedTime
                Range("C" & lRow + 1) = olMail.SenderName
                Range("D" & lRow + 1) = olMail.Body
            End If
        End If
    
    Next i
    Set olFolder = Nothing
        
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = True
    
MsgBox "All email retrieved for received date: " & olDate, vbInformation
    
End Sub

VBA code to collect pivot data source details for all pivot in a workbook!!!

Data Source is key factor to create pivot table in excel. On the basis of data source pivot get refreshed and we can made changes in pivot structure. Thus, to know pivot data source is very essential to structure pivot layout. In this topic, we will see how can be data source information for various pivot collected using excel macro.

Here, we go for excel macro which will collect pivot data source details for all pivots in a workbook: 

Option Explicit

Sub Pivot_DataSource()
' Author: Dreams24d
' Written for VBA Tricks and tips blog
' https://vbatricksntips.com

'Declare Variables
Dim pvt As PivotTable
Dim ws As Worksheet
Dim strPvt As String
Dim sht As String
Dim pvtSource As String
Dim pvtName As String
Dim i As Integer

Application.ScreenUpdating = False
Application.DisplayAlerts = False

strPvt = "       Pivot Name     Sheet Name   Source Data" & vbNewLine
i = 1

'For loop through each worksheets
    For Each ws In ActiveWorkbook.Worksheets
        Worksheets(ws.Name).Select

        'Loop to select pivot tables in each worksheet
        For Each pvt In ws.PivotTables
           
           pvtName = pvt.Name
           pvtSource = pvt.SourceData
           sht = ws.Name
           
            'Pass Pivot details to variables in order to show in message. comment or delete this if not required
            strPvt = strPvt & i & ".    " & pvtName & "         " & sht & "       " & pvtSource & vbNewLine
            
            'Uncomment below lines to get pivot table list in sheet1
            'ActiveWorkbook.Sheets("Sheet1").Range("A" & i).Value = pvtName
            'ActiveWorkbook.Sheets("Sheet1").Range("B" & i).Value = sht

            'ActiveWorkbook.Sheets("Sheet1").Range("C" & i).Value = pvtSource

            i = i + 1
            
        Next pvt
    Next ws
    
MsgBox "Pivot Tables and their data source listed as Below: " & vbNewLine & vbNewLine & strPvt, vbInformation, "Pivot and Data Source"

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub