Object Variable or With Block Variable not set - Pasting Outlook attachment to excel sheet

[Johnnyze]

Hi Guys,

Hopefully someone can help me with this issue - I'm fairly new to VBA.
I found the following piece of code online which is meant to take data from an email attachment (csv) and paste to a certain sheet in a local Excel file.
Code is below and I have highlighted where I am getting errors and what I have already tried.

Option Explicit 
Sub OpenTicketsByClient() '#1 - In the code that I found originally, 'olitem as Outlook.MailItem' was set as a parameter of the subroutine - Excel macro wouldn't allow any parameters here


Dim xlApp As Object
Dim xlWB As Object
Dim xlTempWB As Object
Dim xlSheet As Object
Dim xlTempSheet As Object
Dim lngTempLast As Integer
Dim lngLast As Integer
Dim strFname As String
Dim strTempPath As String
Dim bXLStarted As Boolean
Dim olitem As Outlook.MailItem
'Set olitem = New Outlook.MailItem '#3 - After error 1 below, I set 'olitem' here. An error was then thrown at this line 'Compile error: Invalid use of New keyword'




Const strPath As String = "C:\MyLocalWorkbook.xlsx"        'the path and name of the local workbook
strTempPath = Left(strPath, InStrRev(strPath, "my.name@myCompany.com/Inbox/Project/sample.csv")) 'The path of the temporary file in Outlook: email_address/inbox/folder_within_inbox/attachment_name.csv 


On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err <> 0 Then
    Set xlApp = CreateObject("Excel.Application")
    bXLStarted = True
End If
xlApp.Visible = True


On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
Set xlSheet = xlWB.Sheets("Sheet1") 'The sheet in the local workbook


'Process the message attachment

'Set olitem = New Outlook.MailItem '#4 - I also tried setting 'olitem' here. It threw the same error as #3


With olitem.Attachments.Item(1)  '#2 - When running the macro, an error is thrown at this line: 'Object variable or With block variable not set'


    If Right(.DisplayName, 4) = "csv" Then
        lngLast = xlSheet.Range("B" & xlSheet.Rows.Count).End(-4162).Row
        strFname = strTempPath & .DisplayName
        .SaveAsFile strFname
        Set xlTempWB = xlApp.Workbooks.Open(strFname, editable:=True)
        Set xlTempSheet = xlTempWB.Sheets("")
        lngTempLast = xlTempSheet.Range("B" & xlTempSheet.Rows.Count).End(-4162).Row
        xlSheet.Range("B" & lngLast + 1, "S" & lngLast + lngTempLast - 1).Value = xlTempSheet.Range("B2", "S" & lngTempLast).Value
        xlWB.Save
    End If


End With
xlWB.Close SaveChanges:=True
xlTempWB.Close SaveChanges:=False
If bXLStarted Then
    xlApp.Quit
End If


Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
Set xlTempWB = Nothing
Set xlTempSheet = Nothing
Set olitem = Nothing


End Sub

#1 - In the code that I found originally, 'olitem as Outlook.MailItem' was set as a parameter of the subroutine - Excel macro wouldn't allow any parameters here
#2 - When running the macro, an error is thrown at this line: 'Object variable or With block variable not set'
#3 - After error 1 below, I set 'olitem' here. An error was then thrown at this line 'Compile error: Invalid use of New keyword'
#4 - I also tried setting 'olitem' here. It threw the same error as #3

If anyone can help me out with these errors or provide a work around that would be great!!
Let me know if there's anything I was unclear about.

Thanks in advance,
Johnny