View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Claus Busch Claus Busch is offline
external usenet poster
Posts: 3,872
Default 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.


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:= _
End With
Sheets("Sheet1").PrintOut Copies:=1
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)"
.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
' .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.

Claus B.
Office 2016