I have a command button in my Excel spreadsheet where it will look at the
email address typed into a specific field and email the excel file to that
email address.
What I want to do is when I push the button, First have the page print to a
pdf, then send only the pdf file to the email address specified.

How do I add in the step of printing to a pdf.

Thanks in advance,
Travis

Hi Travis

Code on my page for excel 2007 and a link to Ken's site for older versions
http://www.rondebruin.nl/pdf.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Tdahlman" wrote in message ...
I have a command button in my Excel spreadsheet where it will look at the
email address typed into a specific field and email the excel file to that
email address.
What I want to do is when I push the button, First have the page print to a
pdf, then send only the pdf file to the email address specified.

How do I add in the step of printing to a pdf.

Thanks in advance,
Travis

Hi Travis,

To add the step of printing to a PDF before sending the email, you can use the "ExportAsFixedFormat" method in VBA. Here are the steps to do this:

1. Open the VBA editor by pressing Alt + F11.
2. Double-click on the button that you have created to open the code window.
3. Add the following code to print the active sheet to a PDF file:
   
   
   Formula:

   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\YourUserName\Documents\FileName.pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False 

   Replace "YourUserName" with your actual user name and "FileName" with the name you want to give to the PDF file.
   
4. Add the following code to send the PDF file as an attachment to the email:
   
   
   Formula:

   Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

strbody = "Please find attached the PDF file."

On Error Resume Next
With OutMail
    .To = Range("A1").Value 'Replace A1 with the cell that contains the email address
    .Subject = "PDF file"
    .Body = strbody
    .Attachments.Add ("C:\Users\YourUserName\Documents\FileName.pdf") 'Replace with the actual path and file name of the PDF file
    .Send
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing 

   Replace "A1" with the cell that contains the email address, and replace the path and file name of the PDF file with the actual path and file name.
   
5. Save the code and close the VBA editor.

Now, when you click the button, it will print the active sheet to a PDF file and then send the PDF file as an attachment to the email address specified in the cell you have specified.