Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SenderEmailAddress with email in Excel

Posted on 2011-05-06
Medium Priority
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

Accepted Solution

wesleystewart earned 1000 total points
ID: 35709557
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???

Expert Comment

ID: 35712512

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

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

581 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