Looping through Each Mail Item in Outlook Folder :


 
Here is a Code to make loop on mail item in a particular folder of Outlook. you can use this code to make summary of received Mail
Like  X person send Y Number of Mails.
 
 
Sub GetSummaryFromOutLook()
 
    Dim outLookApp       As Object
    Dim ObjMitem         As Object
    Dim NameSpace        As Object
    Dim ObjFolder        As Object
    Dim ObjAttachment    As Object
    Dim lngCounter       As Long
    Dim VarArrName
    Dim VarArrCount
    
    lngCounter = 1
    Set outLookApp = CreateObject(“Outlook.Application”)
    Set NameSpace = outLookApp.GetNamespace(“MAPI”)
    
    Set ObjFolder = NameSpace.GetDefaultFolder(olFolderInbox).Folders(1)
    
    With CreateObject(“Scripting.Dictionary”)
        For Each ObjMitem In ObjFolder.Items
            If Month(ObjMitem.SentOn) = Range(“StatusOfMonth”).Value Then
                .Item(ObjMitem.SentOnBehalfOfName) = .Item(ObjMitem.SentOnBehalfOfName) + 1
            End If
        Next
        VarArrName = .keys
        VarArrCount = .Items
    End With
    
    If IsArray(VarArrName) Then
        With Range(“GetSummary”)
            .CurrentRegion.ClearContents
            .Resize(UBound(VarArrName)).Value = Application.Transpose(VarArrName)
            .Offset(, 1).Resize(UBound(VarArrCount)).Value = Application.Transpose(VarArrCount)
            .CurrentRegion.Sort key1:=.Offset(-1, 1), order1:=xlDescending
        End With
    End If
   
    Set outLookApp = Nothing
    Set ObjMitem = Nothing
    Set NameSpace = Nothing
    Set ObjFolder = Nothing
    Set ObjAttachment = Nothing
    
End Sub
 

2 Comments Add yours

  1. Lucas says:

    I am placing this code in a module in an Excel spreadsheet but I am getting a run time error on object required Set outLookApp = CreateObject(“Outlook.Application”).

    1. try to remove double quote and put them back everywhere you get error.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s