Get table from lotus note email to excel cell

[tommy8quko]

hi guys,

I got trouble on handling the lotus notes and internet don't have much resources on this.
Here is my problem, i have a table in email look like below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Food[/TD]
[TD]Drinks[/TD]
[TD]People[/TD]
[TD]Car[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]1[/TD]
[TD]25[/TD]
[/TR]
</tbody>[/TABLE]

And i got below code so far, but it error on Set rttab = rtnav.GetElement line.
Any idea how to do the things I want?
Thanks, i have been stuck for whole days.

Public Sub Get_Notes_Email_Text()


    Dim NSession As Object      'NotesSession
    Dim NMailDb As Object       'NotesDatabase
    Dim NDocs As Object         'NotesDocumentCollection
    Dim NDoc As Object          'NotesDocument
    Dim NNextDoc As Object      'NotesDocument
    Dim NItem As Object         'NotesItem
    Dim view As String
    Dim filterText As String
    
    view = "Bond IPO"       'Name of view or folder to retrieve documents from
    filterText = ActiveSheet.Range("AB8")    'Optional text string to filter the view
    
    Set NSession = CreateObject("Notes.NotesSession")
    Set NMailDb = NSession.GetDatabase("", "")  'Default server and database


    If Not NMailDb.IsOpen Then NMailDb.OpenMail
    
    Set NDocs = NMailDb.GetView(view)
    NDocs.Clear
        
    'Apply optional filter
        
    If filterText <> "" Then
        NDocs.FTSearch filterText, 0
    End If
      
    Set NDoc = NDocs.GetFirstDocument
    Do Until NDoc Is Nothing
        Set NNextDoc = NDocs.GetNextDocument(NDoc)
        Set NItem = NDoc.GetFirstItem("Body")
           Set rtnav = NItem.CreateNavigator
            Set rttab = rtnav.GetElement
            Set rtrange = NItem.CreateRange
        
        
        
        If Not NItem Is Nothing Then
            lines = Split(NItem.Text, vbCrLf)
                Range("A10").Resize(UBound(lines) + 1, 1).Value = Application.WorksheetFunction.Transpose(lines)
        End If
        Set NDoc = NNextDoc
    Loop
    
    NMailDb.Close
    NSession.Close
    
    Set NSession = Nothing


End Sub

 

[John_w]

I think you have to set the Navigator position to an element (e.g. a table) before calling GetElement:

    Do Until NDoc Is Nothing
        Set NNextDoc = NDocs.GetNextDocument(NDoc)
        Set NItem = NDoc.GetFirstItem("Body")
        Set rtnav = NItem.CreateNavigator

        If rtnav.FindFirstElement(RTELEM_TYPE_TABLE) Then
            Set rttab = rtnav.GetElement
            Set rtrange = NItem.CreateRange

            If Not NItem Is Nothing Then
                lines = Split(NItem.Text, vbCrLf)
                Range("A10").Resize(UBound(lines) + 1, 1).Value = Application.WorksheetFunction.Transpose(lines)
            End If
        Else
            MsgBox "No table in Body item"
        End If
    Loop