Writing email messages to flat files in Outlook with VBA

Question

I have written a VBA app that opens a folder in outlook and then iterates through the messages. I need to write the message bodies (with some tweaking) to a single flat file. My code is as follows...

Private Sub btnGo_Click()
    Dim objOutlook As New Outlook.Application
    Dim objNameSpace As Outlook.NameSpace
    Dim objInbox As MAPIFolder
    Dim objMail As mailItem
    Dim count As Integer

    Set objNameSpace = objOutlook.GetNamespace("MAPI")
    Set objInbox = objNameSpace.GetDefaultFolder(olFolderInbox)
    count = 0

    For Each objMail In objInbox.Items
       lblStatus.Caption = "Count: " + CStr(count)
       ProcessMailItem (objMail)
       count = count + 1
    Next objMail

  End If
End Sub

The part in question is "ProcessMailItem". As I am not overly concerned with performance at this stage so the very inefficent "open, append, close" file methodology is fine for this example.

I know I could spend some time looking up the answer with google but I checked here first and there was no good answers for this. Being a fan of Stackoverflow I hope that putting this up here will help future developers looking for answers. Thanks for your patience.

Cabe

Answer 1

You can get away with writing to a file without using any objects, just using the built in VBA file tools:

Open "C:\file.txt" for append as 1
Print #1, SomeStringVar
Close #1

Vernal

I chose this answer because it is very VBA centric. Eric Ness' answer is also valid!! – Cabe

Answer 2

If you don't mind re-opening the output file each time you append some text, then this should work.

Private Sub ProcessMailItem(objMail As MailItem)

    Dim fso As New FileSystemObject
    Dim ts As TextStream

    Set ts = fso.OpenTextFile("C:\Outputfile.txt", ForAppending, True)

    ts.Write(objMail.Body)

    ts.Close()
    Set ts = Nothing
    Set fso = Nothing

End Sub

You'll also need to add a reference to the Microsoft Scripting Runtime library. This has FileSystemObject in it.

Goldarned

Answer 3

You also have to take care of the security popup "trying to access email addresses" which is covered in Outlook "Object Model Guard" Security Issues for Developers

Public Sub ProcessMailItem(objMail As MailItem)
Dim FSO As New FileSystemObject
Dim ts As TextStream
Dim loc As String
Dim subject As String
Dim strID As String
' per http://www.outlookcode.com/article.aspx?ID=52
Dim olNS As Outlook.NameSpace
Dim oMail As Outlook.MailItem

strID = MyMail.EntryID
Set olNS = Application.GetNamespace("MAPI")
Set oMail = olNS.GetItemFromID(strID)
subject = oMail.subject
Set ts = FSO.OpenTextFile("C:\Documents and Settings\tempuser\My Documents\EMAILS\" + subject, ForAppending, True)
ts.Write (oMail.Body)
ts.Close
Set ts = Nothing
Set FSO = Nothing
Set oMail = Nothing
Set olNS = Nothing

End Sub

Sokil

there's still some issue with the path in the OpenTextFile. i don't get consistent results to that path vs a shorter path with no spaces. – Sokil