Error 1004 - Method 'Range' of object '_Worksheet' failed.

[sampal]

I am the above error with my variable erow

Sub autoemail_customer()

Dim sh As Worksheet
Dim sh3 As Worksheet
Dim o As Integer
Dim lrow As Long
Dim erow As Range

Set sh = ThisWorkbook.Sheets("Pivot")
Set sh3 = ThisWorkbook.Sheets("Email Contact")
Application.ScreenUpdating = False
lr = sh3.Range("a" & Application.Rows.Count).End(xlUp).Row - 1
For o = 2 To lr
Set erow = sh3.Range("A" & i).Value
sh.Activate

sh.Range("B1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer name"). _
ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer name"). _
CurrentPage = erow
ActiveWorkbook.EnvelopeVisible = True
sh.Range("a1:h" & lr).Select

With Selection.Parent.MailEnvelope.Item

.To = Sheets("Email Contact").Range("b" & i).Value
.CC = ""
.Subject = Sheets("Email Contact").Range("a" & i).Value & "Customer invoices : Invoices Overdue"

.Display

End With
Next
End Sub


Any idea why ??

 

[eduzs]

If erow is a range data type this is invalid:
Set erow = sh3.Range("A" & i).Value (Setting a value to a range variable)
Should be something like:
Set erow = sh3.Range("A" & i).range
Or, if erow is value you should modify to
Dim erow as double/integer/etc.

 

[whatsup]

Now I am stuck on another hurdle : See highlighted in bold, the with statement: any ideas ?

Sub autoemail_customer()

Dim sh As Worksheet
Dim sh3 As Worksheet
Dim o As Integer
Dim lrow As Long

Set sh = ThisWorkbook.Sheets("Pivot")
Set sh3 = ThisWorkbook.Sheets("Email Contact")
Application.ScreenUpdating = False
lr = sh3.Range("a" & Application.Rows.Count).End(xlUp).Row - 1
For o = 2 To lr
sh.Activate

sh.Range("B1").Select
ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer name"). _
ClearAllFilters

ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer name"). _
CurrentPage = Sheets("Email Contact").Range("A" & o).Value

ActiveWorkbook.EnvelopeVisible = True
'sh.Range("a1:h" & lr).Select

With Selection.Parent.MailEnvelope.Item

.To = Sheets("Email Contact").Range("b" & i).Value
.CC = ""
.Subject = Sheets("Email Contact").Range("a" & i).Value & "Customer invoices : Invoices Overdue"

.Display

End With
Next
End Sub

 

[DanteAmor]

I am the above error with my variable erow

Any idea why ??

If you declare the sheets as objects, I recommend you use the object in the code.


For example:

[COLOR=#333333][FONT=Verdana]ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer name").[/FONT][/COLOR][COLOR=#333333][FONT=Verdana]CurrentPage = erow[/FONT][/COLOR]

By:

[COLOR=#ff0000]sh[/COLOR].PivotTables("PivotTable1").PivotFields("Customer name").CurrentPage = [COLOR=#ff0000]c.Value[/COLOR]

---------
Try this. I made some changes to use the objects of the sheets and the cells of the "Email Contact" sheet

Sub autoemail_customer()
    Dim sh As Worksheet, sh3 As Worksheet, c As Range
    
    Application.ScreenUpdating = False
    
    Set sh = ThisWorkbook.Sheets("Pivot")
    Set sh3 = ThisWorkbook.Sheets("Email Contact")
    
    For Each c In sh3.Range("A2", sh3.Range("A" & Rows.Count).End(xlUp))
        sh.PivotTables("PivotTable1").PivotFields("Customer name").ClearAllFilters
        sh.PivotTables("PivotTable1").PivotFields("Customer name").CurrentPage = c.Value
        
        ActiveWorkbook.EnvelopeVisible = True
        With sh.Range("A1", sh.Range("H" & Rows.Count).End(xlUp)).Parent.MailEnvelope.Item
            .To = c.Offset(0, 1).Value
            .CC = ""
            .Subject = c.Value & " Customer invoices : Invoices Overdue"
            .Display
        End With
    Next
End Sub

Let me know if you have any doubt

 

[whatsup]

I ran this , it gives me Method Mailenvelope of object_worksheet Failed

 

[DanteAmor]

I ran this , it gives me Method Mailenvelope of object_worksheet Failed

Enter VBA menu / References / and verify that they are checked:
- Ole Automation
- Microsoft office nn.n Object library

That's it or the excel version does not have that method.
Then you must send your emails by outlook or by another email eg. gmail