KlausW
Active Member
- Joined
- Sep 9, 2020
- Messages
- 445
- Office Version
- 2016
- Platform
- Windows
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
Any help will be appreciated.
Best regards Klaus W
VBA Code:
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