none
Outlook SenderName not working vba RRS feed

  • Question

  • Hi Guys,

    I have a excel macro to capture emails on outlook and I'm having trouble on the SenderName property.
    Kindly help me, below is the code:


    Sub getDataFromOutlook()

    Dim OutlookApp As Outlook.Application
    Dim OutlookNamespace As Namespace
    Dim Ns As Outlook.Namespace

    Dim OutlookMail As Variant
    Dim olShareName As Outlook.Recipient
    Dim i As Integer

    Set OutlookApp = New Outlook.Application

    Set Ns = OutlookApp.GetNamespace("MAPI")
      Dim Folder As Outlook.Folder
      Set olShareName = Ns.CreateRecipient("emailaddress@micro.com") '//
      Set Folder = Ns.GetSharedDefaultFolder(olShareName, olFolderInbox)
      'Set Folder = Ns.GetDefaultFolder(olFolderInbox).Folders("Accenture Stream")
      Set Items = Folder.Items


    i = 1

        For Each OutlookMail In Folder.Items
        
        If OutlookMail.Class = "43" Then
            If OutlookMail.ReceivedTime >= Range("Email_Receipt_Date") Then
          
                Range("Sender_of_Email").Offset(i, 0) = OutlookMail.SenderName
                Range("Sender_of_Email").Offset(i, 0).Columns.AutoFit
                Range("Sender_of_Email").Offset(i, 0).VerticalAlignment = xlTop
                Range("Date_of_Email").Offset(i, 0) = OutlookMail.ReceivedTime
                Range("Date_of_Email").Offset(i, 0).Columns.AutoFit
                Range("Date_of_Email").Offset(i, 0).VerticalAlignment = xlTop
                Range("Email_Subject").Offset(i, 0) = OutlookMail.ReceivedTime
                Range("Email_Subject").Offset(i, 0).Columns.AutoFit
                Range("Email_Subject").Offset(i, 0).VerticalAlignment = xlTop
                i = i + 1
                
            End If
             End If
        Next OutlookMail

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

    Call InsertFormulas

    MsgBox "Extraction Complete"


    End Sub

    Sub InsertFormulas()

    ThisWorkbook.Sheets("Sheet1").Activate

    EndRow = Cells(Cells.Rows.Count, 2).End(xlUp).Row

        Range("A2").Copy
            Range("A4:A" & EndRow).PasteSpecial xlPasteFormulas
        Range("E2:F2").Copy
            Range("E4:F" & EndRow).PasteSpecial xlPasteFormulas
        Range("A4:F" & EndRow).Copy
            Range("A4:F" & EndRow).PasteSpecial xlPasteValues

    End Sub

    This will populate the Sheet1 with the Sender of Mail, Date of Mail, Email Subject etc. but under the line:
     Range("Sender_of_Email").Offset(i, 0) = OutlookMail.SenderName
    it says "object doesn't support this property or method".

    Then when i comment Sendername, it will populate all except Sender of Email column.(that means other property are working like the  - ReceivedTime)

    Thank you in advance.

    Monday, December 10, 2018 9:23 PM

All replies

  • MailItem.Class property is an integer, not a string:

    If OutlookMail.Class = 43 Then

    It also make no sense to loop through *all* item in a folder - use Items.Find/FindNext or Items.Restrict on the Class and ReceivedTime properties.


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Monday, December 10, 2018 10:23 PM
  • Hi McKen,

    Based on my test, it works fine with the code "Range("Sender_of_Email").Offset(i, 0) = OutlookMail.SenderName" and I can get the SenderName data of email. Could you please check it again or change the PC to test it? 

    Also, I found the link to explain the error  "object doesn't support this property or method":

    VBA Object doesn't support this property or method

    Hope it helps you.

    Regards,

    Simon


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, December 11, 2018 11:54 AM
    Moderator
  • Thanks Simon,

    I forgot to mention that I am running this inside citrix.
    It is also working on me, on my outlook(outside citrix) but when I run it inside citrix, the sendername no longer working. Only the reiceived time etc.
    Tuesday, December 11, 2018 1:45 PM
  • Does it only fail for a folder returned from GetSharedDefaultFolder? Does it work if yo uuse GetDefaultFolder?

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Tuesday, December 11, 2018 2:47 PM
  • Yes, I tried working on defaultfolder, also not working.-inside Citrix.
    But when i tried outside(local) it is working.

    I am guessing it has something to do with the security.
    The sharedmailbox I am accesing is based on group.
    I mean, if you need to access the sharedmailbox, you need to be added on their group.
    Tuesday, December 11, 2018 4:42 PM
  • No, it has nothing to do with the security - "object doesn't support this property or method" means the given object does not expose property called SenderName .

    What does

    MsgBox TypeName(OutlookMail)

    show?


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Tuesday, December 11, 2018 9:50 PM
  • It shows "MailItem"

    The weird part is that, if I run it on my local, it is working.
    But when I run it inside citrix the .Sendername is not being recognize while the ReceivedTime and Subject is working.

    Note to consider: The sharedmailbox I am accesing is based on group.
    I mean, if you need to access the sharedmailbox, you need to be added on their group.
    It is inside the Citrix.
    Wednesday, December 12, 2018 2:26 PM
  • Does it show "MailItem" on the problematic machine just before it processes the problematic message that blows up with the error?

    Did you change your code to test for OutlookMail.Class = 43 (int) rather than "43" (string)?


    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Wednesday, December 12, 2018 2:37 PM
  • Yup, and I also changed the class to 43(int)
    Wednesday, December 12, 2018 2:43 PM
  • Ok I tried changing this line -Dim OutlookMail As Variant into -Dim OutlookMail As Outlook.MailItem
    now error it says "
    Method Sendername of object _MailItem failed"

    I tried also manual export into csv using the export/import feature on outlook, the SenderName and SenderEmailAddress is working.
    From: (Name) From: (Address)
    Wednesday, December 12, 2018 3:09 PM
  • Is it always the same message? Do you have the same problem if you look at it with OutlokSpy (select the message, click the Item button, select the SenderName property)?

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Wednesday, December 12, 2018 4:32 PM
  • Unfortunately we can't install OutlookSpy.
    We are not allowed to install other softwares.
    Wednesday, December 12, 2018 5:54 PM
  • Everytime i run the macro, outlook is giving me pop up to select which account.
    Any idea on this?, does it really have to do with security?


    Friday, December 14, 2018 2:26 PM
  • Do you mean a profile prompt?

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Friday, December 14, 2018 2:52 PM
  • yes, a profile prompt.
    Friday, December 14, 2018 6:26 PM
  • You can turn it off in Control Panel | Mail | Show Profiles | Prompt for a profile to be used.

    Dmitry Streblechenko (MVP)
    http://www.dimastr.com/redemption
    Redemption - what the Outlook
    Object Model should have been
    Version 5.5 is now available!

    Friday, December 14, 2018 7:11 PM
  • Thanks Dmitry, Done turning profile prompt in Control Panel.
    No longer pop up, but still the same issue, not working on SenderName when I run inside Citrix using closed group sharedmail box.

    But working on my open group/no password sharedmail box.
    Tuesday, December 18, 2018 1:22 PM