Sending emails generates Run-time error '-2147417851 (80010105)': Method 'To' of object'_MailItem' failed

Question

I want to send an email via Outlook with workbook attached.

The "To" and "CC" fields are not populating, which results in the email not sending.

The "To" and "Cc" fields will change each time the Excel workbook is used so need to use data in H16 and H19 to populate who the email is to be sent to.

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "<Font Size=2 Font Face=Verdana> Hi,<br><br>" & _
          "Please review and approve.<br>"

On Error Resume Next

With OutMail
    .Display
    .To = ActiveSheet.Range("H16")
    .CC = ActiveSheet.Range("H9")
    .BCC = ""
    .Subject = "[APPROVAL REQUIRED]"
    .HTMLBody = strbody & "<br>" & .HTMLBody
    .Recipients.ResolveAll
    .Attachments.Add Application.ActiveWorkbook.FullName
    .Send
End With

On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing

Without On Error Resume Next I get:

Run-time error '-2147417851 (80010105)': Method 'To' of object'_MailItem' failed

Answer 1

You now understand why:

On Error Resume Next
  :  :  :  :
On Error GoTo 0

is never used like this. Used like this it conceals all errors so is only used by people who like mysterious failures. If you would like, I will add a section explaining how On Error Resume Next is supposed to be used.

"Run-time error '-2147417851 (80010105)': Method 'To' of object'_MailItem' failed" is not the most helpful of error messages. All you know something about ActiveSheet.Range("H16") is not compatible with .To.

I would add:

Debug.Print ActiveSheet.Name

Is the ActiveSheet the one you want? ActiveSheet is a property to avoid unless you really need a different active worksheet every time you run the macro. Worksheets("Email Data") is much safer and is helpful for a future maintenance programmer.

If it is the correct worksheet, what is the value of ActiveSheet.Range("H16")? Is it a string or something that can be converted to a string automatically? Try:

Dim StrTemp As String

StrTemp = ActiveSheet.Range("H16").Value
Debug.Print StrTemp
.To = StrTemp

I will be surprised if this does not reveal a problem. Please report what happens if you need further help.