Adjust code to pull excel data in subject line

Hello,

After going through forums I came up with the below code which works perfectly. It saves the worksheet as a PDF then attaches to an email. This is great as is; however icing on the cake would be if data from the worksheet could populate in the subject line.

For example Subject would be "New Order for (B1)"

Please advise

Sub RDB_Worksheet_Or_Worksheets_To_PDF_And_Create_Mail()
Dim FileName As String

If ActiveWindow.SelectedSheets.Count > 1 Then
MsgBox "There is more then one sheet selected," & vbNewLine & _
"be aware that every selected sheet will be published"
End If

FileName = RDB_Create_PDF(ActiveSheet, "", True, False)

If FileName <> "" Then
RDB_Mail_PDF_Outlook FileName, "[email protected]", "New Subject Line", _
"See the attached PDF for order " _
& vbNewLine & vbNewLine & "Warm Regards", False
Else
MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
"Microsoft Add-in is not installed" & vbNewLine & _
"You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
"The path to Save the file in arg 2 is not correct" & vbNewLine & _
"You didn't want to overwrite the existing PDF if it exist"
End If
End Sub

Please use code tags around code for easy reading. It's the hash mark in message editor menu.

In the code that actually sends the email, you should have a

Please Login or Register  to view this content.

Just change that to

Please Login or Register  to view this content.

Change the worksheet and range to suit.