(Solved) – vba exporting table data from email to excel columns

  • by

I’m trying to pull in data that is set up like a table from my email. Does anyone know how I can loop through the email contents to check for tables, and if there is to export the data to columns in excel? Right now the code I have just returns the subject, sender and time sent.

Sub GetMCInboxLeads()

    Dim ol As Outlook.Application
    Dim ns As Outlook.Namespace
    Dim fol As Outlook.Folder
    Dim i As Object
    Dim mi As Outlook.MailItem         
    Dim n As Long 
    Dim rh As Double
    Dim objOwner As Outlook.Recipient

    Set ol = New Outlook.Application
    Set ns = ol.GetNamespace("MAPI")
    Set fol = ns.GetDefaultFolder(olFolderInbox)
    Set objOwner = ns.CreateRecipient("xx@xxx.com")

    If objOwner.Resolved Then
        Set fol = ns.GetSharedDefaultFolder(objOwner, olFolderInbox)
    End If

    rh = Range("A1").RowHeight
    'i = 1

    For Each i In fol.Items

        If i.Class = olMail Then

            n = n   1 

            Set mi = i
            'Debug.Print mi.Subject, mi.SenderName, mi.ReceivedTime

            Cells(n, 1).Value = mi.SenderName
            Cells(n, 2).Value = mi.Subject
            Cells(n, 3).Value = mi.ReceivedTime
            Cells(n, 4).Value = mi.Body
        End If

    Next i

    Range("A1").CurrentRegion.EntireRow.RowHeight = rh

Leave a Reply

Your email address will not be published. Required fields are marked *