VBA to take screen shot of Fixed range sheet and send email

[qmozi1234]

Hi,

I am learning the ways of VBA and struggling to produce a macro that will:

1. Copy a snapshot of a fixed range B4:G34 (this will not change). This data is on the sheet "product", which is the 14th sheet in my workbook.
2. Paste Snapshot into an email and auto send.

I have produced the code below but i have 1 or 2 errors that keep occurring:
1. Variable not defined
2. Select Method of range class failed

Errors appear to related to the highlighted lines.


---------
Option Explicit
----------

Sub Send_Email_With_Snapshot1()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet14")

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

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

With Selection.Parent.MailEnvelop.Item
.to = "Test@test.com"
.Subject = "Product"
.send

End With

MsgBox "Sent"

End Sub
------

 

[Michael M]

Do you have a sheet named "Sheet14" AND
there is a typo on the envelope line....missing an "e"

With Selection.Parent.MailEnvelop.Item

 

[qmozi1234]

Thank you for your reply.
I tried the order the sheet was i.e. the 14th.
So i have changed "Sheet14" back to "Product" (which I had already tried)

Now I see Run-time error '9':
Subscript out of range.

Debugger is highlighting the line below:

Sub Send_Email_With_Snapshot1()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product")

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

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

With Selection.Parent.MailEnvelope.Item
.to = "liam.evans@aspectcapital.com"
.Subject = "IRS"
.send

End With

MsgBox "Sent"

End Sub




I have added the missing "e"!

 

[qmozi1234]

Thank you for your reply.
I tried the order the sheet was i.e. the 14th.
So i have changed "Sheet14" back to "Product" (which I had already tried)

Now I see Run-time error '9':
Subscript out of range.

Debugger is highlighting the line below:

Sub Send_Email_With_Snapshot1()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Product")

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

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

With Selection.Parent.MailEnvelope.Item
.to = "test@test.com
.Subject = "Product"
.send

End With

MsgBox "Sent"

End Sub




I have added the missing "e"!

 

[Michael M]

That error suggests that there is no sheet named "Product"
Check the spelling and make sure there are no leading / trailling spaces in the sheet name

 

[qmozi1234]

Thanks again for your response.

Again thinking the error I was receiving was due the way I was referencing that sheet I have checked that a couple of times and still no luck.

will post if/when I figure out the answer!

 

[Michael M]

Thats' odd
The code as presented in post #3 runs fine for me !!
So, if it is still giving a error 9....then there has to be an issue with the spelling of the Product sheet !