Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109069

Re: Print, Save as PDF, Email PDF code

Show all headers | View raw

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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Print, Save as PDF, Email PDF code Paul Doucette <pauld@mewood.com> - 2016-07-09 13:11 -0700
  Re: Print, Save as PDF, Email PDF code Claus Busch <claus_busch@t-online.de> - 2016-07-09 22:53 +0200
    Re: Print, Save as PDF, Email PDF code Claus Busch <claus_busch@t-online.de> - 2016-07-09 22:57 +0200
      Re: Print, Save as PDF, Email PDF code Paul Doucette <pauld@mewood.com> - 2016-07-09 14:57 -0700
        Re: Print, Save as PDF, Email PDF code Paul Doucette <pauld@mewood.com> - 2016-07-09 21:24 -0700
          Re: Print, Save as PDF, Email PDF code Claus Busch <claus_busch@t-online.de> - 2016-07-10 10:52 +0200
            Re: Print, Save as PDF, Email PDF code Claus Busch <claus_busch@t-online.de> - 2016-07-10 11:04 +0200
            Re: Print, Save as PDF, Email PDF code Paul Doucette <pauld@mewood.com> - 2016-07-10 07:15 -0700

csiph-web