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 = "myemail@blahblah.com"
.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
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"