Need to email pdf of active sheet named as a cell value rather than named as the entire workbook.

[cdg786]

Nearly finished setting up an auto-purchase order workbook. Last issue is that the uto exported and emailed pdf of a sheet is named after the workbook rather than the active sheet. Ideally, I would be able name the new pdf after an identified cell. See current code below.

Public Sub Save_Range_As_PDF_and_Send_Email_PO()

    Dim PDFrange As Range
    Dim PDFfile As String
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim HTMLBody As String
    
    With ActiveWorkbook
    
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        
        PDFfile = Replace(.FullName, ".xlsx", ".pdf")
    
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached purchase order. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                    
        .Attachments.Add PDFfile
        .send
    End With

    'Delete the temporary PDF file

    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

 

[Micron]

Use this approach instead - .ActiveSheet.Range("C6").Value - in your Replace function (or whatever the cell address is if it's not C6).

 

[cdg786]

Thanks. I did that, and change Dim PDFfile to Range rather than string. Still not working. Won't send email at all now. Help is appreciated. Bit out of my depth at this level.

Public Sub Save_Range_As_PDF_and_Send_Email_PO()

    Dim PDFrange As Range
    Dim PDFfile As Range
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim HTMLBody As String
    
    With ActiveWorkbook
    
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        
        PDFfile = .ActiveSheet.Range("C6").Value
    
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached purchase order. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                    
        .Attachments.Add PDFfile
        .send
    End With

    'Delete the temporary PDF file

    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

 

[Micron]

Dim PDFfile to Range

Why? Now you're trying to send a range object (not what is contained in the cell) as the "thing" to send. Maybe if you wrote PDFfile.value = but that wouldn't make as much sense as declaring PDFfile as a string. Should send email if you fix that. I think that in the testing phase, I'd use .Display instead of .Send until I was happy with the email object.

 

[cdg786]

Thanks. That makes sense. I reverted to the original code as listed below. I took your advice earlier to substitute the .ActiveSheet code in place of Replace but it did not send and brought back an error. Could you show me what line of code you would insert to allow me to specify the PDFname to be C6?

Public Sub Save_Range_As_PDF_and_Send_Email()

    Dim PDFrange As Range
    Dim PDFfile As String
    Dim toEmail As String, emailSubject As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim HTMLBody As String
    
    With ActiveWorkbook
    
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        
        PDFfile = Replace(.FullName, ".xlsx", ".pdf")
    
    End With
    
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached

    Set OutApp = New Outlook.Application

    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached bid request. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                    
        .Attachments.Add PDFfile
        .send
    End With

    'Delete the temporary PDF file

    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

 

[Micron]

Please don't ever just say stuff like "doesn't/didn't work", "didn't send", "failed", "caused an error" etc. (and never just provide the error number) because almost never is the reason so evident in the code that info like that is enough. While the following may not produce the exact email message you want, it does work for me:

Public Sub Save_Range_As_PDF_and_Send_Email_PO()

    Dim PDFrange As Range, PDFfile As String
    Dim toEmail As String, emailSubject As String, HTMLBody As String
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
   
    With ActiveWorkbook
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        PDFfile = .ActiveSheet.Range("C6").Value
    End With
   
    PDFrange.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFfile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False

    'Send email with PDF file attached
    Set OutApp = New Outlook.Application
    Set OutMail = OutApp.CreateItem(olMailItem)
    With OutMail
        .To = toEmail
        .Subject = emailSubject
        .HTMLBody = "<p> Hello, </p>" & _
                    "<p> Please see attached purchase order. We are happy to connect to discuss an missing details. </p>" & _
                    "<p> Thank you, </p>" & _
                    "<p> rdc Procurement. </p>"
                   
        .Attachments.Add PDFfile
        '.Send
        .Display
    End With

    'Delete the temporary PDF file
    Kill PDFfile

    Set OutMail = Nothing
    Set OutApp = Nothing

End Sub

I also enabled the attachment line and I get an email with a pdf attachment showing the spreadsheet data that I have (when I enable send).
EDIT - although this time it's fairly obvious what the issue is.

 

[cdg786]

Comments noted. Thank you for your help. That code does produce an email for me, as well. I still am having issues with the produced name of the attached pdf file. It is still produced as the workbook name, rather than the assigned cell value name, in this case, C6.

 

[Micron]

Then you must have the wrong cell reference, or the wrong data in the referenced cell? As it is, you have the subject line and file name coming from the same cell (A6) which doesn't seem right. If I have TEST in A6 and C:\Users\Micron\Documents\15.pdf in A7 (and change the code accordingly) I get

The recipient data is there; I just removed it for privacy reasons.

 

[cdg786]

Can you should me how you changed the code? Do I need to specify a location for the file as you did in "C:\Users\Micron\Documents\15.pdf" if I am intending to delete the file automatically after its creation?

In the same vein, when I change the PDFFile = code it then tells me I have an issues with .Attachments.Add PDFFile

 

[Micron]

Ignore the content except for email address in B15, file path in C7 and subject in C6 :

	TEST		66%​	58 p
x	QR	QO						#VALUE!​
y	-90​	100​	Billed 100 pcs, Received 10 pcs (90 short)					#REF!​
z	-50​	60​	Billed 60 pcs, Received 10 pcs (50 short)					Billed 1 pcs, Received 0 pcs (1 pcs short)
k	50​	10​	Billed 10 pcs, Received 60 pcs (50 extra)					Billed pcs, Received 0 pcs (0 pcs extra)
x	11​	TEST	#VALUE!​					Billed pcs, Received 0 pcs (0 pcs extra)
a	-22​	C:\Users\Micron\Documents\15.pdf	#REF!​					Billed 5 pcs, Received 10 pcs (5 pcs extra)
	-1​	1​	Billed 1 pcs, Received 0 pcs					Billed pcs, Received 0 pcs (0 pcs extra)
5​	5​	5​	3​	3​		2​	2​
	emailAddressHere.com

The code changes for that would be the last 1 or 2 lines in the With block.

    With ActiveWorkbook
        Set PDFrange = .ActiveSheet.Range("A1:I53")
        toEmail = .ActiveSheet.Range("B15").Value
        emailSubject = .ActiveSheet.Range("C6").Value
        PDFfile = .ActiveSheet.Range("C7").Value
    End With