Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extracting email addresses from emails (2003 SP2)

    I have a folder with several thousand emails received. Is there a way to extract the senders' email addresses into new contacts, or a contact list, and eliminate any duplicates (of which there are many)? I am not a VB coder and if doing this requires code, does anyone have a sample?

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Extracting email addresses from emails (2003 S

    I wonder whether you could use the export feature to export just the addresses, and then de-duplicate the list in another Office application?

    A long time ago, I wrote some code that dumped email addresses out to a text file. I can't recall whether it skipped duplicates; probably not... it might be posted here somewhere, but I didn't find it in a search. I can look for it when I get back to the office.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting email addresses from emails (2003 S

    I already tried that and it doesn't work. Most of the emails are from a mailing list, and although the individual emails show the sender's email address, the export function doesn't export that but instead exports just the mailing list address.

  4. #4
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Extracting email addresses from emails (2003 S

    I don't understand what you mean about the Sender's address vs. the Mailing List address. If you map custom fields, can you locate the data you need?

  5. #5
    Super Moderator
    Join Date
    Dec 2000
    Location
    Renton, Washington, USA
    Posts
    12,560
    Thanks
    0
    Thanked 4 Times in 4 Posts

    Re: Extracting email addresses from emails (2003 S

    "Most of the emails are from a mailing list"

    Most people will use a "Mailing list" to prevent the capturing of the sender address to the lists. This is what spammers do!

    Now running HP Pavilion a6528p, with Win7 64 Bit OS.

  6. #6
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting email addresses from emails (2003 S

    The attached shows what I get when I export to Excel. That's the address of the mailing list, but if I look at individual emails, the from field shows the actual email address of the sender as 'Offroad-request@offroadlist.com on behalf of Andrew Simpkins [andrew@xxxxx.com]'. It's what follows the 'on behalf of' that I want to capture. It's obviously in the email somewhere, but it doesn't appear to be in an exportable field.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting email addresses from emails (2003 S

    Thanks for the code sample. In the line "Set colContacts = Outlook.Session.GetDefaultFolder(olFolderContacts) .Items" I presume this selects the currently selected folder, yes? I have about 300 Outlook mail folders. Where does the code send the output? See my <post#=618,294>post 618,294</post#>. Does this have any impact on the code?

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Extracting email addresses from emails (2003 S

    Try the attached. I can't tell if it is giving sensible/useful results for "sent on behalf of" or not. Note that it operates on the current folder and creates a text file in the root of C drive.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting email addresses from emails (2003 S

    Thanks for the code. It partly worked; it got the names but not the email addresses. See attached.

  10. #10
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting email addresses from emails (2003 S

    Thanks for the clarification.

    > Set selSelItems = Outlook.ActiveExplorer.Selection is where the items you have manually selected (by clicking, Shift-clicking or Ctrl-clicking for multiples)

    That's good; I don't want it to loop through all the mail folders, just the one selected.

    > Set colContacts = Outlook.Session.GetDefaultFolder(olFolderContacts) .Items selects the default contacts folder

    I'm not sure which is the default contacts folder. Is that the root 'Contacts' folder? If it's the root folder, that would be OK as there is nothing in that.

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting email addresses from emails (2003 S

    Yes, the default folder is the root folder.

    I have not had time to look further at the issue of getting the underlying SMPT address from the SentOnBehalfOfName - Jefferson may get to it sooner.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Extracting email addresses from emails (2003 S

    From your other post it appears that you need to extract the "SentOnBehalfOfName". The code I provided does not look for that, but it would be easy to obtain; I may have to see if there any complications to getting the SMPT address from the Sender line, it may have to be parsed.

    > Set colContacts = Outlook.Session.GetDefaultFolder(olFolderContacts) .Items

    selects the default contacts folder, not the folder where the messages reside. The code doesn't extract anything, as the text states it adds the address as a Contact in your default Contacts Folder.

    The line

    Set selSelItems = Outlook.ActiveExplorer.Selection

    is where the items you have manually selected (by clicking, or Shift-clicking or Ctrl-clicking for multiples) in a Message Folder are brought into the code as the Items to be acted on. As written, the code is not intended to loop through 300 folders, it's intended to work on manually selected items in a folder. Looping through multiple folders can be done but it is additional work I won't be able to get to, and such code would have to examine every item in the folders, so it might take some time to run.
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Extracting email addresses from emails (2003 S

    Outlook only exposes the Sent-on-behalf-of name and not the address. You might need to move into Extended MAPI, using the Redemption library and parsing the message headers yourself. Actually, you might be able to retrieve the headers without Redemption using CDO; the only reason to prefer Redemption is the security prompts. Try the attached revised version, which retrieves the "Sender", "From" and "Reply-To" headers (if present). You can examine the headers for messages of interest to see whether you want to collect others and put them in the strFieldList, delimited as in the current code. Please note that I used case sensitive comparisons, so there is a small chance that some nonconforming messages might slip past.

  14. #14
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    San Jose, California, USA
    Posts
    290
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extracting email addresses from emails (2003 S

    Thanks for the revised code. I was able to run it successfully.

    > Outlook only exposes the Sent-on-behalf-of name and not the address.

    That's not correct, or at least it does not seem to be. If you look at an email it shows the address in the from field, enclosed in angle brackets after the name. It may be all one field but it's parsable. I was able, using a combination of notepad and Excel, to extract the email addresses I wanted. However, I don't seem able to import them back into an Outlook contacts folder. I exported them to an Excel spreadsheet with two columns, one for name and one for email address. When I try to import this back into Outlook, the 'Map custom fields' button is greyed out, so I can't tell Outlook where to put the data. If I click on 'Continue' without mapping custom fields, Outlook just sits there for ever until I click 'Cancel'.

  15. #15
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Extracting email addresses from emails (2003 S

    Sorry, when I used the word "exposes," I meant as a property of the MailItem object, as part of the Outlook object model.

    I have no idea what is going wrong with your import. If you can't map your columns, try using Outlook's exact field names in the first row. To get the field names, export some sample data.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •