VBA Code to automatically create pdf file based on cell input and then automatically email

[Hazelnut]

Here's my code so far, but I do not know where to enter the pdf portion or even how to begin to add the pdf portion. When I send the email now it strips the data out of the Excel spreadsheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xRgPre As Range
On Error Resume Next
If Target.Cells.Count > 1 Then Exit Sub
Set xRg = Range("P20")
Set xRgPre = xRg.Precedents
If xRg.Value > 2999.99 Then
If Target.Address = xRg.Address Then
Call Mail_small_Text_Outlook
ElseIf (Not xRgPre Is Nothing) And (Intersect(Target, xRgPre).Address = Target.Address) Then
Call Mail_small_Text_Outlook
End If
End If
End Sub
Sub Mail_small_Text_Outlook()
Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Set xOutApp = CreateObject("Outlook.Application")
Set xOutMail = xOutApp.CreateItem(0)
xMailBody = "Attached is the order. Have a wonderful day!" & vbNewLine & vbNewLine & _
"This is line 1" & vbNewLine & _
"This is line 2"
On Error Resume Next
With xOutMail
.To = "hazelnut@garden.com"
.CC = ""
.BCC = ""
.Subject = "Form Submission"
.Body = xMailBody
.Send
End With
On Error GoTo 0
Set xOutMail = Nothing
Set xOutApp = Nothing
End Sub

 

[vmjan02]

Before sending the email you need to save the excel file in PDF format. Below code will help to do so

Sub PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\........\TestPDF.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
End Sub

Call PDF code before email send.

 

[Hazelnut]

Does it matter where in the code I place the code you provided? Before "Call Email" line?

 

[Hazelnut]

Does it matter where in the code I place the code you provided? Before "Call Email" line?

 

[Hazelnut]

The code didn't work. It still sends as an Excel sheet. Not sure what else to do--probably just bag the project. Thanks.

 

[Logit]

.
You have indicated :

create pdf file based on cell input

Is the cell input P20 ? If so ... What is in P20 ?

If not P20 ... which cell ?


Or are you wanting to save the entire workbook as a PDF ? Or maybe the active sheet or a specific sheet as PDF ?

(If you are not familiar, this is the "go to site" for email and VBA : https://www.rondebruin.nl/win/s1/outlook/mail.htm )

 

[Hazelnut]

Thank you for responding. Cell P20 is the result of several cells and if the amount is over 2,999.99, then I want it to save as a pdf and then automatically email the pdf to a designated recipient.

 

[Logit]

"are you wanting to save the entire workbook as a PDF ? Or maybe the active sheet or a specific sheet as PDF ?"

 

[Logit]

You are welcome.

 

[Hazelnut]

The active worksheet.