Outlook attachment to excel table VBA issue


New Member
May 15, 2015
Hi all,

I am trying to have a process where scheduled reports i receive to email inbox will be downloaded to file and attached to the bottom of existing table in a file.

I managed to create the code below using some VBA knowledge and google.
Error code i get is run time error 6 - overflow. Google advises that this is due to file size (One csv file has around 3 mb and between 25k and 30k rows.)
Error via debugger on bolded

Sub CopyAttachmentToExcel(olitem As Outlook.MailItem)

'olitem is needed in sub name as it is a variable
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
'the path and name of the local workbook
Const strPath As String = "C:\Users\testname\Desktop\Agent State Raw\State reports\February 19.xlsx"
strTempPath = Left(strPath, InStrRev(strPath, "")) 'The path of the temporary file

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("feb19") 'The sheet in the local workbook

'Process the message attachment
With olitem.Attachments.Item(1)
If Right(.DisplayName, 3) = "csv" Then
lngLast = xlSheet.Range("A" & xlSheet.Rows.Count).End(-4162).Row
strFname = strTempPath & .DisplayName
.SaveAsFile strFname
Set xlTempWB = xlApp.Workbooks.Open(strFname, editable:=True)
Set xlTempSheet = xlTempWB.Sheets("Agent State Details Report All ") 'sheet data is copied from
lngTempLast = xlTempSheet.Range("B" & xlTempSheet.Rows.Count).End(-4162).Row
'in code below H means last column it will copy to (range limit)
xlSheet.Range("A" & lngLast, "M" & lngLast + lngTempLast - 1).Value = xlTempSheet.Range("A2", "M" & lngTempLast).Value
End If

End With
xlWB.Close SaveChanges:=True
xlTempWB.Close SaveChanges:=False
If bXLStarted Then
End If
Set xlApp = Nothing
Set xlWB = Nothing
Set xlSheet = Nothing
Set xlTempWB = Nothing
Set xlTempSheet = Nothing
Set olitem = Nothing

End Sub

Any chance for some clarification?

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...