Export Body to Excel


J

Jim

Will someone please advise how to export the body of an email to Excel

The emails are system generated and therefore in a standard format.
Once in Excel I will be able to parse the text using vba without any
problems but I am stumped with vba in Outlook.

Regards and many thanks

Jim Burton
 
Ad

Advertisements

K

Ken Slovak - [MVP - Outlook]

How do you decide which emails to export information from? Are they
selected, opened, or what?

Let's say it's based on an item being opened. To get the text information,
assuming you only want the plain text information, you access the Body of
that item:

Dim oMail As Outlook.MailItem
Set oMail = Application.ActiveInspector.CurrentItem

That will only work with email items and from within the Outlook VBA
project.

Then:

Dim strBody As String
strBody = oMail.Body

From there you have a string variable holding the item text, at that point
you open your Excel worksheet or work with an open worksheet and acess the
cell you want the text put into, using Excel code.
 
Joined
Apr 13, 2009
Messages
1
Reaction score
0
Hi Jim,I'm a beginner ,if you have problem about this, you can contact me by email:flion1985@sina.com

Sub exportMail()
'µ¼³öÓʼþΪexcel
'export mail in outlook to excel

Dim myOlApp As New Outlook.Application
Dim myMails As Object
Dim myMail As Object
Dim myNamespace As Outlook.NameSpace
Dim xlApp As New Excel.Application
Dim xlBook As Excel.Workbook
Set myNamespace = myOlApp.GetNamespace("MAPI")
Set myMails = myNamespace.GetDefaultFolder(olFolderInbox).Items
Set xlBook = xlApp.Workbooks.Add

'For Each myMail In myMails
xlBook.Sheets(1).Cells(1, 1).Value = myMails(myMails.Count).Body
xlApp.Visible = False
'myMails(myMails.Count) is your latest mail,if you want get all of your mails,you can use "for each myMail in myMails ¡*¡* next"
'and you need add some code by yourself , this function will slow down your speed.
' Next
xlBook.SaveAs fileName:="d:\mail.xls", FileFormat:=xlNormal _
, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
xlBook.Close
xlApp.Quit
Set myNamespace = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Set myMails = Nothing
Set myOlApp = Nothing

End Sub
 
Last edited:
Ad

Advertisements

Joined
Sep 19, 2013
Messages
1
Reaction score
0
Great post and it's the first that come close to really working!!! what code would you use to only export "highlighted" or "selected" email from any folder within Outlook?

Thanks again for the post, and I look forward to any reply's!!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top