Outlook VBA: Activating a Workbook, Activating a Row, Inserting Copied Rows

Question

I have an Outlook Macro that saves attachments based on a search of an e-mail inbox. The Aggregation File is then opened, then a loop opens the first of the saved attachments and copies the "AggregateThis" named range. What I need to achieve is: 1). Activate the Aggregation File 2). Activate the Row where the result of the search for "END" is located 3). Insert the copied cells above end

The Outlook Object model is giving me trouble, this would be a total cinch in Excel VBA. Your help would mean so much!

 Dim xlApp As Object
 Set xlApp = CreateObject("Excel.Application")
 With xlApp
.Visible = True
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
.Workbooks.Open ("J:\Retail Finance\Varicent\General Teamshare Resources\Acting Mgr Assignment Bonus Aggregation.xlsx")

Dim x As Variant
i = -1
For Each x In AttachNames
Dim wb As Object
i = i + 1
Set wb = .Workbooks.Open("J:\Retail Finance\Varicent\General Teamshare Resources\Teamshare AAA\" & AttachNames(i))
Set wb = .Worksheets("Additional Assignment Bonus FRM")
            'Copies the "Aggregate This" named range from the Individual File (i)
With wb.Range("AggregateThis")
    .Copy
End With
            'Switches focus to Aggregation File
Set wb = .Workbooks("J:\Retail Finance\Varicent\General Teamshare Resources\Acting Mgr Assignment Bonus Aggregation.xlsx")
With wb
    .Activate                           '#1). I want to put focus on this file it throws an error
End With

'Find EndRow in the Aggregation File
Set wb = .Worksheets("Additional Assignment Bonus FRM").Cells.Find("End")
With wb
         .ActivateRow                  '#2).This throws an error
         .PasteSpecialInsertRows       '#3). This doesnt work
End With
Next

in order to use .Activate you need to turn on screen updating. — Sorceri
That is what was holding up my whole project! That blasted screen updating. I will post an answer to my question noting the side-effects of turning screenupdating off. You are then man @Sorceri ! — Richard Pullman
@Sorceri , can you help me out with the Syntax on the Paste Command: It is the 5th line from the bottom in my answer submission. Otherwise, everything else works. — Richard Pullman

Richard Pullman Richard Pullman · Accepted Answer · 2016-04-08T13:01:08

The original code didn't work properly because, for .Activate to work, ScreenUpdating must be set to True (which it is by default).

Dim xlApp As Object
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = True    '## Was set to False in code originally##
.Workbooks.Open ("J:\Retail Finance\Varicent\General Teamshare Resources\Acting Mgr Assignment Bonus Aggregation.xlsx")
Dim x As Variant
i = -1
For Each x In AttachNames
Dim wb As Object
i = i + 1
Set wb = .Workbooks.Open("J:\Retail Finance\Varicent\General Teamshare Resources\Teamshare AAA\" & AttachNames(i))
With xlApp
   .Worksheets("Additional Assignment Bonus FRM").Range("AggregateThis").Copy     'Copies Range
End With
Set wb = .Workbooks.Open("J:\Retail Finance\Varicent\General Teamshare Resources\Acting Mgr Assignment Bonus Aggregation.xlsx")
With wb
    .Worksheets("Additional Assignment Bonus FRM").Rows.Find("End").Select
    .Worksheets("Additional Assignment Bonus FRM").Activerange.Paste  '##This needs to be fixed##, will edit response soon.
End With
Next
End With
End Sub

Outlook VBA: Activating a Workbook, Activating a Row, Inserting Copied Rows

1 Answers