Change PDF Filename

P

Padthelad

Board Regular
Joined
May 13, 2016
Messages
64
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I am using the code below to export an active sheet to Outlook. Most things work well, however, I want the PDF filename to be only the name of the active sheet as opposed to the full workbook name.

I know it is something simple, but for some reason cant see it or get it to work.

Any help is much appreciated.

Thanks,

Paddy



Sub AttachActiveSheetPDF()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object

' Not sure for what the Title is
Title = Range("B10")

' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)


.Subject = Title
Dim Mailadress As String
Mailadress = CStr(Range("C40").Value)
.to = Mailadress
.CC = ""
.Body = "Dear," & vbLf & vbLf _
& "Please find attached quote from " & vbLf & vbLf _
& "Do not hesitate to contact the Timber Buildings office should you have any questions or queries." & vbLf _
& "Kind regards," & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add PdfFile


On Error Resume Next
.Display
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0

End With

Kill PdfFile

If IsCreated Then OutlApp.Quit

Set OutlApp = Nothing

End Sub
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
Padthelad said:
Hi,

I am using the code below to export an active sheet to Outlook. Most things work well, however, I want the PDF filename to be only the name of the active sheet as opposed to the full workbook name.

I know it is something simple, but for some reason cant see it or get it to work.

Any help is much appreciated.

Thanks,

Paddy



Sub AttachActiveSheetPDF()
Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object

' Not sure for what the Title is
Title = Range("B10")

' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"

' Export activesheet as PDF
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PdfFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With

' Use already open Outlook if possible
On Error Resume Next
Set OutlApp = GetObject(, "Outlook.Application")
If Err Then
Set OutlApp = CreateObject("Outlook.Application")
IsCreated = True
End If
OutlApp.Visible = True
On Error GoTo 0

' Prepare e-mail with PDF attachment
With OutlApp.CreateItem(0)


.Subject = Title
Dim Mailadress As String
Mailadress = CStr(Range("C40").Value)
.to = Mailadress
.CC = ""
.Body = "Dear," & vbLf & vbLf _
& "Please find attached quote from " & vbLf & vbLf _
& "Do not hesitate to contact the Timber Buildings office should you have any questions or queries." & vbLf _
& "Kind regards," & vbLf _
& Application.UserName & vbLf & vbLf
.Attachments.Add PdfFile


On Error Resume Next
.Display
Application.Visible = True
If Err Then
MsgBox "E-mail was not sent", vbExclamation
Else
MsgBox "E-mail successfully sent", vbInformation
End If
On Error GoTo 0

End With

Kill PdfFile

If IsCreated Then OutlApp.Quit

Set OutlApp = Nothing

End Sub
Click to expand...
Hi Pat, welcome to the boards.

Please try to remember to wrap your code in the the tags when sharing macros:

[ CODE ]<--delete the spaces in the square brackets-->[ /CODE ]

Anyway, back to your problem...

I believe you just need to change this section:

Rich (BB code): 
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"


To this:
Rich (BB code): 
' Define PDF filename
PdfFile = ActiveSheet.Name
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & ".pdf"
 
Last edited:
Upvote 0
Fishboy said:
Hi Pat, welcome to the boards.

Please try to remember to wrap your code in the the tags when sharing macros:

[ CODE ]<--delete the spaces in the square brackets-->[ /CODE ]

Anyway, back to your problem...

I believe you just need to change this section:

Rich (BB code): 
' Define PDF filename
PdfFile = ActiveWorkbook.FullName
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & "_" & ActiveSheet.Name & ".pdf"


To this:
Rich (BB code): 
' Define PDF filename
PdfFile = ActiveSheet.Name
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)
PdfFile = PdfFile & ".pdf"
Click to expand...


Hi Fishboy,

Thank you for coming back to me with this. I did something similar to this earlier but it returned a runtime error. When I debug it, it highlights this:-

Rich (BB code): 
    .Attachments.Add PdfFile
[\code]

Any help you can provide is very much appreciated.

Thanks
 
Upvote 0
You don't need this:-

Code: 
i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)

As your worksheet won't have a "." in the name.

If that still doesn't work do please specify the exact error message.
 
Upvote 0
Padthelad said:
Hi Fishboy,

Thank you for coming back to me with this. I did something similar to this earlier but it returned a runtime error. When I debug it, it highlights this:-

Code: 
.Attachments.Add PdfFile
[\code]

Any help you can provide is very much appreciated.

Thanks[/QUOTE]
What error does the debug window give when that line is highlighted?

[QUOTE="Stiuart_W, post: 4533488, member: 249937"]You don't need this:-

[CODE]i = InStrRev(PdfFile, ".")
If i > 1 Then PdfFile = Left(PdfFile, i - 1)

As your worksheet won't have a "." in the name.

If that still doesn't work do please specify the exact error message.
Click to expand...
A worksheet can have a "." in the name, so it is still a valid check to carry out.
 
Upvote 0
Hi Stiuart_W,

Thank you for your advice. When I run the Macro with your amendment, it still has the attachment filename as the name of the whole workbook as opposed to just the name of the active worksheet. I am confused.

Thanks
 
Upvote 0
Padthelad said:
Hi Stiuart_W,

Thank you for your advice. When I run the Macro with your amendment, it still has the attachment filename as the name of the whole workbook as opposed to just the name of the active worksheet. I am confused.

Thanks
Click to expand...
I think he just meant you didn't need it in my updated code, however as I mentioned it is quite possible for a sheet name to include a "." in it. If you are CERTAIN that the sheet name will NEVER have a "." in it then we could potentially do away with that part entirely:

Code: 
' Define PDF filename
PdfFile = ActiveSheet.Name & ".pdf"
 
Upvote 0
Fishboy's code works for me - Just make sure your Worksheet name is NOT the same as your Workbook name!
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Modify VBA
Replies
9
Views
608
NVRensburg
N
C
  • Solved
VBA attach PDF to email stopped working
Replies
8
Views
1K
FormR
FormR
L
  • Locked
  • Question Question
How to stop VBA from creating pdf from drop-down list if the value is empty?
Replies
1
Views
281
Fluff
Fluff
J
  • Question Question
Email issue with .Attachments.Add PdfFile
Replies
0
Views
459
jayserna
J
Y
  • Solved
Create email with all currently selected sheets attached as individual pdf files
Replies
2
Views
339
Yamezz
Y

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

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