title: VBA Excel creating Outlook email subject and body blank
body: <p>I've got an Excel spreadsheet built by someone else that sends an email to a group via a scheduled task, or at least used to. It recently stopped working. I don't have the time to rebuild his whole Rube Goldberg / Wile E. Coyote system at the moment, so I'm trying to fix it. </p>
  
  <p>In one of the excel documents, this code exists</p>
  
  <pre><code>Set rng = Nothing
  On Error Resume Next
  Set rng = Sheets("Weight").Range("A2")
  On Error GoTo 0
  
  If rng Is Nothing Then
      MsgBox "The selection is not a range or the sheet is protected" &amp; _
             vbNewLine &amp; "please correct and try again.", vbOKOnly
      Exit Sub
  End If
  
  With Application
      .EnableEvents = False
      .ScreenUpdating = False
  End With
  
  Set OutApp = CreateObject("Outlook.Application")
  Set OutMail = OutApp.CreateItem(0)
  
  On Error Resume Next
  With OutMail
      .To = "eric.lizotte@.com"
      .CC = ""
      .BCC = ""
      .Subject = Sheets("Weight").Range("A1")
      .HTMLBody = Convert.ToString(rng)
      .Send
  End With
  </code></pre>
  
  <p>whenever I see so many "on error resume next" in something I grit my teeth.</p>
  
  <p>What's happening is that it "works" but the email it sends is blank. In debug I can see that the subject content and body content both exist and have values. I rewrote it to be </p>
  
  <pre><code>    OutMail.To = "eric.lizotte@.com"
      OutMail.CC = ""
      OutMail.BCC = ""
      OutMail.Subject = Sheets("Weight").Range("A1")
      OutMail.HTMLBody = Convert.ToString(rng)
      OutMail.Send
  </code></pre>
  
  <p>and threw a debug point on the send, and checked the properties of outmail, and the subject and htmlbody have values. I can't figure out why after the send they are blank.</p>