Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


How to get Sender's email address using VBA

Posted on 2004-04-05
Medium Priority
Last Modified: 2007-12-19

I'm saving emails from outlook to an Access DB using VBA. I'm trying to get the sender's email address from emails.

We're on IMAP here and the enterprise address book does not show up in Outlook. I tried using ADO/ADOX to connect to it like a table and it doesn't seem to want to play ball that way.

Finally I'm trying to use CDO collection to get the sender's email address at the same time as I get the other info from the emails. To test this I've found some code that loops through the folder (for me this is an IMAP SP Folder) and for each email should get the sender's address.

Sub SendersInFolder()
Dim conn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim objOL As Outlook.Application
Dim olNS As Outlook.NameSpace
Dim objFolder As Outlook.MAPIFolder
Dim ObjSubFolder As Outlook.MAPIFolder
Dim myMailItem As Outlook.MailItem
Dim olItem As Outlook.MailItem
Dim strReport As String

Dim sOut(3) As String
Dim strSQL As String
Dim F%
Dim x
'Dim y
Set objOL = New Outlook.Application
Set olNS = objOL.GetNamespace("MAPI")
Set objFolder = olNS.Folders.Item(1)
Set ObjSubFolder = objFolder.Folders.Item(1)

    For Each olItem In ObjSubFolder.Items
      ' Get the Sender's name and Email address
      strReport = strReport & olItem.SenderName & " / " & GetSenderID(olItem) & vbCrLf
  MsgBox strReport
  'Clean Up
  Set olItem = Nothing
  Set myMailItem = Nothing
  Set ObjSubFolder = Nothing
  Set objFolder = Nothing
  Set olNS = Nothing
  Set objOL = Nothing
End Sub

Function GetSenderID(objMsg As Object) As String

Dim strEntryID As String
Dim strStoreID As String
Dim objSession As MAPI.Session
Dim objCDOItem As MAPI.Message
    'Get EntryID and StoreID for message
    strEntryID = objMsg.EntryID
    strStoreID = objMsg.Parent.StoreID
    'Start CDO session
    Set objSession = CreateObject("MAPI.Session")
    objSession.Logon    ' , , False, False               ', True
    'Pass item to CDO and get sender address
    On Error Resume Next
    Set objCDOItem = objSession.GetMessage(strEntryID, strStoreID)
    SenderFromAddress = objCDOItem.Sender.Address
    'Destroy variables
    Set objSession = Nothing
    Set objCDOItem = Nothing
End Function
It runs fine until this line [i]Set objCDOItem = objSession.GetMessage(strEntryID, strStoreID)[/i] and there it says
[i]Runtime Error: -21417221233 (8004010f)

Can anyone explain why this is? Is it because we're on IMAP here not Exchange?

Please bare in mind that I'm a beginner so explanation will need to be simple as well as thorough.

Question by:q_jd2004
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3

Author Comment

ID: 10756594

Will add more points if this is a tricky question - I don't know because I've not been programming for long (a few months).
LVL 19

Expert Comment

ID: 10756639
First thing first

you should not even be receiving an error on that line because the line preceding it is "On Error Resume Next". It should error and just continue to the next line.

Could you let me know if thats always been there or if that was added due to the error?


Author Comment

ID: 10757173
Sorry for confusion, and thanks for reply.

I put that in because of the error. Otherwise, in Function GetSenderID
objCDOItem get's set to "Nothing" .
SenderFromAddress get's set to "Empty"


Accepted Solution

happel earned 300 total points
ID: 10763704

Probable error is on : olItem.SenderName (wich doesn't exist -> olItem.Sender.SenderName does)

You could browse the messages with
   for each obj in folder.Messages
      Debug.Print obj.Subject

      'The sender - emailadres isn't a normally accessible property, next debug.print
      'brings up the security warning (Outlook)
      debug.print obj.Sender.Address

I'm not sure but I think then line
       strStoreID = objMsg.Parent.StoreID
should be
       strStoreID = objMsg.StoreID
But I dont see why you need the  objSession.GetMessage(strEntryID, strStoreID).
You can browse the messages (see 'first')

   The sender - emailadres isn't a normally accessible property.
   I use a tool call 'Redemption' to access 'privacy-blocked' and extended MAPI properties.

   From the Redemption site:

Display sender e-mail address (PR_SENDER_EMAIL_ADDRESS Extended MAPI property). Note that Outlook does not expose this property through its object model. There are dozens more properties that might be of interest to you: PR_TRANSPORT_MESSAGE_HEADERS on e-mail items, PR_SEND_RICH_INFO on Recipient objects, etc. Use MdbView or OutlookSpy to see which properties are available.

dim sItem, oItem
set sItem = CreateObject("Redemption.SafeMailItem")
set oItem = Application.Session.GetDefaultFolder(6).Items(1) 'get first e-mail from the Inbox, can be any other item
sItem.Item = oItem
PrSenderEmail = &H0C1F001E
MsgBox sItem.Fields(PrSenderEmail)

Author Comment

ID: 11377980

sorry for long delay, I'd been lockup up in another project. I'll look at redemption, thanks for that info and your help.


Hi Admin, sorry! I'll try not to do that long wait again.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Suggested Courses

670 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