none
How to list sender's email address of Inbox folder by VBA? RRS feed

  • Question

  • My expected result in excel by VBA:

    Column A : List of subject in one of Inbox folder, say Incoming

    Column B : Sender's email address

    • Moved by Steve Fan Monday, October 9, 2017 7:25 AM relocate
    Friday, October 6, 2017 4:15 AM

Answers

All replies

  • How to write the VBA to list both Subject and Sender's email address in Inbox file to excel?
    Saturday, October 7, 2017 4:29 AM
  • Hi,

    Welcome to the Microsoft Office for IT Professionals Outlook forum, which is for general questions and feedback related to Outlook client. Since your request is more related to VBA, I'll move your question to a more appropriate forum:

    https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=outlookdev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    Regards,

    Steve Fan


    Please remember to mark the replies as an answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, October 9, 2017 7:24 AM
  • Hello,

    I'd recommend starting from the Getting Started with VBA in Outlook 2010 article.

    Dim objNS As Outlook.NameSpace: Set objNS = GetNamespace("MAPI")
    Dim olFolder As Outlook.MAPIFolder
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    Dim Item As Object
    
    For Each Item In olFolder.Items
         If Item.Class = 43 Then
        'If TypeOf Item Is Outlook.MailItem Then 
            Dim oMail As Outlook.MailItem: Set oMail = Item
            Debug.Print oMail.Subject + oMail.SenderEmailAddress
        End If
    Next


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Monday, October 9, 2017 1:32 PM
  • Hi Eugene Astafiev,

    How to revise the VBA if the folder is "Tender in"?

    (There is error message for "Dim objNS As Outlook.Namespace" that has not been defined.)

    Tuesday, October 10, 2017 1:43 AM
  • Hi london7871,

    >>There is error message for "Dim objNS As Outlook.Namespace" that has not been defined.

    If you run the code in Excel VBA, please remember to add reference to Microsoft Outlook Object Library.

    >>How to revise the VBA if the folder is "Tender in"?

    Where is the folder?

    If it is a sub folder in Inbox folder, you could use this code to get the folder.

    Set olFolder = objNS.GetDefaultFolder(olFolderInbox).Folders("Tender in")

    If it is a custom folder at the same level as Inbox, please refer to below code to get the folder.

    Set olFolder = objNS.Session.Folders("your mail address").Folders("Tender in")

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 10, 2017 5:17 AM
  • To consolidate the above replies the vba is revised as follows:

    Sub Getaddress()
    
    Set olFolder = objNS.Session.Folders("london@daokehk.com").Folders("sysivan")
    
    Dim objNS As Outlook.Namespace: Set objNS = GetNamespace("MAPI")
    Dim olFolder As Outlook.MAPIFolder
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    Dim Item As Object
    
    For Each Item In olFolder.Items
         If Item.Class = 43 Then
        'If TypeOf Item Is Outlook.MailItem Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            Debug.Print oMail.Subject + oMail.SenderEmailAddress
        End If
    Next

    There is still an error message for "Dim objNS As Outlook.Namespace" that has not been defined.

    How to "add reference to Microsoft Outlook Object Library"?

    Tuesday, October 10, 2017 6:20 AM
  • You are trying to use an object which is not defined. You need to declare and initialize the object first:

    Sub Getaddress()
    
    Dim objNS As Outlook.Namespace
     Set objNS = OutlookApp.GetNamespace("MAPI")
    Dim olFolder As Outlook.MAPIFolder
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    Dim Item As Object
    
    For Each Item In olFolder.Items
         If Item.Class = 43 Then
        'If TypeOf Item Is Outlook.MailItem Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            Debug.Print oMail.Subject + oMail.SenderEmailAddress
        End If
    Next


    profile for Eugene Astafiev at Stack Overflow, Q&A for professional and enthusiast programmers

    Tuesday, October 10, 2017 11:24 AM

  • The following code
    olFolder = objNS.Session.Folders("london@daokehk.com").Folders("sysivan")
    has been revised as follows but there is still an error message for "Dim objNS As Outlook.Namespace" that has not been defined."
    Sub GetAddressA()
    
    Dim objNS As Outlook.Namespace
     Set objNS = OutlookApp.GetNamespace("MAPI")
    Dim olFolder As Outlook.MAPIFolder
    'Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    
    Set olFolder = objNS.Session.Folders("london@daokehk.com").Folders("sysivan")
    
    Dim Item As Object
    
    For Each Item In olFolder.Items
         If Item.Class = 43 Then
        'If TypeOf Item Is Outlook.MailItem Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            Debug.Print oMail.Subject + oMail.SenderEmailAddress
        End If
    Next
    
    NB: Your VBA, before revision, also has the same error message.
    Wednesday, October 11, 2017 1:49 AM
  • Hi london7871,

    You need go to Tools->References to add the reference to Microsoft Outlook Object Library. 

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 1:56 AM
  • Hi Terry,

    Thanks for showing the way to add the reference to Microsoft Outlook Object Library. 

    But VBA stop at : Set objNS = OutlookApp.GetNamespace("MAPI")

    with error message (translated from my Chinese Excel) : Execution stage error 424 - Need object here

    Wednesday, October 11, 2017 4:15 AM
  • Hi london7871,

    Please refer to below code.

    Sub GetAddressA()
    
    Dim OutlookApp As Outlook.Application
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Dim objNS As Outlook.Namespace
    Set objNS = OutlookApp.GetNamespace("MAPI")
    Dim olFolder As Outlook.MAPIFolder
    Set olFolder = objNS.Session.Folders("london@daokehk.com").Folders("sysivan")
    
    
    Dim Item As Object
    For Each Item In olFolder.Items
         If Item.Class = 43 Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            Debug.Print oMail.Subject + oMail.SenderEmailAddress
        End If
    Next
    End Sub

    You could refer to below link for more information.

    Automating Outlook from a Visual Basic Application

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, October 11, 2017 5:09 AM
  • Hi Terry,

    Upon my revision to your VBA to either:

    Set olFolder = objNS.GetDefaultFolder(olFolderInbox).Folders("sysivan")

    or

    Set olFolder = objNS.Session.Folders("london@daokehk.com").Folders("sysivan")

    VBA stops at the above code and error message (translated):

    execution error '-2147221233 (8004010f)' - Object cannot be found

    Wednesday, October 11, 2017 6:47 AM
  • Hi london7871,
    What's the result of Set olFolder = objNS.Session.Folders("test@address.com")?
    If it could get the folder object, please iterate through folders in the account to check if there is a  folders named
    "sysivan".
    Set olFolder = objNS.Session.Folders("test@address.com")
    For Each fld In olFolder.Folders
    If fld.Name = "sysivan" Then
    MsgBox "There is a folder named sysivan"
    Exit Sub
    End If
    Next fld
    MsgBox "There is no folder named sysivan"
    If it could not get the object, how many account did you add in the outlook? If you add only one account or you set the account you want to custom as the default account, you could use GetDefaultFolder to get a default folder and then try to get the parent of the default folder. The parent should be the account folder. Could you please test below code and tell us what's the output?
    Set olFolder = objNS.GetDefaultFolder(olFolderInbox)
    Debug.Print olFolder.Parent.Name
    Best Regards,
    Terry

    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Wednesday, October 11, 2017 7:25 AM
  • Hi Terry,

    The result of "Set olFolder = objNS.Session.Folders("london@daokehk.com")" is CPU looping several seconds and no response (no error message).

    The result of the above 2 VBA is error message (translated):

    script error: cannot set at constant

    VBA stop at "olFolder ="

    Wednesday, October 11, 2017 8:42 AM
  • Hi london7871,

    If I try to set he olFolder directly without defining it, I got a similar error: Assignment to constant not permitted. I think this is the same error as you got.

    olFolder is a constant and it need to be defined if you want to use it as normal variable name.

    I would suggest you change the variable name and here is the example with whole code.

    Sub GetAddressA()
    
    Dim OutlookApp As Outlook.Application
    
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Dim objNS As Outlook.Namespace
    
    Set objNS = OutlookApp.GetNamespace("MAPI")
    
    Dim customFolder As Outlook.MAPIFolder
    
    Set customFolder = objNS.Session.Folders("test@address.com")
    
    For Each fld In customFolder.Folders
    
    If fld.Name = "sysivan" Then
    
    MsgBox "There is a folder named sysivan"
    
    Exit Sub
    
    End If
    
    Next fld
    
    MsgBox "There is no folder named sysivan"
    
    End Sub

    And

    Sub GetAddressA()
    
    Dim OutlookApp As Outlook.Application
    
    Set OutlookApp = CreateObject("Outlook.Application")
    
    Dim objNS As Outlook.Namespace
    
    Set objNS = OutlookApp.GetNamespace("MAPI")
    
    Dim customFolder As Outlook.MAPIFolder
    
    Set customFolder = objNS.GetDefaultFolder(olFolderInbox)
    
    Debug.Print customFolder.Parent.Name
    
    End Sub

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, October 12, 2017 5:50 AM
  • Hi Terry,

    Part 1 VBA can give "There is a folder named sysivan";

    but there is no response from Part 2 VBA (just looping several second.)

    How to amend?

    Thursday, October 12, 2017 8:25 AM
  • Hi london7871,

    >>Part 1 VBA can give "There is a folder named sysivan";

    Since you could get the sysivan folder, I think you could use objNS.Session.Folders("test@address.com").Folders("sysivan") now.

    If it still not work for you, you could also set the folder when iterating through the folders.

    Sub GetAddressA()
    Dim OutlookApp As Outlook.Application
    Set OutlookApp = CreateObject("Outlook.Application")
    Dim objNS As Outlook.NameSpace
    Set objNS = OutlookApp.GetNamespace("MAPI")
    Dim customFolder As Outlook.MAPIFolder
    Set customFolder = objNS.Session.Folders("test@address.com")
    For Each fld In customFolder.Folders
    If fld.Name = "sysivan" Then
    MsgBox "There is a folder named sysivan"
    Set customFolder = fld
    Exit For
    End If
    Next fld
    If customFolder.Name = "sysivan" Then
    For Each Item In customFolder.Items
         If Item.Class = 43 Then
        'If TypeOf Item Is Outlook.MailItem Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            Debug.Print oMail.Subject + oMail.SenderEmailAddress
        End If
    Next
    End If
    End Sub

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, October 16, 2017 1:27 AM
  • As I don't know how to amend VBA by "objNS.Session.Folders("london@daokehk.com").Folders("sysivan")',
    so I try the VBA instead.
    The VBA stops with a MsgBox "There is a folder named sysivan".
    How to amend VBA?

    Tuesday, October 17, 2017 3:24 AM
  • Hi london7871,

    Delete the code 

    MsgBox "There is a folder named sysivan"

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 17, 2017 3:27 AM
  • I have deleted the following code but the VBA just looping 2 seconds and then no response.
    MsgBox "There is a folder named sysivan"
    Wednesday, October 18, 2017 1:24 AM
  • Hi london7871,

    The result of Debug.print is shown in Immediate window, have you checked it? Maybe change debug.print to msgbox would make result clearer.

     Msgbox oMail.Subject + oMail.SenderEmailAddress

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 19, 2017 10:48 AM
  • Thanks Terry, it works with the following code:
    Msgbox oMail.Subject + oMail.SenderEmailAddress
    How to go to the Immediate window (for my checking the result of Debug.print) with the code:
    Debug.Print oMail.Subject + oMail.SenderEmailAddress

    Friday, October 20, 2017 1:59 AM
  • Hi london7871,

    Open VBE->View->Immediate Window

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 2:04 AM
  • Hi Terry it's OK.
    How to list the result in excel:
    Column A : oMail.Subject
    Column B : oMail.SenderEmailAddress
    Friday, October 20, 2017 2:48 AM
  • Hi london7871,

    Replace correspond code.

    RowCount = 1
    If customFolder.Name = "sysivan" Then
    For Each Item In customFolder.Items
         If Item.Class = 43 Then
            Dim oMail As Outlook.MailItem: Set oMail = Item
            ActiveSheet.Cells(RowCount, 1) = oMail.Subject
            ActiveSheet.Cells(RowCount, 2) = oMail.SenderEmailAddress
            RowCount = RowCount + 1
        End If
    Next
    End If

    Best Regards,

    Terry


    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. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 20, 2017 6:45 AM
  • Hi Terry,

    Completely OK.

    Thanks for your patient help.

    • Marked as answer by london7871 Friday, October 20, 2017 8:30 AM
    Friday, October 20, 2017 8:30 AM