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.
Rich (BB code):
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