VBA error 1004 select method of worksheet class field

Question

I have got a code which sends the emails out to contacts after checking if email address is valid in column 2 and if value in column 6 is higher than 0.

If i click a button to save the workbook and try to run the macro it crashes and errors out with error: error 1004 select method of worksheet class field. After I close the error and navigate through the sheets, select some random cells, and then try to run the macro it runs fine and emails get sent out. This is the line that debugger highlights: .Parent.Select

    Private Sub SendUpdates()
 Dim aEmailList As Variant
 Dim indx As Long
 Dim rng As Range
 Dim ws As Worksheet
 Dim lRow As Long
 Dim wsORPpivot As Worksheet
 Dim wsDSPContacts As Worksheet

 With ThisWorkbook
     Set wsORPpivot = .Worksheets(cShORPpivot)
     Set wsDSPContacts = .Worksheets(cShDSPContacts)
 End With

 Set ws = ActiveSheet

 'On Error GoTo ErrHandler

 With Application
    .ScreenUpdating = False
    .EnableEvents = False
 End With

 With wsDSPContacts
    aEmailList = .Cells(1).CurrentRegion.Value
 End With

 If Not IsArray(aEmailList) Then Exit Sub

 For indx = 2 To UBound(aEmailList)
    If ValidEmail(CStr(aEmailList(indx, 2))) And aEmailList(indx, 6) > 0 Then

    wsORPpivot.PivotTables("ORP").PivotFields("DSP").CurrentPage = aEmailList(indx, 1)
    wsORPpivot.Cells.EntireColumn.AutoFit

        lRow = wsORPpivot.Cells(Rows.CountLarge, "A").End(xlUp).Row
        Set rng = wsORPpivot.Range("A1:J" & lRow)
        ActiveWorkbook.EnvelopeVisible = True
        With rng
            .Parent.Select
            With .Parent.MailEnvelope
                '.Introduction = aEmailList(indx, 4)
                With .Item
                    .To = aEmailList(indx, 2)
                    '.CC = aEmailList(indx, 3)
                    .Subject = aEmailList(indx, 4)
                    '.Intro = aEmailList(indx, 5)
                    .Send
                End With
            End With
        End With

        With wsORPpivot ' Unselect the range, clear pivot filters, fit columns
            .Cells(1).Select
            .PivotTables("ORP").PivotFields("DSP").ClearAllFilters
            .Cells.EntireColumn.AutoFit
        End With
    End If
 Next
 ActiveWorkbook.EnvelopeVisible = False
 ws.Select

ErrHandler:
ActiveWorkbook.EnvelopeVisible = False
 With Application
    .ScreenUpdating = True
    .EnableEvents = True
 End With

 Set ws = Nothing
 Set rng = Nothing
 If Err.Number <> 0 Then
    MsgBox "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description, vbCritical, cAppName
    Err.Clear
 End If

End Sub

Strictly speaking, there is zero need to `Select` the `rng.Parent` in this case. NB: [there usually isn't](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Just get ride of the `.Parent.Select` line. — David Zemens, May 23 '17 at 20:43
Makes sense. Thank you very much as it has solved the problem. — Martin, May 23 '17 at 21:17

VBA error 1004 select method of worksheet class field

0 Answers0