E-mailing selected Range in Excel/Outlook

Question

I have the following code (courtesy of Ron de Bruin) and am trying to figure out how to customize it for my own use.

When I run the code, nothing happens (because of the Error Handler that stops the macro on the error "Subscript out of range"),

however when I change the line:

ActiveWorkbook.EnvelopeVisible = False

to:

ActiveWorkbook.EnvelopeVisible = True

The visible envelope allows me to make the selection and choose who I am sending to, etc.

I am wondering why it is encountering the "out of range" error and if it is possible to have the process go through automatically without the need for me to enter things after the event fires [it is being fired from a Workbook_Open() event - if that makes any difference, and the selection is being made in another worksheet (Worksheet("ValLog")) within the same workbook]

The code I am running is:

Private Sub workbook_open()

Dim AWorksheet As Worksheet
Dim Sendrng, rng As Range
Dim answer As Integer

On Error GoTo StopMacro

answer = MsgBox("Do you want to send e-mail notifications of upcoming tours?", vbYesNo)

If answer = vbYes Then

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

Set Sendrng = Worksheets("ValLog").Range("B5:K12").Select

With Sendrng

    'Select the range you want to mail
    Range("B5:K12").Select

    ' Create the mail and send it
    ActiveWorkbook.EnvelopeVisible = True
    With .Parent.MailEnvelope

        .Introduction = "Test Test Test"

        With .Item
            .To = "[email protected]"
            .CC = ""
            .BCC = ""
            .Subject = "Why, Error?"
            .Send
        End With

    End With

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

End With

Else
'Do Nothing
End If

End Sub

Answer 1

First off, you have not declared SendRange As a range. The line:

Dim SendRange, rng As Range

declares SendRange As a variant and rng As a range. change this to:

Dim SendRange As Range, rng As Range

Second, change the line:

Set Sendrng = Worksheets("ValLog").Range("B5:K12").Select

to:

Set Sendrng = Worksheets("ValLog").Range("B5:K12")

You are trying to set the range here, not select it.

Then, change:

Range("B5:K12").Select

to:

.Select

What you are actually doing in your code is selecting the range in the activesheet (as you have not defined the sheet before range). By putting this in a "With" statement, every statement with a "." before it will inherit the term from the with statement. Thus with ".Select" you are actually running "Sendrng.Select"