Copy tables from outlook to excel

[Vijay Kumar]

Hi.. all

Recently, I joined this forum. I am very interested in learning Outlook Programming. Here is my question
Everyday I am getting some notification mails from our server. In that mail body, there is a table contains some information about the products. I need to copy all those tables from each and every notification mail to excel and do some formatting and sending mail to concern member.
I have tried so many things in excel. I am very much comfortable with Excel VBA and Outlook VBA. I searched so many forums to match my required output. But I can't able to find the proper solution. I hope I will get best method to resolve the problem
How can I recognize the tables in outlook mail ? I can do it if it is word or Excel. But there is no option to recognize the table in outlook mail body.
Please give your valuable feedback

Regards
Vijay

 

[Diane Poremsky]

How are you doing it in Word? The same method should work in Outlook, provided you reference the word object model.

 

[Michael Bauer]

Diane, I think it's answered in the VBA forum.

 

[Diane Poremsky]

We'll keep everything together in that forum then -
https://forums.slipstick.com/threads/92449-tables-in-outlook-body/

 

[Vijay Kumar]

How are you doing it in Word? The same method should work in Outlook, provided you reference the word object model.

Dear Diane

Below

Sub ImportDataFromWord()
    Dim appWd As Word.Application
    Dim appDoc As Word.Document
    Dim tblNo As Integer
    Dim wdFilePath As String
    Dim iRow As Integer
    Dim iCol As Integer
    Dim i As Integer
    Dim lRow As Long
   
    Set appWd = New Word.Application
    wdFilePath = "C:\users\vkumar4\desktop\test.docx"
    Set appDoc = appWd.Documents.Open(wdFilePath, ReadOnly:=True)
   
    With appDoc
        tblNo = appDoc.Tables.Count
        For i = 1 To tblNo
        With .Tables(i)
            For iRow = 1 To .Rows.Count
            lRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
                For iCol = 1 To .Columns.Count
                    ThisWorkbook.ActiveSheet.Cells(lRow, iCol) = WorksheetFunction.Clean(.Cell(iRow, iCol).Range.Text)
                Next iCol
            Next iRow
        End With
        Next i
    End With
   
    Set appDoc = Nothing
    Set appWd = Nothing
End Sub

code I am using for getting data from word files into excel file. Kindly suggest how can I use for Outlook

 

[Michael Bauer]

Please see Diane`s latest reply, it points to your cross post where the answer is given.

 

[Vijay Kumar]

Please see Diane`s latest reply, it points to your cross post where the answer is given.

Dear Michael

I read that article, but I can't able integrate with Outlook and I am new outlook VBA. So please try to suggest me the way for my requirement.

Thank you so much for your valuable comments

 

[Michael Bauer]

The code you're showing sets the variable (appDoc) by calling Documents.Open. Instead you need to get the Document from the WordEditor property as shown in my example. Try it yourself, please.

 

[Vijay Kumar]

The code you're showing sets the variable (appDoc) by calling Documents.Open. Instead you need to get the Document from the WordEditor property as shown in my example. Try it yourself, please.

Dear Michael

I tried by creating some variable for outlook application, and tried to set app doc = ins.wordEditor (In this line I am getting error)

Will you please explain, Now I putting this code in Excel VBA and created referernces for Word & Outlook applications. By the Code, how can I get select the outlook mail body and select the Tables. Its creating confusion. Can you please explain the process.

Sub ImportDataFromWord()
    Dim appWd As Word.Application
    Dim appDoc As Word.Document
    Dim tblNo As Integer
    Dim wdFilePath As String
    Dim iRow As Integer
    Dim iCol As Integer
    Dim i As Integer
    Dim lRow As Long
    Dim olApp As Outlook.Application
    Dim olNs As Outlook.Namespace
    Dim Ins As Outlook.Inspector
    Dim selection As Word.selection
   
   
    Set appWd = New Word.Application
    wdFilePath = "C:\users\vkumar4\desktop\test.docx"
    Set olApp = New Outlook.Application
    Set Ins = Application.ActiveInspector
    Set selection = Word.selection
    Set appDoc = Ins.WordEditor

 

[Vijay Kumar]

Dear Michael

As you said, I just copy your module and paste it in outlook VBA as module. When I go through by pressing F8 key, it gives error on line Set Document = Ins.WordEditor. Can you please tell me the hierarchy of the code ? In your code, variable Document declared as word.document and when we initializing by Set Document = Ins.word editor. How it is possible, without creating instance to word application ? Please elabore more

Public Sub UseWord()
  Dim Ins As Outlook.Inspector
  Dim Document As Word.Document
  Dim Word As Word.Application
  Dim Selection As Word.Selection

  Set Ins = Application.ActiveInspector
  Set Document = Ins.WordEditor
  Set Word = Document.Application
  Set Selection = Word.Selection

  '...

End Sub

 

[Michael Bauer]

ActiveInspector points to the active, open item. If you want to reference the item selected in the folder, use Application.ActiveExplorer.Selection(1). For more details always reveal what the error message is.

Note please, using Application with the mentioned properties doesn't work if you run the code in Word, or Excel as Application always points to the host application that runs the VBA code.

 

[Vijay Kumar]

Dear Michael,

I tried a lot for getting the idea, and I am feeling mad while doing this.. I scratched my head, but can't able to get it
So I request, Can you please prepare a dummy code for my use.

 

[Michael Bauer]

Please show your result.