SenderEmailAddress with email in Excel

Posted on 2011-05-06
Last Modified: 2012-05-11
I'm automating a workflow in an Excel workbook.  The workflow involves individuals passing the workbook along an approval chain.  I've figured out how to email the workbook.  I'd like to keep a record of the path - that is the sender(s) and the recepient(s) of the workbook.  I have the logic for this as well.  The one thing I can't figure out how to do is capture the email address of the sender.  I've tried the SenderEmailAddress property of the mail item, but it shows as blank.  Can someone tell me how to capture the sender's email address?  Am I running into a security restriction??  Thanks a lot.
Question by:jeff20850
    LVL 4

    Accepted Solution

    Are you sending the workbook via automation with VBA?  Can you insert a quick Environ("Username") to grab the username?  It won't be the sender's email address per se, but should allow you to identify them???
    LVL 5

    Expert Comment


    Try this,

    Sub ConvertEmailDisplayToAddress()
        Dim objApp As Application
        Dim objNS As NameSpace
        Dim objFolder As MAPIFolder
        Dim colContacts As Items
        Dim objItem As Object
        Dim strAddress As String
        Set objApp = CreateObject("Outlook.Application")
        Set objNS = objApp.GetNamespace("MAPI")
        Set objFolder = objNS.PickFolder
        If Not objFolder Is Nothing Then
            Set colContacts = objFolder.Items
            For Each objItem In colContacts
                If objItem.Class = olContact Then
                    With objItem
                        If .Email1AddressType = "SMTP" And _
                          .Email1DisplayName <> .Email1Address Then
                            strAddress = .Email1Address
                            .Email1Address = ""
                            .Email1Address = strAddress
                        End If
                        If .Email2AddressType = "SMTP" And _
                          .Email2DisplayName <> .Email2Address Then
                            strAddress = .Email2Address
                            .Email2Address = ""
                            .Email2Address = strAddress
                        End If
                        If .Email3AddressType = "SMTP" And _
                          .Email3DisplayName <> .Email3Address Then
                            strAddress = .Email3Address
                            .Email3Address = ""
                            .Email3Address = strAddress
                        End If
                        If Not .Saved Then
                        End If
                    End With
                End If
        End If
        Set objItem = Nothing
        Set colContacts = Nothing
        Set objFolder = Nothing
        Set objNS = Nothing
        Set objApp = Nothing
    End Sub

    Thanks ,

    Author Closing Comment

    Thanks a lot.  This is exactly what I need, as I really want the user and not his/her email address.  Thanks again.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    Check out this infographic on what you need to make a good email signature that will work perfectly for your organization.
    Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now