PDA

View Full Version : Extract smtp email addresses from Outlook



itsonlyme4
05-24-2006, 07:50 AM
Hi,

I need to know if there is a way to programmatically scan all Folders (INBOX, pst's whatever) and extract SMTP email addresses in the FROM column to an excel file? There are probably alot of duplicates as well and I'd want to deal with that as well (NOT bring over duplicates)

Any help would be greatly appreciated.
Running Exchange 2000 and Outlook 2003 sp1

Killian
05-26-2006, 06:40 AM
Hi and welcome to VBAX :hi:

yup, you can loop through each mail item in a designated folder and check it's properties.
Here's an example where this is done with the Inbox. I also set up a new workbook to send the results to. I use Excel's Find method to check for duplicatesSub GetSMTPaddys()

Dim ns As NameSpace
Dim fldr As MAPIFolder
Dim m As MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSht As Object
Dim r As Long

'set up an Excel sheet for the results
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add
Set xlSht = xlWB.Sheets.Add
xlSht.Name = "SMTPs"

'target the inbox
Set ns = Application.GetNamespace("MAPI")
Set fldr = ns.GetDefaultFolder(olFolderInbox)

r = 1 'used as excel row index
For Each m In fldr.Items 'loop thru the mail items
If m.SenderEmailType = "SMTP" Then
'check it's not already in col A of our results sheet
If xlSht.Columns(1).Find(m.SenderEmailAddress, LookIn:=xlValues) Is Nothing Then
xlSht.Cells(r, 1).Value = m.SenderEmailAddress
r = r + 1 'increment the row index counter
End If
End If
Next m

'show the results
xlApp.Visible = True

End Sub

itsonlyme4
05-26-2006, 08:24 AM
Thank you so much for the reply.. It worked exactly like you suggested that it would !!!!!

I ultimately would like to be able to have the user choose the folder to run it against and perhaps add the Subject line and received date.

I have some other inhouse code that does something like that - with a msg box - I'm going to try and pull that code out and add it to yours.. unless you have a quick and dirty way to do what I'm looking for.. and THANKS AGAIN!!!!!!!!!!!!!!!!

Killian
05-26-2006, 03:09 PM
Well one way to do it would be to get the set the target folder to the Active explorer's current folder - i.e. the folder the user has selected. The syntax seems a litle strange: going from the namespace session to the folders collection of the first "set" of folders. The following code worked out ok for me but you have a number of pst's set up it might get a little more complicated getting down to it.
You also might have to check if the item is valid (a mailitem) before you try to get it's propertiesSet fldr = ns.Session.Folders(1).Folders(ActiveExplorer.CurrentFolder.Name)I think you might be able to get somewhere with that

itsonlyme4
05-27-2006, 02:18 AM
I tried your suggestion and here is the modified code:


Sub exporttest()

Dim ns As NameSpace
Dim fldr As MAPIFolder
Dim m As MailItem
Dim xlApp As Object
Dim xlWB As Object
Dim xlSht As Object
Dim r As Long

'set up an Excel sheet for the results
Set xlApp = CreateObject("Excel.Application")
Set xlWB = xlApp.Workbooks.Add
Set xlSht = xlWB.Sheets.Add
xlSht.Name = "SMTPs"

'target the inbox
Set ns = Application.GetNamespace("MAPI")
' Set fldr = ns.GetDefaultFolder(olFolderInbox)
Set fldr = ns.Session.Folders(1).Folders(ActiveExplorer.CurrentFolder)

r = 1 'used as excel row index
For Each m In fldr.Items 'loop thru the mail items
If m.SenderEmailType = "SMTP" Then
'check it's not already in col A of our results sheet
If xlSht.Columns(1).Find(m.SenderEmailAddress, LookIn:=xlValues) Is Nothing Then
xlSht.Cells(r, 1).Value = m.SenderEmailAddress
r = r + 1 'increment the row index counter
End If
End If
Next m

'show the results
xlApp.Visible = True

End Sub

When I run it, I get this error:

run-time error -1409043995 (a7220005)':
Method'Folder' of object 'MAPIFolder' Failed

So... when I changed this line
' Set fldr = ns.GetDefaultFolder(olFolderInbox)
to this:
Set fldr = ns.Session.Folders(1).Folders(ActiveExplorer.CurrentFolder)

Is there another something else missing.. something that needs to be defined? been trying to figure this out all morning and I'm not quite getting it.. :think:

Killian
05-27-2006, 07:43 AM
I have another look at this and I think I've got the right method now...
"ActiveExplorer.CurrentFolder" will give the currently selected folder.
To get the reference to the folder directly (without having to drill down though any sub-folders) you can use "GetFolderFromID".

This seems to work quite wellSet fldr = ns.GetFolderFromID( _
ActiveExplorer.CurrentFolder.EntryID, _
ActiveExplorer.CurrentFolder.StoreID)