Reputation: 224
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.
In one of the excel documents, this code exists
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" & _
vbNewLine & "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 = "[email protected]"
.CC = ""
.BCC = ""
.Subject = Sheets("Weight").Range("A1")
.HTMLBody = Convert.ToString(rng)
.Send
End With
whenever I see so many "on error resume next" in something I grit my teeth.
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
OutMail.To = "[email protected]"
OutMail.CC = ""
OutMail.BCC = ""
OutMail.Subject = Sheets("Weight").Range("A1")
OutMail.HTMLBody = Convert.ToString(rng)
OutMail.Send
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.
Upvotes: 1
Views: 1973
Reputation: 19299
Given you are automating another application you probably should not rely on the default property of the range - just specify the property you want e.g. Value
:
OutMail.Subject = Sheets("Weight").Range("A1").Value
OutMail.HTMLBody = rng.Value
You might also try the Text
property for the HTMLBody
:
OutMail.HTMLBody = rng.Text
You can then be confident that you are assigning a String
to the mail properties which is what it expects.
Upvotes: 1