.Sendername in vba to extract sender name in outlook mail is not returning the person name sent the email

 

Hi Excel vba champs, 

I have built the code to extract rmail detsils from outlook mailbox from selected folder. It worked for the first run and from second run onwards, its giving me an error at line         Cells(iRow + 1, “B”) = OutlookMail.SenderName and the error is – Run-tine error -2147467259 (80004005). Would anyone be able help me to fix this issue ?

 

Sub Test_SharedMailInbox()

    Dim ObjNS As Namespace

    Dim strFolderPath As String

    Dim OutlookMail As Outlook.MailItem

    ‘Set Outlook Object

    Set ObjNS = Outlook.GetNamespace(“MAPI”)

    Set objFolder = ObjNS.PickFolder

    If TypeName(objFolder) <> “Nothing” Then

        strFolderPath = objFolder.FolderPath

    End If

    Dim LastRow As Long

    Dim iRow As Long

    Dim iCol As Integer

    Dim i As Integer

    LastRow = 0

    i = 0

  ‘Find last row

    For iCol = 1 To 1       ‘Columns A:A

     iRow = Cells(65536, iCol).End(xlUp).Row

     If iRow > LastRow Then LastRow = iRow

    Next iCol

 

    For Each OutlookMail In objFolder.Items

 

    If OutlookMail.ReceivedTime >= Range(“E3”).Value Then

 

    ‘Add the serial number to the mail item..

        If Cells(iRow, “A”) = “Sl No” Then

        Cells(iRow + 1, “A”) = “1”

        Else

        Cells(iRow + 1, “A”) = Cells(iRow, “A”) + 1

        End If

 

        Cells(iRow + 1, “B”) = OutlookMail.SenderName ‘ need to work to get the sender name…**********

 

        Cells(iRow + 1, “C”) = OutlookMail.Subject

 

        Cells(iRow + 1, “D”) = OutlookMail.ReceivedTime

 

        Cells(iRow + 1, “E”) = “Query” & Format(Now, “YYYYMMDD”) & Format(Cells(iRow + 1, “A”), “0000”)

 

        Cells(iRow + 1, “F”) = Format(Cells(iRow + 1, “D”) + 3, “DD-MMM-YYYY”)

 

        Cells(iRow + 1, “K”) = Now

 

        Cells(iRow + 1, “L”) = strFolderPath

 

        Cells(iRow + 1, “M”) = Environ(“Username”)

 

        iRow = iRow + 1

 

        i = i + 1

 

    End If

 

    Next OutlookMail

 

‘Close the objects

    Set objFolder = Nothing

    Set ObjNS = Nothing

 

    MsgBox “Total *** ” & i & ” *** Outlook Email/s Copied To ****Emails Tab***”

 

 

By: Uday u

Leave a Reply

Your email address will not be published. Required fields are marked *