Take fil name from cell

[KlausW]

Hello everyone, I use this VBA code to send the current file by email. But I have a challenge. I would like it to take the file name from cell K4. Some who can help.
Any help will be appreciated.
Best regards Klaus W

Sub mail()

Dim strPath As String
Dim OutlookApp As Object, OutlookMail As Object

On Error GoTo errHandler
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookMail = OutlookApp.CreateItem(0)
strPath = Replace(ThisWorkbook.FullName, ".xlsm", ".xlsx")
Application.DisplayAlerts = False
ThisWorkbook.SaveAs strPath, 51
Application.DisplayAlerts = True

With OutlookMail
    .To = Range("k1").Text
    .CC = ""
    .BCC = ""
    .Subject = Range("k9").Value
    .Body = Range("k2") & vbCrLf & " " & vbCrLf & Range("K3") & vbCrLf & Range("K4")
    .Attachments.Add strPath
    .Send
End With

exitHere:
Set OutlookMail = Nothing
Set OutlookApp = Nothing
Application.DisplayAlerts = True
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

 

[Sanjeev1976]

Try changing strPath formula from :

strPath = Replace(ThisWorkbook.FullName, ".xlsm", ".xlsx")

to :

strPath = Range("K4").text

where K4 includes the full filename including the path and file extension or you need to revise the above formula

 

[KlausW]

Try changing strPath formula from :

strPath = Replace(ThisWorkbook.FullName, ".xlsm", ".xlsx")

to :

strPath = Range("K4").text

where K4 includes the full filename including the path and file extension or you need to revise the above formula

Hi Sanjeev1976
Your VBA code works if I write D:\Peter Jones.xlsx in K4 but if I write C:\Peter Jones.xlsx then the VBA code does not work as intended and cannot find the file and I get an error code 1004. I do not get it.
Klaus W

 

[Sanjeev1976]

I believe when working with VBA, it’s essential to consider file system permissions. By default, non-administrators cannot write directly to the root of the C drive due to User Account Control (UAC) restrictions. You can try to access the file from a folder on C drive where you have permission instead from the root.

 

[KlausW]

I believe when working with VBA, it’s essential to consider file system permissions. By default, non-administrators cannot write directly to the root of the C drive due to User Account Control (UAC) restrictions. You can try to access the file from a folder on C drive where you have permission instead from the root.

Hi Sanjeev1976
I see what you mean, your solution works. And I'm really happy about that. I just want to find out if I can rename the file from cell K4. Also send it directly. Many thanks for the help, many greetings from Klaus Denmark