We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

How to get Sender's email address using VBA

q_jd2004
q_jd2004 asked
on
Medium Priority
5,313 Views
Last Modified: 2007-12-19
Hiya,

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
    Next
   
  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
[/code]
It runs fine until this line [i]Set objCDOItem = objSession.GetMessage(strEntryID, strStoreID)[/i] and there it says
[i]Runtime Error: -21417221233 (8004010f)
[MAPI-[MAPI_E_NOT_FOUND(8004010f)]][/i]

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.

Cheers.
Comment
Watch Question

Author

Commented:
PS:

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).
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?


-Brian

Author

Commented:
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" .
and
SenderFromAddress get's set to "Empty"





Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
Hiya,

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

Q

Hi Admin, sorry! I'll try not to do that long wait again.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.