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.

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

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

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

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

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

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

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