VBA Export Outlook Non-HTML Email Body into Excel

Question

I want to export outlook NON-HTML Email Body into excel with a click of a button inside excel. Below are my codes. Appreciate if anyone could assist me on this.

This is the code that I use to print the plain text email body but I get a lot of unwanted text

sText = StrConv(OutlookMail.RTFBody, vbUnicode)

Range("D3").Offset(i, 0).Value = sText

I did tried this but it prompts run-time error '1004' Application-defined or object-defined error It works on body with HTML tags though.

Range("D3").Offset(i, 0).Value = OutlookMail.Body

This is the structure of my email folders

Below are my complete vba codes

Sub extract_email()

Dim OutlookApp As New Outlook.Application
Dim Folder As Outlook.MAPIFolder
Dim OutlookMail As MailItem

Dim sText As String
Dim i As Integer

Set myAccount = OutlookApp.GetNamespace("MAPI")
Set Folder = myAccount.GetDefaultFolder(olFolderInbox).Parent
Set Folder = Folder.Folders("Test_Main").Folders("Test_Sub")

i = 1

Range("A4:D20").Clear

For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime >= Range("B1").Value And OutlookMail.SenderName = "Test_Admin" Then
        Range("A3").Offset(i, 0).Value = OutlookMail.Subject
        Range("A3").Offset(i, 0).Columns.AutoFit
        Range("A3").Offset(i, 0).VerticalAlignment = xlTop
        Range("B3").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("B3").Offset(i, 0).Columns.AutoFit
        Range("B3").Offset(i, 0).VerticalAlignment = xlTop
        Range("C3").Offset(i, 0).Value = OutlookMail.SenderName
        Range("C3").Offset(i, 0).Columns.AutoFit
        
        
        sText = StrConv(OutlookMail.RTFBody, vbUnicode)
        Range("D3").Offset(i, 0).Value = sText
        Range("D3").Offset(i, 0).Columns.AutoFit
        Range("D3").Offset(i, 0).VerticalAlignment = xlTop
        
        i = i + 1
    End If
Next OutlookMail

Set Folder = Nothing

End Sub

I did, it only works on email body with HTML tag. Plain text body will result in error 1004. — Anthony White
maybe this will help Outlook VBA— Some MailItem Properties return values, others do not — Yane
@Anthony White will it work if you first sub writes body to a plain text file and second sub imports text file to excel. — skkakkar

Tony Dallimore Tony Dallimore · Accepted Answer · 2019-03-11T19:12:35

An email can have several bodies or none. Outlook recognises text, Html and RTF bodies. No email I have examined in recent years contained a RTF body. Once it was the only option if you wanted to format your message. Today, Html and CSS offer far more functionality than RTF and I doubt if any smartphone accepts RTF. I am surprised you are receiving an email with a RTF body; my guess it is from a legacy system.

Ignoring RTF, if an email has an Html body, that is what is displayed to the user. Only if there is no Html body, will the user see the text body.

Of the emails I have examined, all have both a text and an Html body. Almost all of those text bodies are the Html body with every tag replaced by a carriage return and a linefeed. Since CRLF as newline is a Windows convention, I suspect if an email has no text body, Outlook is creating one from the Html body for the benefit of macros that want to process a text body. If my theory is correct, Outlook is not doing the same for an email that only contains a RTF email. Hence, you have a text body if there is an Html body but not if there is a RTF body.

The specification for Microsoft’s RTF is available online so you could research the format if you wish. If you search for “Can I process RTF from VBA?”, you will find lots of suggestions which you might find interesting.

Alternatively, the example RTF body you show looks simple:

{string of RTF commands{string of RTF commands}}
{string of RTF commands ending in “1033 ”text\par
text\par
text\par
}

If you deleted everything up to “1033 ” and the trailing “}” then replaced “\par” by “”, you might get what you want.

I have issues with your VBA. For example:

Not all items in Inbox are MailItems. You should have:

For Each OutlookMail In Folder.Items
  If OutlookMail.Class = olMail Then 
    If OutlookMail.ReceivedTime ... Then
      :   :   :
    End If
  End If

You do not need to format cells individually. The following at the bottom would handle the AutoFit and vertical alignment:

Cells.Columns.Autofit
Cells.VerticalAlignment = xlTop

Your code operates on the active worksheet. This relies on the user having the correct worksheet active when the macro is started. You should name the target worksheet to avoid errors.

VBA Export Outlook Non-HTML Email Body into Excel

3 Answers