Solved

Reading the email address stored within the From field

Posted on 2011-03-22
34
271 Views
Last Modified: 2012-05-11
I am developing a function within my Access database that lets us handle bounce backs and "Out of office" emails.  To do this, I link directly into Outlook via ADO and then pull in the relevant data.  However, on some of the "Out of office" emails it has the persons name within the FROM field but no email address (although I can see it on the screen).  I am guessing that it is within the email properties, but i do not know how to access this data.

Currently (after the connection string), I am simply using a very simple test string to select the emails within a specific folder:
SELECT * FROM [Bounced emails]

But as mentioned above, this doesn't give me everything.  Any ideas?
0
Comment
Question by:Andy Brown
  • 18
  • 10
  • 5
34 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35190307

<I link directly into Outlook via ADO and then pull in the relevant data.>
...p"ull in the relevant data" from what?

What is this "[Bounced emails]" table/query?
What is it based on, what fields are in it..?
...etc
0
 

Author Comment

by:Andy Brown
ID: 35191012
Sorry about that.

I have create a folder (within the inbox) called "Bounce emails", which is where I drag all of my bounced emails to.  I look through this folder for emails and handle them accordingly.  As for the fields available for each email - it has everything I need (Subject, Body etc), but the FROM field only contains the persons name, not their email address (even though it is visible on the screen).

I have searchd through all of the available fields that are available - but there doesn't appear to be a "Sent From" type field, which I am guessing is in another table/location.

I'm not sure if this helps, if you need anything else please let me know.

Thanks for the help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35192505
From what I remember this has to do with Outlook being able to reference the Recipients Name and their email address.
In a linked In-box table, the email field does not appear to be visible, but the name (the "From" field does).

But the email address is in fact in the email.
It can be seen if you right-click the email and select: Message Options
The innards of the email (and the senders email address) can be seen under the "Message Headers" section.

Perhaps there is  another connection technology that reveals this data...?


I had thought that you could also link in the Contacts table and create a query with both outlook tables.
Then link both tables on the "Normalized Subject" and "From" fields.
But this produced no results..  (Perhaps you can investigate this technique further...?)

Let's see what other experts post.

JeffCoachman
0
 

Author Comment

by:Andy Brown
ID: 35192614
Great stuff Jeff,

It's a tricky one as the Contact will not exist within Outlook, so that ones out.

The annoying thing is that when you pull in the FROM field value, it only has the persons name (in most 'Out of office' responses).  However, there is definitely somewhere containing this info - I just need to get at it.

Thanks again for all of your help.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35193087
Again, perhaps there is another connection technology that can get at this data...

To be sure, click the "Request Attention" link and ask that the Exchange zone be added to this Q.

Until then, let's see if another Expert chimes in...
0
 

Author Comment

by:Andy Brown
ID: 35199399
Will do, but I'm hoping it's simply a table that we need to find.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35200050
The field you want is SenderEmailAddress.  The problem is, assuming that I'm remembering correctly, that ADO doesn't return that field.  I believe it returns a fixed set of fields just as an Access table linked to Outlook does.  You can access Outlook directly via it's object model and get all the fields.  Are you interested in that approach?
0
 

Author Comment

by:Andy Brown
ID: 35200197
Thanks BlueDevilFan

I think think your right about ADO only returning a few of the fields.  As for using the object model, that might do the trick, wish i'd have thought of that.

I'm trying to get the SenderEmailAddress, Subject and Body, of records within a folder called "Bounced emails".  Ifyou have anything, or could point me in the right direction - it would be greatly appreciated.

Thanks again.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35200238
Where is the Bounced Emails folder?  Is it under the Inbox, at the same level as the Inbox, or somewhere else?
0
 

Author Comment

by:Andy Brown
ID: 35200298
Sorry about that,

Inbox\Dev\Bounced Emails
0
 
LVL 76

Accepted Solution

by:
David Lee earned 500 total points
ID: 35200323
This should do it.
Sub ReadBounced()
    Dim olkApp As Object, olkSes As Object, olkFld As Object, olkMsg As Object
    Set olkApp = CreateObject("Outlook.Application")
    'Change the profile name (i.e. Outlook) on the next line as needed.'
    Set olkSes = olkApp.GetNamespace("Outlook")
    olkSes.Logon
    Set olkFld = olkSes.GetDefaultFolder(olFolderInbox).Folders("Dev").Folders("Bounced Emails")
    For Each olkMsg In olkFld.Items
        With olkMsg
            'Your code goes here.  Not knowing what you need to do with the data I am only showing the fieldnames.'
            .Subject
            .Body
            .SenderEmailAddress
        End With
    Next
    Set olkMsg = Nothing
    Set olkFld = Nothing
    olkSes.Logoff
    Set olkSes = Nothing
    Set olkApp = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Andy Brown
ID: 35200422
There's always something :)

I got a compile error, variable not defined on "olFolderInbox" within the following string.

Set olkFld = olkSes.GetDefaultFolder(olFolderInbox).Folders("Dev").Folders("Bounced Emails")

I don't currently have a reference to the Outlook object (but it looks like you've used late binding)

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35200543
Shouldn't you really have accepted the post by BlueDevilFan as the "Solution"...?

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26902792.html?cid=238#35200298
...is not really a "Solution", it is just you agreeing to give the suggestion a try...
0
 

Author Comment

by:Andy Brown
ID: 35200555
Sure, but I'm pretty confident this will do the job. I just need to sort out that variable (everything else makes sense).

Thanks though.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35200756
Yes, I used late binding.  You can change that if you want to.  Change olFolderInbox to 6, or define a constant with that name and set its value to 6.  Sorry, I write the code in Outlook using early binding and then typically change to late binding when I post.  I forgot to fix that constant.

What Jeff is saying about the way you're closing is that you're accepting your own comment as the answer and not awarding any points.  In essence it's like saying that you solved the issue yourself.  How about accepting my post as the answer and awarding points to it?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35200896
AndrewDBrown,

Yes, again, if you look at yopur post (that you acceped as the solution), you see that it is not really a solution, again it is only your agreement to try the suggestion.

I think you may have just selected your own post by accident.

Please click the "Request Attention" link for help with accepting BlueDevilFan's post, which *is* actually the "Solution"

;-)

JeffCoachman
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Andy Brown
ID: 35200940
Dohhhhhhh......sorry about that.

I've now replaced the olFolderInbox with 6, which seems to work fine.

However, I now get "Invalid procedure or argument" on the following line:
Set olkSes = olkApp.GetNamespace("Outlook")

PS - I'll request attention now, once again really sorry about that.



0
 

Author Comment

by:Andy Brown
ID: 35200991
Mistake - sorry about that.  Please assign the points to BlueDevilFan

Thank you.
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35201071
No problem.  These things happen.

What version of Outlook are you using?
0
 

Author Comment

by:Andy Brown
ID: 35201104
2007 (although) it could end up on 2003/2010.

Thanks
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35201164
For 2007 and 2010 you can change this line

Set olkSes = olkApp.GetNamespace("Outlook")

to

Set olkSes = olkApp.GetNamespace(olkApp.DefaultProfileName)

For 2003 you must either know the profile name or read it from the registry.  I think I have a code fragment here somewhere that does that.  Let me know if you need it and I'll find and post it.
0
 

Author Comment

by:Andy Brown
ID: 35201353
Same error I'm affraid.  If I run "debug.print olkApp.DefaultProfileName", it returns "Outlook" so that bit is ok.
0
 

Author Comment

by:Andy Brown
ID: 35201368
If it helps I know the full string for that folder:

outlook:\\Personal Folders\Inbox\Dev\Bounced Emails
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35201405
Is your mail on an Exchange server?  Is the Inbox in that path the one where all your mail is delivered?
0
 

Author Comment

by:Andy Brown
ID: 35201423
Most of the time it will be Exchange, but it could be Outlook.

And yes.

I bet you wished you never looke at this question.....
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35201620
Outlook has a set of default folders (inbox, calendar, etc.).  Those folders are in the message store (mailbox or PST file) where mail is delivered.  It's unusual for an Exchange server account to deliver mail to a PST file.  Mail is typically delivered to the mailbox which is on the Exchange server.  That would make the Inbox in the mailbox the default, not the one in a PST file.  The command is getting the default inbox.  If that's the inbox in the Exchange mailbox and not the inbox folder in the PST file, then the command wouldn't find the folder you want.  So, are you sure that the inbox folder in the PST file is the default?

No, I don't wish that.  I'm here to help.
0
 

Author Comment

by:Andy Brown
ID: 35201686
Thanks BlueDevilFan - I'll take another look in the morning (when I'm a little more awake).

Really appreciate your help today, I'll post back tomorrow.
0
 

Author Comment

by:Andy Brown
ID: 35205346
It's amazing what a good night's sleep can do.  I changed one reference (Outlook to MAPI - see code below) and it pretty much worked, except now Outlook is coming up with an annoying security warning.

I think I'm going to post this as another question.

Thank you so much for the help - it really has been an interesting exercise.
Sub ReadBounced()

    Dim olkApp As Object, olkSes As Object, olkFld As Object, olkMsg As Object
    Set olkApp = CreateObject("Outlook.Application")
    
    'Set olkSes = olkApp.GetNamespace("Outlook")
    Set olkSes = olkApp.GetNamespace("MAPI")
    
    olkSes.Logon
    Set olkFld = olkSes.GetDefaultFolder(6).Folders("Dev").Folders("Bounced emails")
    For Each olkMsg In olkFld.Items
        With olkMsg
            Debug.Print .SenderEmailAddress
        End With
    Next
    Set olkMsg = Nothing
    Set olkFld = Nothing
    olkSes.Logoff
    Set olkSes = Nothing
    Set olkApp = Nothing
    
End Sub

Open in new window

Outlook.JPG
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35205416
My fault.  Where was my brain at?  

You should not get that warning on Outlook 2007 or 2010 unless security is misconfigured or Windows cannot detect an acceptable anti-virus/malware solution.  You will get that warning on Outlook 2003.  While it cannot be turned off on Outlook 2003 and earlier there are ways to work around it.  Here are some of those ways.

1.  Sign the code.  Here's a link to instructions on doing that: http://msdn.microsoft.com/en-us/library/aa155754(office.10).aspx
2.  Use ClickYes (http://www.contextmagic.com/express-clickyes/), a small utility that'll click the Yes button for you.  It creates a security hole though, since a virus could start sending messages and ClickYes would click the Yes button for it too.  
3.  Use Redemption (http://www.dimastr.com), a COM library that enables code to safely bypass Outlook security.


The question is still set to be closed by accepting your comment.  To prevent that you can cancel the close and then go through the closure process a second time this time selecting a comment as the solution and awarding points.  Neither Jeff nor I are admins of the system and we can't change the way a question is closed.  The admins don't watch the questions so they won't see your comment in post 35200991 requesting a change in the way the question is closed.  You should see a button on the page that cancels the close.  Clicking it should cancel the close and allow you to restart the process.  If not, then I can post an objection to the closure.
0
 

Author Comment

by:Andy Brown
ID: 35205487
Please can you assign all points to BlueDevilFan.  Thank you.
0
 

Author Comment

by:Andy Brown
ID: 35205492
Thanks BlueDevilFan (yet again),

I'm not sure if I have closed/flagged this questions off correctly, but can't see anywhere else to do it from.  I have objected (above)....
0
 

Author Closing Comment

by:Andy Brown
ID: 35205962
I hope this has worked....
0
 
LVL 76

Expert Comment

by:David Lee
ID: 35206456
You're welcome.  Glad I could help.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Suggested Solutions

Resolve Outlook connectivity issues after moving mailbox to new Exchange 2016 server
Are you unable to connect or configure Hotmail email account in Microsoft Outlook 2010, 2007? Or Outlook.com emails are not downloading to Outlook? Lets’ see the problem and resolve Outlook Connector error syncing folder hierarchy (0x8004102A).
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

758 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

27 Experts available now in Live!

Get 1:1 Help Now