none
VBA - Outlook RRS feed

  • Question

  • Hi Team,

    In outlook I have folder named "Task". On a daily basis, I receive mails in the table format as shown below:

    ID

    Location

      123456

    CA

    I am looking for a VBA code,

    1. Should copy the above table and paste in excel sheet.

    2.Should copy all the tables in the folder named "Task" and paste in the excel sheet as shown below:

    For example, in the folder (Task) I have 5 emails (means 5 tables), the output should be as follows:

    ID & Location are common in all the emails/tables, hence I need only data as shown below.

    ID Location
    123456 CA
    234567 PA
    189654 NJ
    111111 MN
    112233 NC

    Thank you in advance for you help !!

    Currently I have a VB code : However the challenge with the code is each time ID & Location along with the data is getting copied as shown below:

    ID Location
    123456 CA
    ID Location
    234567 PA
    ID Location
    189654 NJ
    ID Location
    111111 MN
    ID Location
    112233 NC

    ****

    Sub dd()
    Dim item As MailItem, x%
    Dim r As Object  'As Word.Range
    Dim doc As Object 'As Word.Document
    Dim xlApp As Object, wkb As Object
    Set xlApp = CreateObject("Excel.Application")
    Set wkb = xlApp.Workbooks.Add
    xlApp.Visible = True

    Dim wks As Object
    Set wks = wkb.Sheets(1)
    For Each item In Application.ActiveExplorer.Selection
    Set doc = item.GetInspector.WordEditor
        For x = 1 To doc.tables.Count
         Set r = doc.tables(x)
            r.Range.Copy
           wks.Paste
           wks.cells(wks.rows.Count, 1).End(3).Offset(1).Select
        Next
    Next
    End Sub

    *****

    Request you enhance my code with the above requirement or help me with brand new code.

    Thanks again !!

    Regards,

    Mahesh Babu


    • Edited by mbkoppa Monday, July 23, 2018 11:10 AM
    Monday, July 23, 2018 11:08 AM

All replies

  • Hello mbkoppa,

    >>However the challenge with the code is each time ID & Location along with the data is getting copied as shown below:

    In my test, the code could copy the data well for me. Is there any other content in the mail expects the table? For avoid any misunderstanding, I would suggest you save a simple mail as msg file and then share the file to us for testing.

    For sharing file, you could share the file via Cloud Storage, such as One Drive, and then put link address here.

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, July 24, 2018 1:35 AM