extracting the email address from the sender

[dcharland]

I'm trying to write code in excel vba that will extrat the email address from the "from" field of an outlook message. Does anyone know how to do that?

Thanks
Denis

 

[Ruddles]

If you have access to it as an Outlook MailItem object, then it's the .SenderEmailAddress property.

What code have you got so far?

 

[dcharland]

This is my third attempt (changing logic each time) at creating a macro that will be able to read the email I sent via excel and set the status (variable) into my spread sheet. I need the address from the "from" because some emails bounce and don't have the email address in the body of the email.

"I could also use you hlep for: put these email addresses into an array with it associated status and before adding a new one compare it to the ones already in the array, only include it if it doesn't exist in the array. if it does exist update the status. (that would mean that I got two emails from the email address) E.i. email was (delayed) then (failed).

here is the code. Note that the .SenderEmailAdress is not working It give me an error

Sub SetStatus()
 
 
Dim OutApp As Object ' Outlook.Application
Dim NmSpace As Object 'Outlook.NameSpace
Dim Inbox As Object ' Outlook.MAPIFolder
Dim MItem As Object ' Outlook.MailItem
Dim MySubFolder As Object
Dim i As Long
Dim Response As String
Dim BodyArray() As Variant
Dim EmailAddress As String
Dim Ebody As Variant
Dim Ewords As Variant
Dim Status As String
Dim Cell As Range
Dim ProcessedFolder As Object
Dim MatchFound As String
Dim countnoemail As Integer
Dim emaillist As Variant
Dim el As Integer

Set OutApp = CreateObject("Outlook.Application")
Set NmSpace = OutApp.GetNamespace("MAPI")
Set Inbox = NmSpace.GetDefaultFolder(6) 'olFolderInbox
Set MySubFolder = Inbox.Folders("test") ' Note Case Sensitive!
Set ProcessedFolder = Inbox.Folders("processed") ' Note Case Sensitive!
emaillist = ""
el = 0

'****************************************************************
' This macro will look in the body of each email and extract
' the email addresses contained within them to cross reference
' them with the email addresses in excle and will set the status
' according to the subject email. March 4 2011
'****************************************************************

MsgBox MySubFolder.Items.count ' Debugging Only

For Each MItem In MySubFolder.Items ' Starts with first email recieved

    Select Case True
        Case Left(MItem.Subject, 13) = "Undeliverable": Status = "Undeliverable"
        Case Left(MItem.Subject, 4) = "Read": Status = "Read"
        Case Left(MItem.Subject, 8) = "Not Read": Status = "Deleted"
        Case InStr(MItem.Subject, "(Failure)"): Status = "failed"
        Case InStr(MItem.Subject, "(Delay)"): Status = "Delayed"
        Case UCase(Left(MItem.Subject, 3)) = "RE:": Status = "Replied"
        Case InStr(MItem.Subject, 14) = "Returned mail:": Status = "Invalid Email"
        Case Else: Status = "Other"
    End Select
    MsgBox MItem.SenderEmailAddress
    ' Now I need to Find all of the email adresses within the email and delete duplicated ones, If any.
     If InStr(MItem.body, "@") Then ' If MItem.body contains an @ character
        'Assign each word in the body of the email to Ewords
        Ewords = Split(MItem.body) ' Split each word in MItem.body and assign it to the Ewords variable
        
            For i = LBound(Ewords) To UBound(Ewords) ' sets the for i loop to the number of split in MItem
                If InStr(Ewords(i), "@") Then 'Found the string that contains an email address
                    'clean the email address
                    Call EmailAddresCleanup(Ewords(i))
                    'compare current email with the array
                    'if it doesn't exist include it and put the status
                    'if it exit move on
                    'keep the list of emails until the end of the sub

                Else
                                    
                End If
            Next i
     MsgBox MItem
     Else
     ' Take the email address from the From field
     
     End If

Next MItem ' Goes to next email

GoTo cleanup ' Cleans all of the variables

cleanup:

Set MItem = Nothing
Set Inbox = Nothing
Set NmSpace = Nothing
Set OutApp = Nothing
Set MySubFolder = Nothing

End Sub

 

[dcharland]

I've been trying, for a few day's now to access the email address from the sender....for some unexplained, or should I say unknown reasons to me the .SenderEmailAddress is not working!

Can anyone help. I don't have access to the outlook.mailitem or if I do I don't know how to access it?

Thanks for the help.

Denis

 

[dcharland]

I did more investigations on this issue and notice that the .SenderEmailAddress is actually working, the problem is related to the fact that the email that it's reading is a read receipt and doesn't contain the .SenderEmailAddress property!!

So the question is now, is there a way to extract the email from a Read Reciept? Or is there a way that I can link it back to the original email sent? Note that I sent multiples emails of emails with the same subject line.

 

[Ruddles]

You need to check whether the item you're currently processing is a real email or a read receipt. The code will look something like this:-

If MItem.MessageClass = "IPM.Note" Then
    ' it's an email
ElseIf MItem.MessageClass = "IPM.Report" Then
    ' it's a read receipt
Endif

I don't know where the address is though.

If you declare MItem as Outlook.MailItem, then you'll get the benefit of Intellisense menus telling you what methods and properties are available for MItem. You might find something that way.

I'd hate to think you had to match the read receipt up with the sent item to find out who the addressee was.

 

[dcharland]

Thanks for the response, will give it a try. Do you know why I get an compile error: User-type not defined when I declare :

Dim MItem As Outlook.MailItem

Thanks

 

[Ruddles]

You were declaring your objects simply as Object and allowing VBA to 'late bind' them at execution time. If you 'early bind' them at compilation time, you need to provide a library where VBA can look up what type of object they are.

This is done in VBA by going Tools > References and, in this case, selecting the Microsoft Outlook Objects Library.

 

[dcharland]

Hi,

It's been a few weeks now and I've tried everything, with the exception of the one way it would work .

The my knowledge, there isn't a way of reading the senders email address from a read receipt!

So I will need to find a way to link the read receipt with the original email sent and extract the email from the original person i sent it to. In a way that isn't to bad since I had situations where someone else opened the email! Perhaps, their server automatically forward it.

I will post a new question as i couldn't find how to "link" the two either?

 

[lyduyphuong]

Hi,

It's been a few weeks now and I've tried everything, with the exception of the one way it would work .

The my knowledge, there isn't a way of reading the senders email address from a read receipt!

So I will need to find a way to link the read receipt with the original email sent and extract the email from the original person i sent it to. In a way that isn't to bad since I had situations where someone else opened the email! Perhaps, their server automatically forward it.

I will post a new question as i couldn't find how to "link" the two either?

Hi dcharland

Can you share me the file with code for reading the senders email address from a read receipt? I need it to do st like this..

My Email: lyduyphuong@gmail.com

Thanks for sharing!!!