Copy tables from outlook to excel

Status
Not open for further replies.

Vijay Kumar

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
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
 

Vijay Kumar

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
How are you doing it in Word? The same method should work in Outlook, provided you reference the word object model.

Dear Diane

Below
Code:
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

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
Please see Diane`s latest reply, it points to your cross post where the answer is given.
 

Vijay Kumar

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
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

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
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.

Code:
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

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
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


Code:
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

Senior Member
Outlook version
Outlook 2010 32 bit
Email Account
Exchange Server
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

Member
Outlook version
Outlook 2013 64 bit
Email Account
Exchange Server
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.
 
Status
Not open for further replies.
Similar threads
Thread starter Title Forum Replies Date
B Need to Copy an email to a subfolder Outlook VBA and Custom Forms 2
O Outlook 365 - How to create / copy a new contact from an existing one? Using Outlook 3
S Copy Tasks/Reminders from Shared Mailbox to Personal Tasks/Reminders Outlook VBA and Custom Forms 0
A Cannot copy this folder because it may contain private items Using Outlook 0
C Copy Move item won't work Outlook VBA and Custom Forms 2
B VBA to convert email to task, insert text of email in task notes, and attach copy of original email Outlook VBA and Custom Forms 4
Commodore Move turns into "copy" Using Outlook 3
C Copy Outlook contact field value to another field Outlook VBA and Custom Forms 1
J Copy to calendar function no longer working in outlook 365 Using Outlook 5
F Copy and replace not update contact in another pst Using Outlook 0
B Outlook Business Contact Manager with SQL to Excel, User Defined Fields in BCM don't sync in SQL. Can I use VBA code to copy 1 field to another? BCM (Business Contact Manager) 0
Commodore Folders always closed in move/copy items dialog box Using Outlook 3
N Outlook rules don't create a copy for bcc'ed emails Using Outlook 3
geofferyh Outlook 2010 How to Copy Outlook Attachment to a Specific Folder? Outlook VBA and Custom Forms 3
S Custom Form, copy user field data to message body Outlook VBA and Custom Forms 12
R Copy Outlook Public Folders to a File Server Shared Folder Using Outlook 0
K Outlook Rules: Move a Copy Using Outlook 4
oliv- HOW TO COPY /USE FOLDERS ICONS Outlook VBA and Custom Forms 2
E Copy e-mail body from outlook and insert into excel Outlook VBA and Custom Forms 3
B Copy/Move Exchange inbox to Pop inbox Using Outlook 4
R Sending email copy (*.msg file) of sent email if subject line contains specific string. Outlook VBA and Custom Forms 1
O Copy mails from many subfolders to 1 foldr Using Outlook 2
K ind specific Subject line from outlook and copy the content of the email body to exce Outlook VBA and Custom Forms 0
K How to find specific header and copy the mail body Using Outlook 0
J Copy or Export Outlook Mail to Excel Outlook VBA and Custom Forms 6
G Copy Contact field to Appointment Custom Form Field Outlook VBA and Custom Forms 2
G How to Copy Multi Select Listbox Data to Appointment Outlook VBA and Custom Forms 3
Carrie Dickey Outlook 2016 created two calendars titled Calendar1 - appear to be a copy Using Outlook 2
P How to copy and append data from Outlook 2016 message into Excel 2016 workbook Using Outlook 0
Stilgar Relsik Create a rule to copy text from an email and paste it in the subject line. Using Outlook 1
R Macro to copy email to excel - Runtime Error 91 Object Variable Not Set Outlook VBA and Custom Forms 11
H Macro to Copy Specific content from Mail Body and Paste to Excel Outlook VBA and Custom Forms 4
M How to keep reccurence during copy tasks to calendar? Using Outlook 1
Diane Poremsky Copy New Appointments to Another Calendar using VBA Using Outlook 0
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
C Copy Task to Non-Microsoft PIM "Rainlendar" Using Outlook 0
G VBA Copy draft email to a new email - attachments not copided Using Outlook 7
C Copy email to excel runtime error 5020 Using Outlook 5
I Copy email from folder to folder - FAILS Using Outlook 5
M Copy new appointments created in multiple shared calendars to another exchange calendar Outlook VBA and Custom Forms 1
Q Why can't I copy image with embedded hyperlink from email to Word Using Outlook 0
I How to make a copy of a task Using Outlook 8
F copy data in Custom Field to other folder Outlook VBA and Custom Forms 2
A Outlook: copy & paste Outlook VBA and Custom Forms 9
V Copy and paste body and subject and send multiple emails Outlook VBA and Custom Forms 3
Diane Poremsky Use a macro to copy data in Outlook email to Excel workbook Using Outlook 0
joflo copy contents from outlook to excell - please help. Using Outlook 4
L Copy email body fields to excel Using Outlook 0
G outlook 13 copy & paste without losing formatting Using Outlook 1
C Copy Cell value from Excel and paste into current email Outlook VBA and Custom Forms 10

Similar threads

Top