0

I am trying to automate copying and sending emails. However I get an error on line (sh.Range("A1:D200" & lr).Select) if I change the 200 to 50 it works but with 200 it does not work. The error I get is 1004 out of range.

Option Explicit

Sub Send_Email_With_snapshotDyka()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Dyka PVC")

Dim lr As Integer
lr = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

sh.Range("A1:D200" & lr).Select

With Selection.Parent.MailEnvelope.Item
    .to = sh.Range("H3").Value
    .Subject = sh.Range("H4").Value
    .send

End With

MsgBox "done"

End Sub
Kirill Matrosov
  • 5,564
  • 4
  • 28
  • 39
Bjorn Zonneveld
  • 3
  • 3
  • I made a button inside the sheet and the macro runs when clicked so it is Always selected. Do you mean the last cell that is used? D210 – Bjorn Zonneveld Sep 25 '19 at 08:17

1 Answers1

1

There is an issue with the following line:

sh.Range("A1:D200" & lr).Select

What happens is you fill the end of the range in (D200) and then add the value of lastrow to that end range. Say lastrow is 50, excel will then see the range that you set as "D200" & "50" making it "D20050" which is obviously not what you want. Amend the line like so:

sh.Range("A1:D" & lr).Select

Which gives you the range of "A1:D" & "50", making it "A1:D50" which is the correct output.

Plutian
  • 2,276
  • 3
  • 14
  • 23
  • You're welcome. Consider accepting my answer if your issue is now resolved. Also [this might be a useful read to you](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) for this and future projects. – Plutian Sep 25 '19 at 08:25