Changing file name after exporting a worksheet to PDF & Emailing

[Hayf13]

Hi Guys, hoping for some help, couldn't find a solution browsing the forums;

Have adapted some code as below which does work in theory, it creates a temp pdf from a set range, attaches it to an email and then Kills the file when complete - is there a way to rename this pdf attachment to a cell value in a named range before it gets attached? IE could I name the file based on the value BZ2 before attaching it?

Any help would be marvelous! Thankyou to all

Set xpdfemailtitle = Range("bz2")

strPath = ActiveWorkbook.FullName
lngPos = InStrRev(strPath, ".")
strPath = Left(strPath, lngPos) & "pdf"
Range("a1:k179").ExportAsFixedFormat xlTypePDF, strPath
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
emailItem.Subject = xpdfemailtitle
emailItem.Attachments.Add strPath
emailItem.Display
Set emailItem = Nothing
Set emailApplication = Nothing
Kill strPath
End Sub

 

[breynolds0431]

This should work for you.

Sub exportfile()

Dim pdfTitle As String: pdfTitle = Range("bz2").Value
Dim strPath As String: strPath = ThisWorkbook.Path & "/" & pdfTitle & ".pdf"

Range("a1:k179").ExportAsFixedFormat xlTypePDF, strPath

Dim oApp As Outlook.Application: Set oApp = CreateObject("Outlook.Application")
Dim oMail As Outlook.MailItem: Set oMail = oApp.CreateItem(0)

With oMail
    .Subject = pdfTitle
    .Attachments.Add strPath
    .Display
End With

Set oMail = Nothing
Set oApp = Nothing

Kill strPath

End Sub

 

[Hayf13]

Brilliant! Thankyou - knew it was in that top line - everything works a treat, much obliged!

"Dim pdfTitle As String: pdfTitle = Range("bz2").Value
Dim strPath As String: strPath = ThisWorkbook.Path & "/" & pdfTitle & ".pdf""