Expanding email addresses in mass

[Scott Tiger]

Hello,

At work we have an email account that is nothing but a service account - a real person doesn't actually use it. The reason this email account exists is because when someone likes on a link on a webpage to request being added to a mailing list an email is sent to this box.

My problem is there are a growing number of emails being sent to this address. What I would like to do is have the ability to read in all the email addresses from all the emails and add them to a distribution list (best option) or a spreadsheet (workable).

I have no control or even persuasion over of how the website works. I "think" the version of exchange we're using is 2000 (but I have no access to it) and the version of Outlook is 2002. The desktop in question is Windows XP.

If someone could come up with a nice client-side way to handle this I'd really appreciate it.

Thanks,
Scott

[vass0922]

One option would be to write up a small script

Would do something like

Probably use CDO

Logon to mailbox xyz
Get collection of mail in inbox
Get body of each message, and look for email address (assuming since sent email is from script its a fixed text so should be easy to parse)
Output to a semicolon delimited text file so you can easily cut and paste into the DL.

Is this email being sent to a public folder, or an actual mailbox?

If its being sent to a public folder (if you have rights) you can put an event on that folder that each time a message is received you can run a script that will do this work. Otherwise you'll just have to double click a script on occasion.
You won't be able to schedule the script (without help) due to wonderful Outlook security that <insert profanity> with admins.

[Scott Tiger]

Thanks Vass - I was hoping you could help me out with.

I'd actually like to be able to run this manually instead of letting it run in the background.

I will either have access to the mailbox or I'll be sitting close to the guy (I can watch him do it) who will so rights won't be a problem. We'll probably only do it once a month or so.

I'm pretty sure this is an actual mailbox but now that you've mentioned it I'm not quite sure. I'll have to check into that.

Any ideas on where I can get help with the script? And what scripting language are you refering to? I'd really like to stick with something native to Windows (vbscript) rather than installing something like perl if at all possible.

Any more info you've got would help me out - I'm just pressed for time here and the less I have to use Google the quicker I'll get this done.

Again - many thanks for your input.

-Scott

[vass0922]

here is a quick draft of a VBScript
This will logon to a mailbox (it will prompt for a profile)
It will ONLY look for UNREAD emails
It will then export the FULL body into a text file (name and folder are constants in top of script).
It will probably need some cleaning up to get just the email address but I don't know the format of the email you're receiving so this is your rough draft.

Dim objSess, objInbox, objMsgColl, objMsgFilter
Dim objMess  ' individual message processed in loop
Dim objFile
Dim objFSO
Dim sBodyString
Const DEFAULT_DIR = "C:\"
Const DEFAULT_FILENAME = "Filename.txt"

Set objSess = CreateObject ( "MAPI.Session" )
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.CreateTextFile(DEFAULT_DIR & DEFAULT_FILENAME, True)

objSess.Logon ' assume valid session for this example

Set objInbox = objSess.Inbox

If objInbox Is Nothing Then
    MsgBox "Invalid IPM Inbox from session"
    'Exit Function
End If

Set objMsgColl = objInbox.Messages ' get Inbox’s messages collection 

' ( ... then validate the messages collection before proceeding ... )
Set objMsgFilter = objMsgColl.Filter

' ( ... then validate the message filter before proceeding ... )
objMsgFilter.Unread = True ' filter for unread messages
sBodyString = ""

' Message filter is now specified; ready for display loop
For Each objMess in objMsgColl ' performs loop, Sets each objMess
    sBodyString = sBodyString & objMess.Text & ";"
Next
objFile.WriteLine sBodyString
objSess.LogOff
WScript.Echo "Completed"

Set objInbox = Nothing
Set objMsgColl = Nothing
Set objMsgFilter = Nothing
Set objSess = Nothing
Set objFSO = Nothing
Set objFile = Nothing

Let me know how it goes!

[Scott Tiger]

That works really well Vass - the only trouble is it's giving me the contents of the email and not the sender's address.

I'm not much with VBScript but you've got me 95% of the way there! I'll see what else I can do with it.

Thanks again,
Scott

[vass0922]

OH I thought the email being sent would be from a generic source like an ASP page.

Unfortunately getting the FROM address (as I found on a previous project) is a REAL pain in the arse.
You can't get the Sender address from MAPI
So at that point you have to use CDO
(MS is retarded)

I have some existing code let me see if I can put it together into this script.

[Scott Tiger]

Ah.. that's what I was afraid of.

I know I'm showing my ignorance here but what's CDO?
(Common Data Object??)

[vass0922]

Collaborative Data Objects

Specific to Outlook (shocked aren't ya?)

Actually CDO isn't installed by Default with Outlook, so you have to go back through the install and add the CDO component

I played with it last night, but wasn't able to get it working
Its QUITE a clusterF...errrr ya lol

[Scott Tiger]

<sigh> Thanks, but that's what I was afraid of. </sigh>

I also appreciate the info you've given me. At least now I know where to go from here.

[vass0922]

man I hate Outlook coding

Note: If you ever get asked to do a Custom outlook form

RUN FOR YOUR LIFE!

    Dim oOutlook 
    Dim oNs 
    Dim oParentFolder 
    Dim oInboxFldr 
    Dim objFolder 
    Dim colFolders 
    Dim objSession 
    
    Dim iMsgCount 
    Dim objCDOMessage 
    Dim oMessage 
    Dim blnRead 
    Dim olFolderInbox
    Dim sBodyString
    Const DEFAULT_DIR = "C:\"
    Const DEFAULT_FILENAME = "Filename.txt"
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFile = objFSO.CreateTextFile(DEFAULT_DIR & DEFAULT_FILENAME, True)
    olFolderInbox = 6

    On Error Resume Next
    Set oOutlook = CreateObject("Outlook.Application")
    Set oNs = oOutlook.GetNamespace("MAPI")                      ' Instantiate the Outlook namespace
    Set oInboxFldr = oNs.GetDefaultFolder(olFolderInbox)         ' get the INBOX folder so we can find its parent
    Set objSession = CreateObject("MAPI.Session")                ' Instantiate the MAPI Session object
    objSession.Logon , , False, False                            ' Login to the MAPI session

    Set oParentFolder = oInboxFldr.Parent                        ' Get the root folder for this mapi session
    Set colFolders = oParentFolder.Folders                       ' Instantiate the root folder collection
    For Each objFolder In colFolders                             ' Loop through all of the folders
        WScript.Echo objFolder.Name
        If objFolder.Name = "Inbox" Then                     ' If the current folder is the quarantine folder - grabit
            Exit For ' we got our object lets continue!
        End If
    Next

    For Each oMessage In objFolder.Items                          ' Loop through all of the messages
        If oMessage.MessageClass = "IPM.Note" Then                ' make sure we got a IPM.Note!!
        ' We need a CDO Message object to get the email address
            Set objCDOMessage = objSession.GetMessage(oMessage.EntryID, oMessage.Parent.StoreID)
            blnRead = objCDOMessage.UnRead                        ' Store the UNREAD value
            If Err.Number = 0 And blnRead Then
               sBodyString = sBodyString & objCDOMessage.Sender.Address & ";"
            End If
        End If
    Next                                                 ' NEXT MESSAGE
    objFile.WriteLine sBodyString
    ' Lets be good little developers and clean up our mess
    Set oMessage = Nothing
    Set oInboxFldr = Nothing
    Set oNs = Nothing
    Set oOutlook = Nothing
    Set objCDOMessage = Nothing
    Set objSession = Nothing
    Set oParentFolder = Nothing
    Set colFolders = Nothing

Good luck!