Extracting HTML tables with a specific format from Outlook to Excel with VBA

M

MrHelpcel

New Member
Joined
Jan 21, 2022
Messages
32
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
  6. 2011
  7. 2010
  8. 2007
  9. 2003 or older
Platform
  1. Windows
Hello all!

I have a question regarding extraction of HTML tables of a specific format (not your standard rows and columns table) from outlook, and to put the corresponding information in row format on an excel file.

Previously, with the help of Domenic on this forum (which I CANNOT THANK ENOUGH for all the help he provided me with) I managed to extract all the conventional HTML tables from a specific outlook subfolder. This resulted in a huge file with all the data recorded in rows per table (which was exactly what I wanted). Please see the link for reference: Outlook/VBA - Extracting tables from all emails in a specific folder

However, there are some HTML tables (sort of) that still haven't been successfully extracted. The reason I add the "sort of" part, is due to the nature of these tables. In the source, they are categorized as tables, but they are not your standard row and column tables. I will provide a screenshot of it (sorry for all the censoring, I don't want to get in trouble with the company!):

HKgrSd0l.jpg



Is there a way to obtain information from this table to into excel?


Would it be possible to write some VBA code to extract the information from the little "Enter text here" fields? I already have some code that helped me in retrieving the data from the previous tables (the standard row and column tables), perhaps it could be modified to grab this data instead?

VBA Code: 
Option Explicit

Sub demo()

    Dim oApp As Outlook.Application
    Dim oMapi As Outlook.MAPIFolder
    Dim oMail As Outlook.MailItem
    Dim oItem As Variant
    Dim oHTML As MSHTML.HTMLDocument
    Dim oTable As MSHTML.HTMLTable
    Dim oTables As MSHTML.IHTMLElementCollection
    Dim nextRow As Long
    Dim x As Long
    Dim y As Long
  
    On Error Resume Next
    Set oApp = GetObject(, "Outlook.Application")
    If oApp Is Nothing Then
        Set oApp = CreateObject("Outlook.Application")
        If oApp Is Nothing Then
            MsgBox "Unable to start Outlook!", vbExclamation, "Outlook"
            Exit Sub
        End If
    End If
    On Error GoTo 0
  
    Set oMapi = oApp.GetNamespace("MAPI").Folders("folder1").Folders("folder2").Folders("folder3").Folders("folder4")
  
    nextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
  
    For Each oItem In oMapi.Items
        If TypeName(oItem) = "MailItem" Then
            Set oMail = oItem
            Set oHTML = New MSHTML.HTMLDocument
            With oHTML
                .Body.innerHTML = oMail.HTMLBody
                Set oTables = .getElementsByTagName("table")
            End With
            For Each oTable In oTables
                For x = 0 To oTable.Rows.Length - 1
                    For y = 0 To oTable.Rows(x).Cells.Length - 1
                        If y = 1 Then
                            Cells(nextRow, "A").Value = oMail.ReceivedTime
                            Cells(nextRow, "B").Value = oMail.Subject
                            Cells(nextRow, "C").Offset(y - 1, x).Value = oTable.Rows(x).Cells(y).innerText
                        End If
                    Next y
                Next x
                nextRow = nextRow + 1
            Next oTable
            Set oHTML = Nothing
            Set oMail = Nothing
        End If
    Next oItem
  
    Set oApp = Nothing
    Set oMapi = Nothing
    Set oMail = Nothing
    Set oHTML = Nothing
    Set oTable = Nothing
    Set oTables = Nothing
  
End Sub

I would be very thankful for any help that you guys can provide!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
In this case, I think you'll need to post the HTML source code for the email for anyone to be able to help you. And, of course, you can substitute any confidential data with fake data.

Cheers!
 
Upvote 0
Domenic said:
In this case, I think you'll need to post the HTML source code for the email for anyone to be able to help you. And, of course, you can substitute any confidential data with fake data.

Cheers!
Click to expand...
Here it is! I had to edit the contents quite a bit, but this is what I managed to grab from the email source code:

HTML: 
<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office"
    xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml"
    xmlns="http://www.w3.org/TR/REC-html40">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <meta name="Generator" content="Microsoft Word 15 (filtered medium)">
    <style>
        <!--
        /* Font Definitions */
        @font-face {
            font-family: "Cambria Math";
            panose-1: 2 4 5 3 5 4 6 3 2 4;
        }

        @font-face {
            font-family: Calibri;
            panose-1: 2 15 5 2 2 2 4 3 2 4;
        }

        /* Style Definitions */
        p.MsoNormal,
        li.MsoNormal,
        div.MsoNormal {
            margin: 0cm;
            font-size: 11.0pt;
            font-family: "Calibri", sans-serif;
        }

        a:link,
        span.MsoHyperlink {
            mso-style-priority: 99;
            color: blue;
            font-style: normal;
            text-decoration: none none;
        }

        span.StileMessaggioDiPostaElettronica23 {
            mso-style-type: personal-compose;
            font-family: "Calibri", sans-serif;
            color: windowtext;
        }

        .MsoChpDefault {
            mso-style-type: export-only;
            font-size: 10.0pt;
        }

        @page WordSection1 {
            size: 612.0pt 792.0pt;
            margin: 70.85pt 2.0cm 2.0cm 2.0cm;
        }

        div.WordSection1 {
            page: WordSection1;
        }
        -->
    </style>
    <!--[if gte mso 9]><xml>
    <o:shapedefaults v:ext="edit" spidmax="1026" />
    </xml><![endif]-->
    <!--[if gte mso 9]><xml>
    <o:shapelayout v:ext="edit">
    <o:idmap v:ext="edit" data="1" />
    </o:shapelayout></xml><![endif]-->
</head>

<body lang="IT" link="blue" vlink="purple" style="word-wrap:break-word">

    <div>

        <div id="body_style">
            <p class="MsoNormal"><span style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black">
                    <o:p>&nbsp;</o:p>
                </span></p>
            <div align="center">
                <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="100%"
                    style="width:100.0%">
                    <tbody>
                        <tr>
                            <td width="100%" style="width:100.0%;background:white;padding:11.25pt 0cm 7.5pt 0cm">
                                <div align="center">
                                    <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="800"
                                        style="width:600.0pt">
                                        <tbody>
                                            <tr>
                                                <td style="background:white;padding:0cm 0cm 0cm 0cm">
                                                    <p class="MsoNormal"><span
                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black">&nbsp;
                                                            <o:p></o:p>
                                                        </span></p>
                                                </td>
                                            </tr>
                                            <tr>
                                                <td width="800" valign="top"
                                                    style="width:600.0pt;border:solid #E5E5E5 1.0pt;background:white;padding:0cm 0cm 0cm 0cm">
                                                    <table class="MsoNormalTable" border="0" cellspacing="0"
                                                        cellpadding="0" align="left" width="800" style="width:600.0pt">
                                                        <tbody>
                                                            <tr>
                                                                <td width="738" valign="top"
                                                                    style="width:553.5pt;padding:18.75pt 22.5pt 15.0pt 22.5pt">
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Lorem
                                                                            ipsum dolor sit amet,<br>
                                                                            <br>
                                                                            Lorem ipsum <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">#12345
                                                                                </span></strong>dolor sit amet,
                                                                            consectetur adipiscing elit, sed do
                                                                            eiusmod<br>
                                                                            <br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Lorem
                                                                                    ipsum:
                                                                                </span></strong>1234567890<br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Street
                                                                                    Name: </span></strong>
                                                                            1234 street name
                                                                            <br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">City:</span></strong>
                                                                            City<br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Country:</span></strong>
                                                                            Country
                                                                            <br>
                                                                            <br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Main
                                                                                    Contact:</span></strong> Contact
                                                                            Name
                                                                            <br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Phone
                                                                                    number:</span></strong>
                                                                            12345678 / 910111213
                                                                            <br>
                                                                            <br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Date
                                                                                    of Intake</span></strong>: Jan 01
                                                                            1111<br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Expected
                                                                                    resolution on:</span></strong>
                                                                            Jan 02 2222<br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Incident
                                                                                    tag: </span></strong>Lorem ipsum
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt">
                                                                        <strong><span
                                                                                style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Header
                                                                                1:</span></strong><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">
                                                                            <o:p></o:p>
                                                                        </span>
                                                                    </p>
                                                                    <table class="MsoNormalTable" border="0"
                                                                        cellspacing="0" cellpadding="0" width="739"
                                                                        style="width:554.25pt;border-collapse:collapse">
                                                                        <tbody>
                                                                            <tr>
                                                                                <td width="715"
                                                                                    style="width:536.25pt;border:inset 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt">
                                                                                    <p class="MsoNormal"><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Lorem
                                                                                            ipsum dolor sit
                                                                                            amet</span><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black">
                                                                                            <o:p></o:p>
                                                                                        </span></p>
                                                                                </td>
                                                                            </tr>
                                                                        </tbody>
                                                                    </table>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt">
                                                                        <strong><span
                                                                                style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Header
                                                                                2:</span></strong><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">
                                                                            <o:p></o:p>
                                                                        </span>
                                                                    </p>
                                                                    <table class="MsoNormalTable" border="0"
                                                                        cellspacing="0" cellpadding="0" width="739"
                                                                        style="width:554.25pt;border-collapse:collapse">
                                                                        <tbody>
                                                                            <tr style="height:15.0pt">
                                                                                <td width="100%"
                                                                                    style="width:100.0%;border:inset 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:15.0pt">
                                                                                    <p class="MsoNormal"><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Lorem
                                                                                            ipsum<br>
                                                                                            Dolor sit amet<br>
                                                                                            <br>
                                                                                            consectetur adipiscing<br>
                                                                                            elit, sed do eiusmod<br>
                                                                                            <br>
                                                                                            tempor incididunt ut<br>
                                                                                            labore et </span><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black">
                                                                                            <o:p></o:p>
                                                                                        </span></p>
                                                                                </td>
                                                                            </tr>
                                                                        </tbody>
                                                                    </table>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444"><br>
                                                                            <b><br>
                                                                                <strong><span
                                                                                        style="font-family:&quot;Arial&quot;,sans-serif">Header
                                                                                        3:</span></strong></b>
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <table class="MsoNormalTable" border="0"
                                                                        cellspacing="0" cellpadding="0" width="739"
                                                                        style="width:554.25pt;border-collapse:collapse">
                                                                        <tbody>
                                                                            <tr>
                                                                                <td width="730"
                                                                                    style="width:547.5pt;border:inset 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt">
                                                                                    <p class="MsoNormal"><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">12345678<span
                                                                                                style="color:black">
                                                                                                <o:p></o:p>
                                                                                            </span></span></p>
                                                                                </td>
                                                                            </tr>
                                                                        </tbody>
                                                                    </table>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt">
                                                                        <strong><span
                                                                                style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Header
                                                                                4:</span></strong><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">
                                                                            <o:p></o:p>
                                                                        </span>
                                                                    </p>
                                                                    <table class="MsoNormalTable" border="0"
                                                                        cellspacing="0" cellpadding="0" width="739"
                                                                        style="width:554.25pt;border-collapse:collapse">
                                                                        <tbody>
                                                                            <tr>
                                                                                <td width="99%"
                                                                                    style="width:99.44%;border:inset 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt">
                                                                                    <p class="MsoNormal"><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">12
                                                                                            3<span style="color:black">
                                                                                                <o:p></o:p>
                                                                                            </span></span></p>
                                                                                </td>
                                                                            </tr>
                                                                        </tbody>
                                                                    </table>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt">
                                                                        <strong><span
                                                                                style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Header
                                                                                5:</span></strong><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">
                                                                            <o:p></o:p>
                                                                        </span>
                                                                    </p>
                                                                    <table class="MsoNormalTable" border="0"
                                                                        cellspacing="0" cellpadding="0" width="739"
                                                                        style="width:554.25pt;border-collapse:collapse">
                                                                        <tbody>
                                                                            <tr style="height:15.0pt">
                                                                                <td width="99%"
                                                                                    style="width:99.44%;border:inset 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt;height:15.0pt">
                                                                                    <p class="MsoNormal"><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif"><a
                                                                                                href="https://www.google.com">www.google.com</a>;
                                                                                            <a
                                                                                                href="https://www.google.com">www.google.com</a>;
                                                                                            <a
                                                                                                href="https://www.google.com">
                                                                                                www.google.com</a><span>This
                                                                                                actually contains links
                                                                                                to email
                                                                                                addresses</span><span
                                                                                                style="color:black">
                                                                                                <o:p></o:p>
                                                                                            </span></span></p>
                                                                                </td>
                                                                            </tr>
                                                                        </tbody>
                                                                    </table>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444"><br>
                                                                            <strong><span
                                                                                    style="font-family:&quot;Arial&quot;,sans-serif">Header
                                                                                    6:</span></strong>
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <table class="MsoNormalTable" border="0"
                                                                        cellspacing="0" cellpadding="0" width="739"
                                                                        style="width:554.25pt;border-collapse:collapse">
                                                                        <tbody>
                                                                            <tr>
                                                                                <td width="99%"
                                                                                    style="width:99.44%;border:inset 1.0pt;padding:5.0pt 5.0pt 5.0pt 5.0pt">
                                                                                    <p class="MsoNormal"><span
                                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif">1234<span
                                                                                                style="color:black">
                                                                                                <o:p></o:p>
                                                                                            </span></span></p>
                                                                                </td>
                                                                            </tr>
                                                                        </tbody>
                                                                    </table>
                                                                    <p
                                                                        style="mso-margin-top-alt:0cm;margin-right:0cm;margin-bottom:12.0pt;margin-left:0cm;line-height:15.0pt">
                                                                        <span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444"><br>
                                                                            <br>
                                                                            Lorem ipsum dolor sit amet, consectetur
                                                                            adipiscing elit.&nbsp;<o:p>
                                                                            </o:p></span>
                                                                    </p>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Thank
                                                                            you!<br>
                                                                            Lorem ipsum<o:p></o:p></span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">Lorem
                                                                            ipsum dolor sit amet, consectetur adipiscing
                                                                            elit<o:p></o:p></span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                    <p style="margin:0cm;line-height:15.0pt"><span
                                                                            style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#444444">&nbsp;
                                                                            <o:p></o:p>
                                                                        </span></p>
                                                                </td>
                                                            </tr>
                                                        </tbody>
                                                    </table>
                                                </td>
                                            </tr>
                                        </tbody>
                                    </table>
                                </div>
                            </td>
                        </tr>
                    </tbody>
                </table>
            </div>
        </div>
        <p class="MsoNormal" style="margin-bottom:12.0pt"><span
                style="font-size:1.0pt;font-family:&quot;Arial&quot;,sans-serif;color:white">_==--</span><span
                style="font-size:10.0pt;font-family:&quot;Arial&quot;,sans-serif;color:black">
                <o:p></o:p>
            </span></p>
    </div>
    </div>
</body>

</html>

It's a lot of code! But it's what I managed to crop out from 3 emails worth of source code in a thread. Hopefully this helps.

Let me know if I can do anything else!
 
Upvote 0
It looks like the relevant elements within the HTML document have the class name "MsoNormal", and have index numbers 2 through 6, inclusively. Therefore, you can use the following code to retrieve those values and assign them accordingly.

VBA Code: 
    With oHTML
    
        'header 1
        Dim header1 As String
        header1 = .getElementsByClassName("MsoNormal")(2).innerText
        
        'header 2
        Dim header2 As String
        header2 = .getElementsByClassName("MsoNormal")(3).innerText
        
        'header 3
        Dim header3 As String
        header3 = .getElementsByClassName("MsoNormal")(4).innerText
        
        'header 4
        Dim header4 As String
        header4 = .getElementsByClassName("MsoNormal")(5).innerText
        
        'header 5
        Dim header5Element As Object
        Dim header5 As String
        Set header5Element = .getElementsByClassName("MsoNormal")(6)
        header5 = header5Element.innerText
        
        'header 5 hyperlinks
        Dim linksElementCollection As Object
        Dim hyperlinks() As String
        Dim i As Long
        Set linksElementCollection = header5Element.getElementsByTagName("a")
        ReDim hyperlinks(linksElementCollection.Length - 1)
        For i = 0 To linksElementCollection.Length - 1
            hyperlinks(i) = Trim(linksElementCollection(i).getAttribute("href")) 'Trim(linksElementCollection(i).innerText)
        Next i
        
    End With

Hope this helps!
 
Upvote 0
Domenic said:
It looks like the relevant elements within the HTML document have the class name "MsoNormal", and have index numbers 2 through 6, inclusively. Therefore, you can use the following code to retrieve those values and assign them accordingly.

VBA Code: 
    With oHTML
   
        'header 1
        Dim header1 As String
        header1 = .getElementsByClassName("MsoNormal")(2).innerText
       
        'header 2
        Dim header2 As String
        header2 = .getElementsByClassName("MsoNormal")(3).innerText
       
        'header 3
        Dim header3 As String
        header3 = .getElementsByClassName("MsoNormal")(4).innerText
       
        'header 4
        Dim header4 As String
        header4 = .getElementsByClassName("MsoNormal")(5).innerText
       
        'header 5
        Dim header5Element As Object
        Dim header5 As String
        Set header5Element = .getElementsByClassName("MsoNormal")(6)
        header5 = header5Element.innerText
       
        'header 5 hyperlinks
        Dim linksElementCollection As Object
        Dim hyperlinks() As String
        Dim i As Long
        Set linksElementCollection = header5Element.getElementsByTagName("a")
        ReDim hyperlinks(linksElementCollection.Length - 1)
        For i = 0 To linksElementCollection.Length - 1
            hyperlinks(i) = Trim(linksElementCollection(i).getAttribute("href")) 'Trim(linksElementCollection(i).innerText)
        Next i
       
    End With

Hope this helps!
Click to expand...
Thank you very much! How would I go into putting this piece of code into the code above to have it extract just those type of emails into an excel file?
 
Upvote 0
Simply assign the values from each variable to their respective cells, for example cells(nextRow, "C").value = header1, etc...
 
Upvote 0
Domenic said:
Simply assign the values from each variable to their respective cells, for example cells(nextRow, "C").value = header1, etc...
Click to expand...
I did but I get an error (Run-time error 91: Object variable or With lock variable not set) after header1 = .getElementsByClassName("MsoNormal")(2).innerText in the code (for reference, as you'll see below, im only grabbing the information from headers 3, 4 and 5:

VBA Code: 
Option Explicit

Sub demo()

    Dim oApp As Outlook.Application
    Dim oMapi As Outlook.MAPIFolder
    Dim oMail As Outlook.MailItem
    Dim oItem As Variant
    Dim oHTML As MSHTML.HTMLDocument
    Dim oTable As MSHTML.HTMLTable
    Dim oTables As MSHTML.IHTMLElementCollection
    Dim nextRow As Long
    Dim x As Long
    Dim y As Long
  
    On Error Resume Next
    Set oApp = GetObject(, "Outlook.Application")
    If oApp Is Nothing Then
        Set oApp = CreateObject("Outlook.Application")
        If oApp Is Nothing Then
            MsgBox "Unable to start Outlook!", vbExclamation, "Outlook"
            Exit Sub
        End If
    End If
    On Error GoTo 0
  
    Set oMapi = oApp.GetNamespace("MAPI").Folders("1").Folders("2").Folders("3").Folders("4")
  
    nextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
  
    For Each oItem In oMapi.Items
        If TypeName(oItem) = "MailItem" Then
            Set oMail = oItem
            Set oHTML = New MSHTML.HTMLDocument
            With oHTML
    
                'header 1
                Dim header1 As String
                header1 = .getElementsByClassName("MsoNormal")(2).innerText
        
                'header 2
                Dim header2 As String
                header2 = .getElementsByClassName("MsoNormal")(3).innerText
        
                'header 3
                Dim header3 As String
                header3 = .getElementsByClassName("MsoNormal")(4).innerText
        
                'header 4
                Dim header4 As String
                header4 = .getElementsByClassName("MsoNormal")(5).innerText
        
                'header 5
                Dim header5Element As Object
                Dim header5 As String
                Set header5Element = .getElementsByClassName("MsoNormal")(6)
                header5 = header5Element.innerText
        
                'header 5 hyperlinks
                Dim linksElementCollection As Object
                Dim hyperlinks() As String
                Dim i As Long
                Set linksElementCollection = header5Element.getElementsByTagName("a")
                ReDim hyperlinks(linksElementCollection.Length - 1)
                For i = 0 To linksElementCollection.Length - 1
                    hyperlinks(i) = Trim(linksElementCollection(i).getAttribute("href")) 'Trim(linksElementCollection(i).innerText)
                Next i
                
        
            End With
            For Each oTable In oTables
                For x = 0 To oTable.Rows.Length - 1
                    For y = 0 To oTable.Rows(x).Cells.Length - 1
                        If y = 1 Then
                            Cells(nextRow, "A").Value = header3
                            Cells(nextRow, "B").Value = header4
                            Cells(nextRow, "C").Value = header5
                            Cells(nextRow, "D").Offset(y - 1, x).Value = oTable.Rows(x).Cells(y).innerText
                        End If
                    Next y
                Next x
                nextRow = nextRow + 1
            Next oTable
            Set oHTML = Nothing
            Set oMail = Nothing
        End If
    Next oItem
  
    Set oApp = Nothing
    Set oMapi = Nothing
    Set oMail = Nothing
    Set oHTML = Nothing
    Set oTable = Nothing
    Set oTables = Nothing
  
End Sub
 
Upvote 0
You're getting that error because you haven't assigned the email's html to oHTML. And it looks like you haven't assigned the tables to oTables either. Also, since you only need header3, header4, and header5, you can get rid of the rest of the code. So something like this...

VBA Code: 
Option Explicit

Sub demo()

    Dim oApp As Outlook.Application
    Dim oMapi As Outlook.MAPIFolder
    Dim oMail As Outlook.MailItem
    Dim oItem As Variant
    Dim oHTML As MSHTML.HTMLDocument
    Dim oTable As MSHTML.HTMLTable
    Dim oTables As MSHTML.IHTMLElementCollection
    Dim nextRow As Long
    Dim x As Long
    Dim y As Long
 
    On Error Resume Next
    Set oApp = GetObject(, "Outlook.Application")
    If oApp Is Nothing Then
        Set oApp = CreateObject("Outlook.Application")
        If oApp Is Nothing Then
            MsgBox "Unable to start Outlook!", vbExclamation, "Outlook"
            Exit Sub
        End If
    End If
    On Error GoTo 0
 
    Set oMapi = oApp.GetNamespace("MAPI").Folders("folder1").Folders("folder2").Folders("folder3").Folders("folder4")
 
    nextRow = Cells(Rows.Count, "A").End(xlUp).Row + 1
 
    For Each oItem In oMapi.Items
        If TypeName(oItem) = "MailItem" Then
            Set oMail = oItem
            Set oHTML = New MSHTML.HTMLDocument
            With oHTML
                .Body.innerHTML = oMail.HTMLBody
                Set oTables = .getElementsByTagName("table")
                'header 3
                Dim header3 As String
                header3 = .getElementsByClassName("MsoNormal")(4).innerText
                'header 4
                Dim header4 As String
                header4 = .getElementsByClassName("MsoNormal")(5).innerText
                'header 5
                Dim header5 As String
                header5 = .getElementsByClassName("MsoNormal")(6).innerText
            End With
            For Each oTable In oTables
                For x = 0 To oTable.Rows.Length - 1
                    For y = 0 To oTable.Rows(x).Cells.Length - 1
                        If y = 1 Then
                            Cells(nextRow, "A").Value = header3
                            Cells(nextRow, "B").Value = header4
                            Cells(nextRow, "C").Value = header5
                            Cells(nextRow, "D").Offset(y - 1, x).Value = oTable.Rows(x).Cells(y).innerText
                        End If
                    Next y
                Next x
                nextRow = nextRow + 1
            Next oTable
            Set oHTML = Nothing
            Set oMail = Nothing
        End If
    Next oItem
 
    Set oApp = Nothing
    Set oMapi = Nothing
    Set oMail = Nothing
    Set oHTML = Nothing
    Set oTable = Nothing
    Set oTables = Nothing
 
End Sub

Hope this helps!
 
Upvote 0
Hello!

Sorry for the late response. I was tasked with other files to deal with and now I am finally back to this one.

The code you posted seems to loop a few times but then ends up displaying a runtime error. It happens on the line:

header3 = .getElementsByClassName("MsoNormal")(4).innerText

and displays "Run-time error '91': Object variable or With block variable not set


Furthermore, it seems to be gathering the wrong table. It's not picking up the one from the picture I initially linked, but rather the one with over a dozen rows.

Any idea how to resolve this?
 
Upvote 0
That means that the html document for the email does not contain a 5th element (elements are indexed starting at 0) having the classname "MsoNormal". Is that particular email structured differently than the others?
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA is centering the image instead of aligning it to the left as usual in New Email
Replies
1
Views
141
Micron
M
E
  • Solved
Copy Outlook Email HTML Body as Table to Excel
Replies
6
Views
1K
E1375
E
O
  • Solved
Unable to download html data from specific site
Replies
7
Views
487
Edgar_
Edgar_
J
  • Solved
How to clean scrape a website for financial data?
2
Replies
17
Views
669
Edgar_
Edgar_
C
  • Solved
Add a table to e-mail to be sent to multiple addreses
Replies
3
Views
208
mumps
mumps

Forum statistics

Threads
1,223,948
Messages
6,175,571
Members
452,652
Latest member
eduedu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top