2

Well, I am very new to this VBA Code. I have found code to extract the email details in excel. This code is working for me the way I want but the only issue is the result that I am getting in excel for the Email Body. I have a table with relevant details in the email and I want the output in the same way in excel too for each individual emails.

Here is the code.


Sub GetFromOutlook()

Dim OutlookApp As Outlook.Application
Dim OutlookNamespace As Namespace
Dim Folder As MAPIFolder
Dim OutlookMail As Variant
Dim i As Integer

Set OutlookApp = New Outlook.Application
Set OutlookNamespace = OutlookApp.GetNamespace("MAPI")
Set Folder = OutlookNamespace.GetDefaultFolder(olFolderInbox).Folders("Personal")

i = 1

For Each OutlookMail In Folder.Items
    If OutlookMail.ReceivedTime >= Range("From_date").Value Then
        Range("eMail_subject").Offset(i, 0).Value = OutlookMail.Subject
        Range("eMail_date").Offset(i, 0).Value = OutlookMail.ReceivedTime
        Range("eMail_sender").Offset(i, 0).Value = OutlookMail.SenderName
        Range("eMail_text").Offset(i, 0).Value = OutlookMail.Body        
        i = i + 1
    End If
Next OutlookMail

Set Folder = Nothing
Set OutlookNamespace = Nothing
Set OutlookApp = Nothing

End Sub

Below is a sample of the email body content.

Sample of Email Body image

Currently, the result from the query is - The body message of the email is getting pasted in one particular cell.

Can someone please help

Ashvath Poojary
  • 21
  • 2
  • You'll need to do something like this (https://stackoverflow.com/questions/48151491/convert-html-table-to-excel-using-vba) - essentially, locating the table rows within the HTML of the email body (assuming it is an HTML body .... if it was Rich Text, this may not work). If you think about what you manually do if you transfer a table from an email to Excel - you would highlight ONLY the table, then copy and paste, and obviously Excel's got a bit of smarts to identify that what you're pasting can be converted to table cells. Your code needs to essentially do the same sort of thing – Craig May 03 '21 at 22:45
  • Oh, with reference to that question that I linked, I mean using code similar to the first answer ("client side solution") - NOT the OP's solution (which involves dragging and dropping an HTML file). The "client side solution" answer gets its HTML document from a URL - Set oHtml = oHtml4.createDocumentFromUrl("https://rasmusrhl.github.io/stuff/", "") - you'll just need to get your HTML from the body of the email message. – Craig May 03 '21 at 22:48

1 Answers1

0

I might be wrong, but it looks like your offsets aren't changing for each of the pieces of data. The offset is based on the current cell. If you write something into an offset (i,0) and then do the same again you will effectively be writing over the top of the initial data. This is why you end up with only the email text, because that's the last write operation.

Rob Kellock
  • 11
  • 2