Get HTML Email body into IE document object

Question

Get HTML Email body into IE document object

fredmartinmaine (IS/IT--Management)

(OP)

30 Dec 19 18:06

I have a need to open Outlook emails, which contain HTML tables. I need to parse some data out of the tables.

I have the email in an object, and can access the HTML body:

Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItemFromTemplate(EmailFile)

MsgBox(objEmail.body)
MsgBox(objEmail.htmlbody

Unless I'm headed down the wrong path, I'd like to get that htmlbody into an IE.document so I can use some of the GetElement methods to retrieve the data I need.

Can someone point me in the right direction, or to some documentation?

Thanks
Fred

strongm (MIS) · Answer 1 · 2019-12-30 23:33:03

Use the lighter weight MSHTML library, and then something like:

CODE

    Set HTMLDoc = CreateObject("HTMLFile")
    HTMLDoc.body.innerHTML = objEmail.htmlBody

You should then be able to parse HTMLDoc using getelement methods

fredmartinmaine (IS/IT--Management) · Answer 2 · 2020-01-02 20:27:29

Thanks. I started in with it but got some unexpected errors, could have been my poor setup of the example. But, I'm temporarily assigned to work on a different project but will be back on this soon. I'll post back.
Fred

fredmartinmaine (IS/IT--Management) · Answer 3 · 2020-01-06 20:36:15

I'm back at this now. Here's what I have in total:

CODE -->

EmailFile2 = "H:\Projects\Customer\Loss_Control_Inspection.msg"

Set objOutlook = CreateObject("Outlook.Application")
Set objEmail = objOutlook.CreateItemFromTemplate(EmailFile2)

Set OutFile = objFSO.CreateTextFile("H:\Projects\Customer\TEST2.html",TRUE)
OutFile.writeline(objEmail.htmlbody)

Set HTMLDoc = CreateObject("HTMLFile")
HTMLDoc.body.innerHTML = objEmail.htmlBody

I get the runtime error: Object required: 'HTMLDoc.body' 800A01A8

TEST2.html was just to prove that I was looking at the right data. I found several cases of the same error in forums on the web, but no solutions. Do I need to invoke MSHTML in some way?

Thanks.

strongm (MIS) · Answer 4 · 2020-01-06 22:12:14

Possibly my fault. I tested the code in VBA rather than VBScript because of the useful IDE - but sometimes VBScript works a bit differently to VBA.

Try

HTMLDoc.Write objEmail.htmlBody
HTMLDOC.Close

Instead of

HTMLDoc.body.innerHTML = objEmail.htmlBody

fredmartinmaine (IS/IT--Management) · Answer 5 · 2020-01-07 00:54:20

No worries. In the meantime I stumbled upon these two things which both work although I can't say I understand why. One of them matches yours:

CODE -->

Set document = CreateObject("htmlfile")
document.write objEmail.htmlBody

CODE -->

Set oHTMLDoc = CreateObject("htmlfile")
oHTMLDoc.open
oHTMLDoc.close
oHTMLDoc.body.innerHTML = objEmail.htmlBody

Thanks.

fredmartinmaine (IS/IT--Management) · Answer 6 · 2020-01-07 14:35:30

I'm afraid someone will have to hit me over the head with an example; I'm struggling with usage and I'm still getting errors like 'object required' and 'object doesn't support this property or method'.

CODE -->

<table>
  <tr>
    <td class='text' style='margin-left:10px; margin-top:5px; vertical-align:top; width:300px'><b>Policy/Quote#:</b> PMP0975891</td>
	<td class='text' style='margin-left:20px; margin-top:5px; vertical-align:top; width:180px'><b>Type of Risk: </b></td>
	<td class='text' style='margin-top:5px; vertical-align:top; width:500px'>Mixed types including Office</td>
  </tr>
</table>

Assume the above fragment in the html body of that email. I'm trying to extract the policy number "PMP0975891".
If I can extract the string "<b>Policy/Quote#:</b> PMP0975891" then I can deal with it.

I figured I'd get a list of elements with class 'text' and from that, find the one containing the value "Policy/Quote#:".

Is that reasonable? In any case, right out of the gate I'm having trouble getting the list of objects. Samples I've found online aren't helping me. I've tried stuff like:

CODE -->

Set EList = HTMLDoc.body.innerHTML.getElementsByClassName("text")
Set EList = HTMLDoc.innerHTML.getElementsByClassName("text")
Set EList = HTMLDoc.getElementsByClassName("text")
Set EList = HTMLDoc.body.getElementsByClassName("text")

Thanks in advance.

strongm (MIS) · Answer 7 · 2020-01-08 07:45:50

<grrrr> Seem to be running into challenges with the way VBScript handles HTMLDocument objects

fredmartinmaine (IS/IT--Management) · Answer 8 · 2020-01-08 18:37:58

Equally frustrated. For what it's worth here's a code snippet that I found online that actually produces some results without errors. Still feels a long way from where I need to be.

CODE -->

Set document = CreateObject("htmlfile")
document.write "<html><head><title>test</title></head><body><div id='yo'>hello world</div><table><tr><td>Col-A</td><td>Col-B</td></tr></table></body></html>"
MsgBox document.GetElementById("yo").innerHTML
Set allresults = document.getElementsByTagName("td")
For Each result In allresults
	msgbox("got an object")
	msgbox(result.innerhtml)
Next
MsgBox("done")

Come Join Us!

Posting Guidelines

Jobs

Get HTML Email body into IE document object