Print, Save as PDF, Email PDF code
Hi Paul,
Am Sat, 9 Jul 2016 13:11:12 -0700 (PDT) schrieb Paul Doucette:
I am looking to assign vba code to a command button which will do the following:
1) Print worksheet ?Quote? and worksheet ?Sheet1? to my default printer.
2) Save worksheet ?Quote? as a PDF to the folder ?R:\emailed quotes\?, giving it a file name taken from cell: Sheets("Quote").Range("M1").
If possible, I would also like to then attach that pdf to a new email. I am using Outlook and Excel 2010.
try:
Sub PrintSheets()
With Sheets("Quote")
.PrintOut Copies:=1
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\emailed quotes\" & .Range("M1") & ".pdf",
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False,
OpenAfterPublish:= _
False
End With
Sheets("Sheet1").PrintOut Copies:=1
SendMail
End Sub
Sub SendMail()
Dim objOutlook As Object, objMail As Object
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.Createitem(0)
With objMail
.to = "claus_busch(at)t-online.de"
.Subject = "Expected PDF file"
.body = "Hi Claus," & Chr(10) & Chr(10) _
& "Here is the mail with the expected PDF"
With .attachments
.Add "C:\emailed quotes\" & Sheets("Quote").Range("M1") & ".pdf"
End With
.display
' .send
End With
End Sub
Assign "PrintSheets" to the button. "SendMail" is called in that macro.
You have to change the address, the subject and the body.
Regards
Claus B.
--
Windows10
Office 2016
|