• 0

Need some help with VBA


Question

Hi,

I need some help with taking all emails in a folder (outlook 2007/exchange) and write them out to a local text file.

Ideally the output would be something like this:

begin file-----

Sender: sender@domain.com

Subject: check this out

Body: Hello there!

--------------------

Sender: sender2@domain2.com

Subject: another email

Body: Hello again

end file-------

...and so on

Anyone have anything that does something like this?

Thanks!

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Outlook does provide access to its API in VBA. With VBA you can get access to Outlooks Namespace objects, which there is only 1, and its called MAPI.

The following example will read all the emails in the inbox and write them out to a file.

Sub GetEmails()
	Dim path: path = "C:\Test.txt"
	Dim ns As NameSpace
	Dim inbox As MAPIFolder
	Dim item As Object
	Dim file, num

	Set ns = GetNamespace("MAPI")
	Set inbox = ns.GetDefaultFolder(olFolderInbox)

	If (inbox.Items.Count > 0) Then
		num = FreeFile()
		Open path For Output As num
			For Each item In inbox.Items
				With item
					Write #num, "-----------------------------"
					Write #num, "From: " & .Reply.Recipients.item(1).Name
					Write #num, "Subject: " & .Subject
					Write #num, "Body: " & .Body
					Write #num, "-----------------------------"
				End With
			Next
		Close #num
	End If


	Set item = Nothing
	Set inbox = Nothing
	Set ns = Nothing
End Sub

If you want other folders, you're gonna have to either traverse the folder's Folders property until you find the one you want, or if you know its entryID, you can use GetFolderFromID

God its been ages since I've done VBA!

Link to comment
Share on other sites

  • 0

wow, thats pretty fantastic!

Way better than what I was working on

seen here:

Public Sub SaveIt()

Dim objMapiName As Outlook.NameSpace
Set myOlApp = CreateObject("Outlook.Application")
Set objMapiName = myOlApp.GetNamespace("MAPI")

Set Folder = objMapiName.GetDefaultFolder(olFolderInbox)
Set subfolder = Folder.Folders("test")

For i = 1 To subfolder.Items.Count

Set myItem = subfolder.Items.Item(i)

myItem.SaveAs "C:\report.txt", olTXT

Next

End Sub

which only did one at a time.

Anyway, if I wanted to modify your script to pull in another field from the header how would I go about it?

I am having an issue because I would like to pull in some tracking info I put into headers.

The fieldname is JC-ref3 . I tried adding

Write #num, "JC-ref3: " & .JC-ref3

but when I execute it makes it

Write #num, "JC-ref3: " & .JC - ref3

and then it doesn't work. (notice the extra spaces it puts in on its own!)

You've been a tremendous help already. I look forward to any more advice.

Link to comment
Share on other sites

  • 0

The code editor is formatting that for you, mainly because "JC-ref3" isn't a valid identifier.

I think what you need to do, is access the Headers property, unforetunately from what I've been reading, it doesn't break each one down into a set of key-value pairs.

Const CdoPR_TRANSPORT_MESSAGE_HEADERS = &H7D001E

Public Function GetHeaders(ByVal email As Object)
	Dim fields : Set fields = email.Fields

	Dim headers : headers = ""

	On Error Resume Next
		headers = fields.Item(CdoPR_TRANSPORT_MESSAGE_HEADERS).Value
	On Error Goto 0

	GetHeaders = headers
End Function

Hopefully you should just be able to then write out all the headers by modifying the original code:

Write #num, "Headers: " & GetHeaders(item)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.