Email: extracting & displaying *content* of attachments

[Koxinga]

Something very specific I'd like to do, and I'd like to do exactly this (so never mind all the inevitable "why don't you just [do something completely different]"):

I'd like to take a bunch of Outlook emails and somehow save or export them in bulk, to a single file

such that the *content* of an attached file in each message is displayed side by side with the email header & body text.

The reason I'd like to do this is I receive hundreds of automatically generated emails from a certain news service, and each email provides a link to the news source -- but only in the form of a very short, one line attachment. The link doesn't appear in the body text, so if I want to see the source I have to go to each individual email and open the individual attachment.

But I'd like to do it in bulk for 100s of emails at once.

Possible?

[Koxinga]

and thanks in advance, and hope I didn't sound snarky

[DMC]

It's certainly possible if you're handy with programming. The Outlook Object Model should expose just about everything you need.

[jdc]

I had a need to receive, send, and parse emails from within Access, and rather than figure out how to beat Outlook into submission, I just spent $78.40 for a single computer license for MailBee SMTP and MailBee POP3 components and it was a lot easier to write some VBA code using the examples they included.

This was in 2008 so prices may have changed, and I think I got a discount because the programming was for a non-profit. They did not have any problem with my installling the component on 2 computers, mine (for writing the application) and the one at the Church for actually running it

The company is afterlogic www.afterlogic.com

It looks like they only show the Developer License pricing on their website, but you might ask them if they still sell a "single computer" option.

[Number]

As DMC said, this is likely possible with VBA or VBScript. A few questions, though:

Are all of these emails in a single folder? If not, is there an easy way to identify which ones to include?

What format do you want the output in - plain text, or in a Word doc with all the formatting preserved?

Are the attachments .txt files or something else?

When you say "side by side with the email header & body text", would it be acceptable if the link appeared above or below the message?

[Koxinga]

Originally Posted by Number

As DMC said, this is likely possible with VBA or VBScript. A few questions, though:

Are all of these emails in a single folder? If not, is there an easy way to identify which ones to include?

What format do you want the output in - plain text, or in a Word doc with all the formatting preserved?

Are the attachments .txt files or something else?

When you say "side by side with the email header & body text", would it be acceptable if the link appeared above or below the message?

Hi, yes, they're all in a single folder, and a plain text file is fine. Ideally I'd like one big text file containing the email subject lines and attachment text of everything in the folder; it doesn't have to be formatted a certain way just as long as it's consistent.

(After I have this text file in hand, I plan to copy and paste it to a Word file where I can run a macro to sort it out the way I want.)

[Number]

Create a new Outlook macro named EmailExport, edit it, and paste in the text below. Customize the value of the strOutput variable on line 6 with the location of the output file to create.

When run, it will prompt for the folder to run against. It will then create a comma-delimited text file with the emails' subjects and attachment contents. The .csv can be opened in Excel and should be easier to manipulate that way than in Word or Notepad.

Sub EmailExport()
    Const ForReading = 1
    Const ForWriting = 2
    Const TriStateUseDefault = -2
    
    strOutput = "c:\output.csv"
    strTempFile = Environ("TEMP") & "\EmailAttachment.txt"
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objOutput = objFSO.OpenTextFile( _
        strOutput, ForWriting, True)
    objOutput.WriteLine "Subject,Attachment Text"
    
    Set objNS = Application.GetNamespace("MAPI")
    Set objFolder = objNS.PickFolder

    For Each objItem In objFolder.Items
        strAttachText = ""
        
        If objItem.Class = olMail Then
            If objItem.Attachments.Count > 0 Then
                Set objAtt = objItem.Attachments(1)
                
                If LCase(Right(objAtt.FileName, 4)) = ".txt" Then
                    objAtt.SaveAsFile strTempFile
                    Set objTempFile = objFSO.OpenTextFile( _
                        strTempFile, ForReading, False, TriStateUseDefault)
                    strAttachText = objTempFile.ReadAll
                    objTempFile.Close
                    objFSO.DeleteFile strTempFile, True
                End If
            End If
            
            objOutput.WriteLine Chr(34) & objItem.Subject & Chr(34) & _
                "," & Chr(34) & strAttachText & Chr(34)
        End If
    Next
    
    objOutput.Close
    MsgBox "Output written to " & strOutput
End Sub

[Koxinga]

Number, thank you so much! That's *exactly* what I was hoping for, and what you put together was past my own capabilities. I'm inspired to learn programming now!