Save pdf to a specific folder

[trevolly]

Hi all

I'm working on a workbook called "1282 Final" that when a "submit" button is pressed the vba code copies data to another sheet in the workbook, creates a pdf from the active worksheet, names the pdf in a very specific format, attaches the pdf to an email and then saves the pdf to the same folder path as where the "1282 Final" workbook is.

I'm really happy with how it all works but I'd like for when the pdf exports it saves in to a specific folder which has the path of "X:\Airfield Operations\2022 Airfield Inspection and Data\CA1282 Bird Strikes\PDF" rather than just where the workbook is. I do need the file name to be as the coding names it.......for example "1282 Final_Serial 28 - 27-11-2022"

I've attached the part of the vba code that refers to the creation of the pdf.... could anyone please help?

Thank you

Sheets("Birdstrike").Select

'email and pdf
Dim IsCreated As Boolean
  Dim I As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
 
  ' Title area
  Title = Range("C6")
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.FullName
  I = InStrRev(PdfFile, ".")
  If I > 1 Then PdfFile = Left(PdfFile, I - 1)
  PdfFile = PdfFile & "_Serial" & " " & Title & " - " & Format(Now(), "dd-mm-yyyy") & ".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)
   
    ' E-mail address and content
    .Subject = "1282 Bird Strike Form - Serial" & " " & Title & " - " & ActiveSheet.Range("F6")
    .To = "fredbloggs@me.com" ' <-- Put email of the recipient here
    .Body = "Hi Trevor," & vbLf & vbLf _
          & "Please find attached a PDF of the 1282 for the recent Wildlife Strike Incident." & vbLf & vbLf _
          & "Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not generated", vbExclamation
    End If
    On Error GoTo 0
   
  End With
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing

 

[Sequoyah]

Hi trevolly,
try this code, I just inserted a variable corresponding to your path

Sheets("Birdstrike").Select

'email and pdf
Dim IsCreated As Boolean
  Dim I As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  Dim MyPath As String
 
  ' Title area
  Title = Range("C6")
 
 ' Define path
 MyPath = "X:\Airfield Operations\2022 Airfield Inspection and Data\CA1282 Bird Strikes\PDF\"
 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Name 'File name without path
  I = InStrRev(PdfFile, ".")
  If I > 1 Then PdfFile = Left(PdfFile, I - 1)
  PdfFile = MyPath & PdfFile & "_Serial" & " " & Title & " - " & Format(Now(), "dd-mm-yyyy") & ".pdf"

 

[trevolly]

Hey @Sequoyah , thanks for your reply. This works perfectly thank you so much.

 

[trevolly]

Hi all

Earlier this week I asked for help with exporting an excel sheet as a pdf to a specific folder.... I was helped and it worked fine but in the last couple of days the pdf doesn't save in to the specific path I've listed - only in to the same folder that the original workbook is in. I've listed the part of vba that relates to the creation of the pdf, attaching it to an email and then exporting it to the specified folder and wondered if anyone would be able to see any glaring errors? I need the PDF to save in to a folder as "CA1282 V2_Serial 5 - 02-12-22" for example (the serial no - retrieved from cell C6 on the worksheet, and date will change to the current date). Maybe I've accidently changed something that is tripping the vba up? I'd appreciate any help

Kind Regards

Sheets("Birdstrike").Select

'email and pdf
Dim IsCreated As Boolean
  Dim I As Long
  Dim PdfFile As String, Title As String
  Dim OutlApp As Object
  Dim MyPath As String
 
  ' Title area
  Title = Range("C6")
 
 ' Define path
 MyPath = "X:\Airfield Operations\2022 Airfield Inspection and Data\CA1282 Wildlife Strikes\PDF"

 
  ' Define PDF filename
  PdfFile = ActiveWorkbook.Name 'File name without path
  I = InStrRev(PdfFile, ".")
  If I > 1 Then PdfFile = Left(PdfFile, I - 1)
  PdfFile = "CA1282" & "Serial" & " " & Title & " - " & Format(Now(), "dd-mm-yyyy") & ".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)
   
    ' E-mail address and content
    .Subject = "1282 Bird Strike Form - Serial" & " " & Title & " - " & ActiveSheet.Range("F6")
    '.To = "fredbloggs@me.com" ' <-- Put email of the recipient here
    '.CC =  'CC details
    .Body = "Hi Vicki," & vbLf & vbLf _
          & "Please find attached the main information, photos and a PDF of the CA1282 form for a recent Wildlife Strike Incident." & vbLf & vbLf _
          & "Kind Regards," & vbLf _
          & Application.UserName & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Display
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not generated", vbExclamation
    End If
    On Error GoTo 0
   
  End With

 

[Sequoyah]

Hi trevolly,
it's possible you forgot the final slash

 MyPath = "X:\Airfield Operations\2022 Airfield Inspection and Data\CA1282 Bird Strikes\PDF\"

 

[trevolly]

Hey @Sequoyah thanks for replying. I’ve tried that and it didn’t help. The workbook is in X:\Airfield Operations\2022 Airfield Inspection and Data\CA1282 Wildlife Strikes (the folder below where I want the pdf to save to). I’ll keep looking!!

 

[Sequoyah]

Hi trevolly,
you also need to enter the path

PdfFile =  MyPath & "CA1282" & "Serial" & " " & Title & " - " & Format(Now(), "dd-mm-yyyy") & ".pdf"

 

[trevolly]

That was it!!! Thank you so much @Sequoyah Adding that got it working. Thank you for your time and patience.