Copying HTML Tables from Outlook to Excel-CodePudding

Have been trying to get Excel VBA to export HTML tables - keep getting a runtime error. Something similar does work when run in Outlook VBA using: For Each Item In Application.ActiveExplorer.Selection

What I want to do is to copy the HTML tables and convert them to Excel tables From here I will be able to work on the data to extract and convert to an ongoing spreadsheet record

Dim OutlookMail As Outlook.MailItem
Dim myInbox As Outlook.MAPIFolder
Dim myitems As Outlook.Items
Dim x As Long
Dim lRow As Long
Dim lCol As Long
Dim wdDoc As Object
Dim Item As MailItem
Dim r As Object
Dim myInspector As Outlook.Inspector


Sub getOrderEmailData()

    ' *************This section cycles through email accounts until it finds "[email protected]" and selects the mailitems in this account inbox ********
    Set myAccounts = outlookapp.GetNamespace("MAPI").Stores
    
    For i = 1 To myAccounts.Count
        If myAccounts.Item(i).DisplayName = "[email protected]" Then
            Set myInbox = myAccounts.Item(i).GetDefaultFolder(olFolderInbox)
            Debug.Print myInbox
            Exit For
        End If
    Next
    If myInbox Is Nothing Then Exit Sub ' avoid error if no account is chosen

    Set myitems = myInbox.Items 'select the Inbox

    ' show some feedback if no email is found
    If myitems.Count = 0 Then
        MsgBox "Nothing found. Try another account."
        Exit Sub
    End If
    Count = 0
    
    
'*************** This section should copy however many tables are found in the HTML email on Outlook to Excel ***********************
        
   For Each Item In myInbox.Items
       'On Error Resume Next
        Set myInspector = Item.GetInspector
        Set wdDoc = myInspector.WordEditor  <<<<-------- Here is where the program fails - "Run time error - The operation failed"
            
            Debug.Print Item.Subject
            If Item.Subject = "Alert from MadeUp Solutions" Then

                deleteSheet ("test")

                    'to get all tables
                    Debug.Print wdDoc.Tables.Count
                    For x = 1 To wdDoc.Tables.Count
                        'Select table range
                        Set r = wdDoc.Tables(x)
                        'Copy and paste next table
                        r.Range.Copy
                        Worksheets("Test").Cells(lRow   1, 1).Select
                        Worksheets("Test").Paste
                        On Error Resume Next
                        'Find row extent of data in each sheet
                        lRow = Worksheets("Test").Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
                    Next x
                
            End If
        Next

CodePudding user response：

Try to call the Display method before getting the Word editor to initialize it:

For Each Item In myInbox.Items
    'On Error Resume Next
    Item.Display
    ' ...
    Set myInspector = Item.GetInspector
    Set wdDoc = myInspector.WordEditor
    ' ...
    Item.Close olSave 
Next

It also makes sense to run the code for getting the Word editor instance in the Inspector's Activate event to be sure the editor is fully initialized when you try to get its instance.

CodePudding user response：

If GetInspector and Word editor do not work for you, the only option is to read the MailItem.HTMLBody property and load it into an instance of the HTMLDocument object, which you can then use to extract HTML tables.