Get Email Address of Sender

See more:

Outlook

VBA

Hi Everyone,

I Hope i'm on the right place :P

i am trying to extract the email address of sender from an outlook email. i got this piece of code and modified a little to allow a user select what folder should the code run into.

Sub GetSMTPaddress()
     
    Dim ns As NameSpace
    Dim fldr As MAPIFolder
    Dim m As MailItem
    Dim xlApp As Object
    Dim xlWB As Object
    Dim xlSht As Object
    Dim r As Long
     
     'set up an Excel sheet for the results
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Add
    Set xlSht = xlWB.Sheets.Add
    xlSht.Name = "SMTPs"
     
     'target the selected folder
    Set ns = Application.GetNamespace("MAPI")
   Set fldr = ns.GetFolderFromID( _
ActiveExplorer.CurrentFolder.EntryID, _
ActiveExplorer.CurrentFolder.StoreID)

     
    r = 1 'used as excel row index
    For Each m In fldr.Items 'loop thru the mail items
        If m.SenderEmailType = "SMTP" Then
             'check it's not already in col A of our results sheet
            If xlSht.Columns(1).Find(m.SenderEmailAddress, LookIn:=xlValues) Is Nothing Then
                xlSht.Cells(r, 1).Value = m.SenderEmailAddress
                r = r + 1 'increment the row index counter
                msg = m.Subject
            End If
        End If
    Next m
     
     'show the results
    xlApp.Visible = True
     
End Sub

i created a dummy folder on my outlook to test the result. i sent an email to my outlook account using another email of mine. used at least 2 email addresses to send test emails to my outlook email.

i selected the folder. then run the script.
well the Excel file should show 2 different email address in rows when i run the code.

Excel doesn't populate the emails from the selected folder. i checked the VBA window of outlook and tested the script if the correct folder is selected by the script and Yes it was able to.

The

Dim m As MailItem

even shows the subject line of the first test mail.



Please let me know where the code went wrong? Thanks in advance.