Outlook attachment to excel table VBA issue

filarap

New Member
Joined
May 15, 2015
Messages
33
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
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

Any chance for some clarification?
Filarap
 

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

Threads
1,109,434
Messages
5,528,742
Members
409,832
Latest member
Misspears10

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...
  • VBA COUNTIF SOLUTION
    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...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    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...
Top