title: E-mailing selected Range in Excel/Outlook
body: <p>I have the following code (courtesy of <a href="http://www.rondebruin.nl/win/s1/outlook/bmail3.htm" rel="nofollow">Ron de Bruin</a>) and am trying to figure out how to customize it for my own use. </p>
  
  <p>When I run the code, nothing happens (because of the Error Handler that stops the macro on the error "Subscript out of range"), </p>
  
  <p>however when I change the line:</p>
  
  <pre><code>ActiveWorkbook.EnvelopeVisible = False
  </code></pre>
  
  <p>to:</p>
  
  <pre><code>ActiveWorkbook.EnvelopeVisible = True
  </code></pre>
  
  <p>The visible envelope allows me to make the selection and choose who I am sending to, etc.                                   </p>
  
  <p>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]</p>
  
  <p>The code I am running is:</p>
  
  <pre><code>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
  </code></pre>