I need the PDF title to be the same as a cell in the worksheet

[JackPG]

Ok so I am really new to VBA but I have been hitting the books pretty hard and have a couple of the basics down. I’m in a class at college right now and am finishing up my final project. My goal is to be able to click a button that I have made and use the macro to create a PDF of the active worksheet, then send that worksheet in an email. I found some code on this site that does almost everything that I need but it names the PDF the Active workbook title so it reads AgrilitixWorkbook.pdf because that’s how I have saved the workbook. These are invoices for a real company and as a vendor they need to be able to send the PDF as the invoice number, for example 20071.pdf . The invoice number is found in a cell on the worksheet. I’m in a huge bind as this needs to be turned in on the 13^th of this month and have exhausted all my other resources. My own professor says that its over his head. Just so everyone knows the cell I am referencing is H5 this has a formula that changes the invoice number every time one invoice is uploaded to the database I created. I am using Excel 2016 in windows 10 I really appreciate any help given. Here is my code.

Sub Create_pdf_send_pdf_via_email()


Dim IsCreated As Boolean
Dim i As Long
Dim PdfFile As String, Title As String
Dim OutlApp As Object

' Invoice
Title = Range("H5")

' 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 = "Invoice#" & " " & Worksheets("Invoice").Range("H5").Value
.To = "" ' <-- Put email of the recipient here
.CC = "" ' <-- Put email of 'copy to' recipient here
.Body = "" & vbLf & vbLf _
& "" & vbLf & vbLf _
& "Regards," & vbLf _
& Application.UserName & 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 "You bout to get paid!", 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

 

[baitmaster]

welcome to the board

You're going through a complex process to try and work out what the file name "should" be, but it doesn't matter because you're killing it at the end anyway. Just tell VBA what it's going to be called

' Define PDF filename
PdfFile = ActiveWorkbook.Path & "/" & Title & ".pdf"