File name based upon the cell value

[ismailshajji]

Hello Guys,

I am facing a bit of an issue i hope some one will be able to put e in the correct direction.

I am trying to use the below code for attaching an active work sheet to outlook and would like to name the file name as per the cell value D9 when i type the text in the cell value the code works fine but when the cell value has a formula like =E8&"/"&E6 the code stops working.

I have posted similar question last year this wasnt problem then not sure what wrong with the below code.

Thank you so much for your help.

Sub Mail_workbook_Outlook_2()
'Working in Excel 2000-2016
'Mail a copy of the ActiveWorkbook with another file name
Dim wb1 As Workbook
Dim xSht As Worksheet
Dim TempFilePath As String
Dim TempFileName As String
Dim FileExtStr As String
Dim OutApp As Object
Dim OutMail As Object
Dim xUsedRng As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set wb1 = ActiveWorkbook
Set xSht = ActiveSheet

'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 = xSht.Range("D9").Value & " " & Format(Now, "dd-mmm-yy h-mm-ss")

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
With OutMail
.to = ""
.CC = ""
.BCC = ""
.Subject = "New Asset Form " + xSht.Range("M16").Value
.Body = ""
.Attachments.Add TempFilePath & TempFileName & FileExtStr
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
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 Sub

[iustin]

Use .text instead of .value

Please Login or Register  to view this content.

[ismailshajji]

thank you iustin , i am getting a debugg error wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr

the formula works when the value is typed but will not work when the cell has = some thing