Expanding an old VBA script for send Excel-sheet as PDF on e-mail

[Bartster]

Hi!

I have been browsing the forum and found a very good code for sending an excel sheet as PDF on e-mail by a user ZDI (?). I modified it a bit - picking cell-references into the body text of the e-mail, subject and To/Cc fields.

The code looks like this now:

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("A1")
 
  ' 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)
   
    ' Prepare e-mail
    .Subject = "Purches order from Company X - Ordernumber: " & Range("C6")
    .To = Range("P18") ' <-- Put email of the recipient here
    .CC = Range("I8")  ' <-- Put email of 'copy to' recipient here
    .Body = "Hi," & vbLf & vbLf _
          & "Attached is our purchase order." & vbLf & vbLf _
          & "Ref:  Order " & Range("C6") & vbLf _
          & "Project:  " & Range("C5") & vbLf & vbLf _
          & "Delivery address: " & Range("C7") & vbLf _
          & "Delivery week: " & Range("C8") & vbLf & vbLf _
          & "Please confirm delivery date back on e-mail to " & Range("I8") & vbLf & vbLf _
          & "Best regards," & vbLf _
          & Range("I6") & vbLf _
          & "Company X" & vbLf & vbLf
    .Attachments.Add PdfFile
   
    ' Try to send
    On Error Resume Next
    .Send
    Application.Visible = True
    If Err Then
      MsgBox "E-mail was not sent.", vbExclamation
    Else
      MsgBox "E-mail sent successfully.", vbInformation
    End If
    On Error GoTo 0
   
  End With
 
  ' Delete PDF file
  Kill PdfFile
 
  ' Quit Outlook if it was created by this code
  If IsCreated Then OutlApp.Quit
 
  ' Release the memory of object variable
  Set OutlApp = Nothing
 
End Sub

I have two Scenarios I'd like to figure out:

1. How can I force the PDF-file name to be something else than what it is now in the code ? (I am not a coder - just trying to copy/paste whatever I find ). I want the filename to be something like "Purchase order <cell reference="" here="" with="" number="" in="" cell="" c6=""> from Company X".pdf</cell>
2. A "Save file" button with a script that can do the following:
   - Save the file with filename "Purchase order <cell reference="" here="" with="" number="" in="" cell="" c6="">"
   - Location of the file to be \\servername"directory name with spaces""another directory""<cell reference="" with="" number="" in="" cell="" c6=""> <cell reference="" with="" name="" in="" c5="">"\ if this directory does not excist - create it. And if file exists, save it with same filename but just add a consecutive number after it (2, 3, 4, ...)</cell></cell></cell>

<cell reference="" here="" with="" number="" in="" cell="" c6=""><cell reference="" with="" number="" in="" cell="" c6=""><cell reference="" with="" name="" in="" c5="">
Is Scenario 2 even possible ? If so I would be super happy!

Best regards,
Martin Bergholtz
"Excel copy/paste expert"

EDIT
Just needed to add that the filename in 1. has to include the number which is written in Cell C6 - e.g. "Purchase order from Company X"</cell></cell></cell>