VBA Export Outlook Non-HTML Email Body into Excel

vba outlook. trying to extract specific data from email body and export to excel
outlook vba copy email body to excel
how to import your outlook emails into excel with vba
export outlook email body text to excel spreadsheet
export email body from outlook to excel using python
export outlook emails to excel with date and time vba
extract data from email body to excel
outlook vba read email body

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

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?

How to export Outlook email body text to Excel spreadsheet?, How to export Outlook email body text to Excel spreadsheet? Export Outlook email body text to Excel spreadsheet with VBA code. Open the email, select the email body you want to export to Excel spreadsheet, and then press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window. In the Microsoft The following Visual Basic for Applications (VBA) example creates a new MailItem object and sets the BodyFormat property to olFormatHTML. The body text of the email item will now appear in HTML format. Sub CreateHTMLMail() 'Creates a new email item and modifies its properties.

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.

[Solved] Export Outlook Email body to Excel via VBA, I have now this code to export it to Excel. This code exports from the current Folder every Mail to Excel, but it pastes the body in 1 cell. I would to� Posted on December 18, 2013 by Vitosh Posted in VBA \ Excel With this article I will simply present you with a solution, combining HTML and VBA, exporting a graph from excel to MS Outlook e-mail. The HTML part is a little complicated and not very flexible, but anyhow – it is just an example that HTML can be incorporated in VBA.

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

How To Import Your Outlook Emails Into Excel With VBA, In this example I'm going to be importing from an Outlook folder called Inbox/Net Sales The main text body from emails will be imported just below this cell. Here is the Any way to not go into the inbox folder to begin with? Mail Sheet, Sheets, Range or workbook as PDF in Excel 2007. Zip the Active Workbook and mail it with Outlook . Outlook object model (body) Mail range as picture in the body of Outlook mail. Mail one worksheet in the body of the mail . Mail Range/Selection in the body of the mail. Mail selection, range or worksheet in the body of a mail with

Learn Excel - Video 537 - VBA, You can purchase entire course or selective topics also on www.excelvbalover. com. Any Duration: 22:08 Posted: Dec 18, 2017 VBA - How to change font, color, etc in an email output (outlook) I currently work with a VBA that is set up to gather data then create and send an email in outlook but is wondering how could I alter the email output i.e. bold text, change font, color and size, underline, etc.

Get Data from Outlook into Excel worksheet, How to get data from Outlook emails into an Excel worksheet with VBA. More details Duration: 17:10 Posted: Mar 9, 2018 The following VBA example sets the current folder as the Inbox and displays the second mail message in the folder. In general, the order of mail messages in a folder is not guaranteed to be in a particular order.

Use a Macro to Copy Data in an Email to Excel, The code sample at copy-to-excel-regex.txt uses early binding. Late binding makes it easier to share macros as the references do not need to be set. the macro above with the macro at "Outlook VBA: Work with Open Item or Selected Item". To use, select a mail folder in Outlook then run the macro. And if you are using Outlook 2007, please click Message > Other Actions > Edit Message to display the Options tab, and then click HTML under Options. 2. You can also view the source code of html email body by opening the message and put the cursor at the message body then right click, choose View Source from the right-clicking menu.

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