none
VBA Code RRS feed

  • Question

  • Hi Team,

    I am very new to VBA coding.

    I was hoping to code my outlook to capture data from an excel attachment into an excel spreadsheet.

    If anyone can give me some basic code that i can work on that would be great.

    Apologies i don't know if i am asking for anything too much.

    I appreciate it

    Tuesday, June 6, 2017 8:46 AM

All replies

  • Hi Ralphvn01,

    You could save the attachment file and then you could use Microsoft.Office.Interop.Excel dll to iterate worksheets in the attachment and export data into another sheet. Here is the example.

    Function GetExcelAttachment(ma As MailItem)
    Dim elApp As Excel.Application
    Set elApp = CreateObject("Excel.application")
    elApp.Visible = True
    Dim att As Attachment
    Set att = ma.Attachments(1)
    Path = "C:\Users\Desktop\ExcelFile\"
    FullName = Path & att.FileName
    att.SaveAsFile FullName
    Dim attWorkbook, yourWorkbook
    Set attWorkbook = elApp.workbooks.Open(FullName)
    Set yourWorkbook = elApp.workbooks.Open("Your sheet file path")
    Set ws = yourWorkbook.sheets(1)
    lastrow = ws.Cells(65536, 1).End(xlUp).Row
    For Each sht In attWorkbook.worksheets
        sht.usedrange.Copy 
        lastrow = ws.Cells(65536, 1).End(xlUp).Row
        ws.Cells(lastrow + 1, 1).Select 'copy to last row
        ws.Paste
    Next sht
    End Function

    For more information, you could refer

    Automating Outlook from a Visual Basic Application

    How to automate Microsoft Excel from Visual Basic

    Best Regards,

    Thursday, June 8, 2017 1:43 AM