CODEWITHSUNDEEP
vbaexcelemailoutlook
Eric Lizotte
Eric Lizotte

Reputation: 224

VBA Excel creating Outlook email subject and body blank

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

Answers (1)

Robin Mackenzie
Robin Mackenzie

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

Related Questions