Changing file name after exporting a worksheet to PDF & Emailing

Hayf13

New Member
Joined
Jul 27, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
This should work for you.

VBA Code:
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
 
Upvote 0
Solution
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""
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top