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

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
458
NVRensburg
N
C
  • Solved
VBA attach PDF to email stopped working
Replies
8
Views
861
FormR
FormR
J
  • Question
Email issue with .Attachments.Add PdfFile
Replies
0
Views
335
jayserna
J
M
  • Solved
How To Make Shape Disappear After Mouse Click, then reappear after switch sheet.
Replies
6
Views
589
Artik
Artik
sanantonio
  • Solved
Where in this code it is specifying a filepath?
Replies
4
Views
536
Domenic
D

Forum statistics

Threads
1,217,021
Messages
6,134,065
Members
449,857
Latest member
caracal

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