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

[USAMax]

This function has been working perfectly fine until now and nothing has changed. The OutMail.To function is failing and if I replace the Named Range with the string value of cell B11 it works. This fails everywhere this command uses a Named Range.

Function Mail_workbook_Outlook(Recipient As String)
'Working in Excel 2000-2013
'Mail a copy of the ActiveWorkbook with another file name
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
    Dim wb1 As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileExtStr As String
    Dim OutApp As Object
    Dim OutMail As Object

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set wb1 = ActiveWorkbook

    'Make a copy of the file/Open it/Mail it/Delete it
    'If you want to change the file name then change only TempFileName
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Johns New Item Form " & Format(Now, "mm-dd-yy")
    FileExtStr = "." & LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

    wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

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

    'On Error Resume Next
    Select Case Recipient
        Case "Buyer"
            With OutMail
                .To = Range("SF.BuyerEmail")
                .cc = ""
                .BCC = ""
                .Subject = "Johns New Item Form " & Range("SF.SupplierContactName") & " " & Range("SF.SupplierInformation")(1) & " " & Format(Now(), "mm/dd/yyyy")
                .Body = "Attention " & Range("SF.BuyerName") & " -  Attached is the Johns New Item Form for your review."
                .Attachments.Add TempFilePath & TempFileName & FileExtStr
                .Send   'or use .Display
            End With
        Case "Supplier"
            With OutMail
                .To = Range("SF.SupplierEmail")
                .cc = ""
                .BCC = ""
                .Subject = "Johns New Item Form " & Range("SF.SupplierContactName") & " " & Range("SF.SupplierInformation")(1) & " " & Format(Now(), "mm/dd/yyyy")
                .Body = "Attention " & Range("SF.SupplierContactName") & " -  Attached is the Johns New Item Form for your review."
                .Attachments.Add TempFilePath & TempFileName & FileExtStr
                .Send   'or use .Display
            End With
        Case "Final"
            With OutMail
                .To = "MDSM@Johns.com"
                .cc = ""
                .BCC = ""
                .Subject = "Johns New Item Form " & Range("SF.BuyerName") & " " & Range("SF.SupplierInformation")(1) & " " & Format(Now(), "mm/dd/yyyy")
                .Body = "Attention " & Range("SF.SupplierContactName") & " -  Attached is the Johns New Item Form for your review."
                .Attachments.Add TempFilePath & TempFileName & FileExtStr
                .Send   'or use .Display
            End With
        Case Else
            End
    End Select
    On Error GoTo 0

    'Delete the file
    Kill TempFilePath & TempFileName & FileExtStr

    Set OutMail = Nothing
    Set OutApp = Nothing

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Function

 

[RickXL]

I don't know but I suspect Outlook does not like something it finds.

Try to do it in pieces. Define a temporary string along with the other Dims, then assign the range to the string , finally assign the string to .To.

For example:

    Dim TempEmail As String
...
...
            With OutMail
                TempEmail = Range("SF.BuyerEmail")
                .To = TempEmail
...
...
            With OutMail
                TempEmail = Range("SF.SupplierEmail")
                .To = TempEmail

If it works then good, if not, then you will know which half of the operation the problem is in. That is, either in the part that assigns the range to the string or the string to the property.

Regards,