Export Body to Excel

  • Thread starter Thread starter Jim
  • Start date Start date
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
 
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.
 
Hi Jim,I'm a beginner ,if you have problem about this, you can contact me by email:[email protected]

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:
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!!!
 
Back
Top