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.
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
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.)
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
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!