How to copy entire e-mail body with same format in excel

[ThrottleWorks]

Hi,

I have an e-mail. This e-mail has few text lines and one InfoPath form in between.
This form has few text boxes and few questions with multiple options.

Users has filled text box with text value and selected one radio button for each question.

How do I export this e-mail to excel with original format.

The code I am using is exporting e-mail without text box and radio buttons.

How to I export e-mail with all the text boxes and radio buttons.
Can anyone please help me in this.

Public Sub PickOutlookFolder()

    Dim ObjOutlook As Object
    Dim MyNamespace As Object
    Dim i As Integer
    Dim j As Long
    Dim abody() As String
 
    Set ObjOutlook = GetObject(, "Outlook.Application")
    Set MyNamespace = ObjOutlook.GetNamespace("MAPI")
 
    Dim MacroBook As Workbook
    Dim MapSht As Worksheet
    Dim DataSht As Worksheet
 
    Set MacroBook = ThisWorkbook
    Set DataSht = MacroBook.Worksheets("Data")
 
    Dim objNS As Namespace
    Dim objFolder As Folder
    Dim strFolderPath As String
    Dim strEntryID As String
 
    Set objNS = Outlook.GetNamespace("MAPI")
    Set objFolder = objNS.PickFolder
 
    Dim olMail As Variant
 
    If TypeName(objFolder) <> "Nothing" Then
        For Each olMail In objFolder.Items
         
            abody = Split(olMail.Body, Chr(13) & Chr(10))
            For j = 0 To UBound(abody)
                DataSht.Cells(65000, 1).End(xlUp).Offset(1, 0).Value = abody(j)
            Next
        Next olMail
    End If
 
    strFolderPath = objFolder.FolderPath
    strEntryID = objFolder.EntryID
    MapSht.Range("E1").Value = strFolderPath
 
    Set objFolder = Nothing
    Set objNS = Nothing
End Sub

 

[Chihiro]

Copy and paste from Outlook manually and record process. That should give you basis of code needed.

If that doesn't work, you'll have to load .HTMLBody of email and read necessary data. Then write to Excel using code to reproduce content.

Though without sample, bit hard to give you specific help.

 

[ThrottleWorks]

Hi Chihiro sir, thanks a lot for the help.

 

[ThrottleWorks]

Hi Chihiro sir,

Before posting I tried doing same thing manually. But I am trying to do it with code.
I clicked on Forward button. Copied entire e-mail and pasted manually in the excel worksheet.
This way, I was able to get entire e-mail with text box and radio button with original format and alignment.

However not able to achieve same thing with coding. Sorry for not uploading. Not allowed.

 

[Chihiro]

I'd personally recommend just reading .HTMLBody, and then just fill values in Excel. It's much easier to work with.

If you must, copy as is. Try googling for copy .HTMLBody to clipboard (Office Clipboard) etc. You should find some sample codes out there.

Once you have what you want in the clipboard. Then it's simply matter of using .PasteSpecial "HTML" to paste it into Excel.

 

[ThrottleWorks]

Hi @Chihiro sir, thanks a lot for the help. Have a nice day ahead.