Print, Save as PDF, Email PDF code
 

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.

Thanks in advance, I hope someone sees this as possible, I havent had much luck so far.

Thanks, Paul
-accidentally posted this to microsoft.public.mac.office.excel earlier.

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

Print, Save as PDF, Email PDF code
 

Hi again,

Am Sat, 9 Jul 2016 22:53:21 +0200 schrieb Claus Busch:

try:

Sub PrintSheets()

better readable:

Sub PrintSheets()
With Sheets("Quote")
.PrintOut Copies:=1
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"R:\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 "R:\emailed quotes\" & Sheets("Quote").Range("M1") _
& ".pdf"
End With
.send
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Print, Save as PDF, Email PDF code
 

On Saturday, July 9, 2016 at 4:57:07 PM UTC-4, Claus Busch wrote:
Hi again,

Am Sat, 9 Jul 2016 22:53:21 +0200 schrieb Claus Busch:

try:

Sub PrintSheets()

better readable:

Sub PrintSheets()
With Sheets("Quote")
.PrintOut Copies:=1
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"R:\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 "R:\emailed quotes\" & Sheets("Quote").Range("M1") _
& ".pdf"
End With
.send
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

BEAUTIFUL! Thank you Claus!!! - Very happy and grateful! Paul

Print, Save as PDF, Email PDF code
 

On Saturday, July 9, 2016 at 5:58:02 PM UTC-4, Paul Doucette wrote:
On Saturday, July 9, 2016 at 4:57:07 PM UTC-4, Claus Busch wrote:
Hi again,

Am Sat, 9 Jul 2016 22:53:21 +0200 schrieb Claus Busch:

try:

Sub PrintSheets()

better readable:

Sub PrintSheets()
With Sheets("Quote")
.PrintOut Copies:=1
.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"R:\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 "R:\emailed quotes\" & Sheets("Quote").Range("M1") _
& ".pdf"
End With
.send
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

BEAUTIFUL! Thank you Claus!!! - Very happy and grateful! Paul

One more, question on this... If I wanted the:
..to =
..Subject =
..body =
to pull their information from: Sheets("Quote").Range("M2,M3,M4)" and to not actually send but just open, and allow me to review before sending, is that possible?
Thank you, Paul

Print, Save as PDF, Email PDF code
 

Hi Paul,

Am Sat, 9 Jul 2016 21:24:56 -0700 (PDT) schrieb Paul Doucette:

One more, question on this... If I wanted the:
.to =
.Subject =
.body =
to pull their information from: Sheets("Quote").Range("M2,M3,M4)" and to not actually send but just open, and allow me to review before sending, is that possible?

try:

Sub SendMail()
Dim objOutlook As Object, objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.Createitem(0)

With objMail
.to = Sheets("Quote").Range("M2")
.Subject = Sheets("Quote").Range("M3")
.body = Sheets("Quote").Range("M4")
With .attachments
.Add "R:\emailed quotes\" & Sheets("Quote").Range("M1") _
& ".pdf"
End With
.display
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Print, Save as PDF, Email PDF code
 

Hi again,

Am Sun, 10 Jul 2016 10:52:37 +0200 schrieb Claus Busch:

Sub SendMail()

or:

Sub SendMail()
Dim objOutlook As Object, objMail As Object
Dim varText As Variant

varText = Sheets("Quote").Range("M2:M4")

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.Createitem(0)

With objMail
.to = varText(1, 1)
.Subject = varText(2, 1)
.body = varText(3, 1)
With .attachments
.Add "R:\emailed quotes\" & Sheets("Quote").Range("M1") _
& ".pdf"
End With
.display
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Print, Save as PDF, Email PDF code
 

On Sunday, July 10, 2016 at 4:52:45 AM UTC-4, Claus Busch wrote:
Hi Paul,

Am Sat, 9 Jul 2016 21:24:56 -0700 (PDT) schrieb Paul Doucette:

One more, question on this... If I wanted the:
.to =
.Subject =
.body =
to pull their information from: Sheets("Quote").Range("M2,M3,M4)" and to not actually send but just open, and allow me to review before sending, is that possible?

try:

Sub SendMail()
Dim objOutlook As Object, objMail As Object

Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.Createitem(0)

With objMail
.to = Sheets("Quote").Range("M2")
.Subject = Sheets("Quote").Range("M3")
.body = Sheets("Quote").Range("M4")
With .attachments
.Add "R:\emailed quotes\" & Sheets("Quote").Range("M1") _
& ".pdf"
End With
.display
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

Worked Perfectly, Claus! Thank you AGAIN! :-) -Paul