Extracting email addresses from emails (2003 SP2)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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’.

Reply | Quote

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.

Reply | Quote

My fault. I omitted to check the box in front of the source file! As soon as I did that I was able to map the fields. I was able to separate the name and address into two fields by using Notepad’s “find and replace” to replace the right angle bracket with a blank, loading the file into Excel and parsing it using the left angle bracket as a delimiter. Voila! Thanks for your help.

Reply | Quote

I had separately determined that the underlying address of the person for whom the message is “SentOnBehalfOfName” is not exposed in the Outlook VBA model, but did not get back to the Lounge until now. As Jefferson states, you’ll need to use Redemption or CDO or another library to expose that information. Outlook is very annoying in regard to the limits of its object model, especially compared to othe Office apps, but MS probably limits the Outlook Object model to reduce the use of Outlook as a Spam and virus propagation engine.

Reply | Quote

“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!

DaveA I am so far behind, I think I am First
Genealogy....confusing the dead and annoying the living

Reply | Quote

FWIW, attached is some VBA code that will add Addresses to your default Contacts folder. It checks that the Contact exists by checking only for an exact match on the Contact FileAs Field. If the FileAs field match fails, a new Contact will be created, with the email address. If the Contact is found but the email address is not among the three records, and there is an unused address of the three available, the email address will be added to that Contact. The code is written for messages selected from the Folder view, not the individual open message item.

The code may need further testing (and doesn’t win any awards for elegance).

Reply | Quote

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. Does this have any impact on the code?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote