I get 50 mails with Excel sheets per day. I want to add the first line of each Excel sheet to an existing Excel sheet located on my computer.
I first wrote a script that merges those sheets locally (I downloaded the Excel sheet and then run my script) and here everything works.
Now I tried to put the script directly into Outlook, so this is done automatically whenever one of those emails is received.
Originally I wanted to add a rule on which basis the macro should have been run, but this does not work. The solution I found was to call the macro in a subroutine within the "ThisOutlookSession":
This is the first time I am writing a macro for outlook, so I am unsure if I am passing the arguments correctly.
When my Modul2 is called, I immediately get the error
user-defined type not defined
on the line Dim wb_master As Workbook
, and on Dim wb_email As Workbook
.
Here is a mini example of the code (here it simply adds the name of the file into the ID column):
Sub Merge_oewaReport(itm As Outlook.MailItem)
Dim wb_path As String
Dim wb_master As Workbook
Dim ws_master As String
Dim objAtt As Outlook.Attachment
Dim FileName As String
Dim wb_email As Workbook
Dim j As Integer
Dim ir_last As Integer
wb_path = "\\swi56prof01\UserData$\heinreca\Documents\Outlook-Dateien\AllData.xlsx"
Set wb_master = Workbooks.Open(wb_path)
ir_last = wb_master.Worksheets(ws_master).Range("A" & Rows.Count).End(xlUp).Row
For Each objAtt In itm.Attachments
FileName = objAtt.DisplayName
Set wb_email = Workbooks.Open(FileName, True, True)
fID = Split(FileName, " - ")
j = wb_master.Worksheets(ws_master).Cells.Find(What:="ID", SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
wb_master.Worksheets(ws_master).Cells(ir_last + 1, j) = fID(0)
Next
I checked the Tools>references Solution. The tick at Microsoft Office 16.0 Object Library was already there.
I tried to define New Workbook instead of just Workbook.
Edit: I decided to try the late binding method and changed some of my Dims:
Dim app_master As Object
Dim wb_master As Object
Dim ws_master As Object
Dim ic_last As Integer
Followed by:
Set app_master = CreateObject("Excel.Application")
Set wb_master = app_master.Workbooks.Open(wb_path)
Set ws_master = wb_master.Sheets(1)
However now it returns an error
Variable not defined
at the line:
ic_last = ws_master.Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
It highlights "xlPrevious".
When my module2 is called, I immediatly get the "user-defined type not defined" error, on the line Dim wb_master As Workbook, and on Dim wb_email As Workbook.
There are two possible ways to solve that:
Add an Excel COM reference so the types declared will be available to VBA. This is called early-binding.
Declare objects as Object if you don't want to add an Excel COM reference. This is called late-binding.
Read more about that in the Using early binding and late binding in Automation and Early Binding vs. Late Binding: The Essential Guide for VBA Developers articles.