CODEWITHSUNDEEP
vbaexceloutlook
Del
Del

Reputation: 80

Finding the LastRow from Excel Attachment via Outlook VBA

I've been searching for an answer to this particular issue but I can't seem to find one. I am trying to combine multiple Excel-based lists that I receive as e-mail attachments. Just to give a little context, this macro has been working for almost two years without error but I recently switched it from a system running Excel 2007 & Outlook 2010 to a system running Excel 2007 & Outlook 2007.

The following line is giving me an 1004: Application-defined or object-defined error:

LR = xlAtt.ActiveSheet.Range("A" & xlAtt.ActiveSheet.Rows.Count).End(xlUp).Row

in context the code is:

Private Sub ProcessAttachments(olFolder As Outlook.MAPIFolder)

Dim xlApp As Object, xlAtt As Object
Dim LR As Long

Dim olItem As Outlook.MailItem
Dim count As Integer

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False

For count = olFolder.Items.Count To 1 Step -1
  Set olItem = olFolder.Items.Item(count)

  If olItem.Class = olMail And olItem.Attachments.Count > 0 Then
    'Omitted a few lines here that verify if the attachment is an Excel file
    'and then saves it to a folder
    Set xlAtt = xlApp.Workbooks.Open("pathToFile")
    xlAtt.Activate
    LR = xlAtt.ActiveSheet.Range("A" & xlAtt.ActiveSheet.Rows.Count).End(xlUp).Row

'More VBA after

The above is just a snippet of the code but hopefully gives enough context.

I have tried testing each individual piece of the line giving me an error and I've been able to narrow it down to the .End(xlUp).Row portion of the line.

Any help is appreciated.

Upvotes: 2

Views: 1089

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

Outlook 2007 must not recognize the Excel Constants, whereas OL 2010 does.

Instead of writing xlUp write the enumeration for xlUp which is -4162.

So your code would look like this:

LR = xlAtt.ActiveSheet.Range("A" & xlAtt.ActiveSheet.Rows.Count).End(-4162).Row

To find any enumeration for any constant in Excel VBA, once inside the VBE, press F2 to open the Object Browser and type the constant into the box next to the binoculars, then click the binocular. Click on the constant in the search results and the box at the bottom will show the enumeration.

Alternatively, you could set a constant variable to the enumeration and still use xlUp in your syntax by:

Constant xlUp = -4162

Upvotes: 2

Related Questions