Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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.
  #2   Report Post  
Posted to microsoft.public.excel.programming
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.


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
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 40
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Send email from excel to notes - save email in sent folder Boss Excel Programming 0 March 15th 10 02:52 PM
Code to automatically email when xls file is save? sherifffruitfly Excel Programming 3 January 20th 09 10:53 AM
Need a code to print and save file as Ron de Bruin Excel Programming 4 January 9th 07 09:42 PM
Totally Disabling (^ save ) (Save as) and Save Icon Which code do I use: harpscardiff[_10_] Excel Programming 8 November 10th 05 01:24 PM
Save, Save As, Print Grayed Out when instance created via VBA/VB6 Jake Marx[_2_] Excel Programming 0 September 22nd 03 10:16 PM


All times are GMT +1. The time now is 12:52 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"