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

Hazelnut

New Member
Joined
Mar 21, 2018
Messages
10
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
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
The code didn't work. It still sends as an Excel sheet. Not sure what else to do--probably just bag the project. Thanks.
 
Upvote 0
.
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 )
 
Last edited:
Upvote 0
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.
 
Upvote 0
"are you wanting to save the entire workbook as a PDF ? Or maybe the active sheet or a specific sheet as PDF ?"
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top