SenderEmailAddress with email in Excel

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.
jeff20850Asked:
Who is Participating?
 
wesleystewartCommented:
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???
0
 
wellousCommented:
Hi,

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
                        .Save
                    End If
                End With
            End If
        Next
    End If
   
    Set objItem = Nothing
    Set colContacts = Nothing
    Set objFolder = Nothing
    Set objNS = Nothing
    Set objApp = Nothing
End Sub


Thanks ,
Wellous
0
 
jeff20850Author Commented:
Thanks a lot.  This is exactly what I need, as I really want the user and not his/her email address.  Thanks again.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.