CODEWITHSUNDEEP
excelvbaoutlook
Anthony White
Anthony White

Reputation: 44

VBA Export Outlook Non-HTML Email Body into Excel

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

Output Example

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

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

Upvotes: 0

Views: 1358

Answers (3)

Anthony White
Anthony White

Reputation: 44

Thank you for all the answers, I have improved my codes and I have figured out the issue. It is due to the "=" symbols that is generated from the script and sent to my email. Excel treat the "=" sign differently that's why it didn't allow me to extract properly. Once I changed the "=" symbol to "#" symbol, I can extract the email normally using:

OutlookMail.Body

Upvotes: 0

Tony Dallimore
Tony Dallimore

Reputation: 12403

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.

Upvotes: 1

Dmitry Streblechenko
Dmitry Streblechenko

Reputation: 66215

Excel does not not support RTF directly, but plain text MailItem.Body should work fine, I have never seen MailItem.Body raising an exception. Do you save the message first?

Upvotes: 0

Related Questions