Export Mail Body with Formatting in Excel Sheet

[HaiderAliKhan]

Hi Excel Gurus,

I am trying to automate below task: -
User manually selects(searches for email basically) the email in Inbox and click on forward button and then copies the whole body (Ctrl+A) then (Ctrl+C) and pastes (Ctrl+V) in Sheet1 of workbook.

I am successful in referencing an email from Excel VBA to extract Subject, ReceivedTime but not able to extract mail body with HTML formatting. I am able to fetch mailbody with below line of code.

Sheet1.cells(1,1) = olMail.body, but that doesn't help as it copies unformatted text to Sheet1.

I need the body in the same format it gets pasted manually.Please help.

Thanks in advance.

 

[Worf]

Hi

Sub FromClip()
Dim oApp As Outlook.Application, oMapi As Outlook.MAPIFolder    ' Excel module
Dim oMail As Outlook.MailItem, odata As MSForms.DataObject


On Error Resume Next
Set oApp = GetObject(, "Outlook.Application")
If (oApp Is Nothing) Then Set oApp = CreateObject("Outlook.Application")
On Error GoTo 0


Set oMapi = oApp.GetNamespace("MAPI").Folders(1).Folders(1)     ' desired folder
Set oMail = oMapi.Items(oMapi.Items.Count)                      ' desired email
Set odata = New MSForms.DataObject
odata.SetText oMail.HTMLBody
odata.PutInClipboard
ActiveSheet.Paste                                               ' import formatted data
End Sub