Results 1 to 8 of 8

Thread: Emailing an Excel Spreadsheet Print Range as a .pdf

  1. #1

    Emailing an Excel Spreadsheet Print Range as a .pdf



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I have a spreadsheet with 9 defined page breaks that I print and distribute to 9 different people. I would love to be able to email each person their respective page as a .pdf. For example page 1 would always be emailed to johndoe and page 2 would always be emailed to janedoe etc. Any ideas??? Thanks in advance!
    Tim

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Sure, I have ideas.

    First thing I would do is set up a table on a new worksheet that listed:
    • Page number
    • Name
    • Email address


    I'd use that table to loop through and send the emails. Some things I'd need to know from you to help you with your code though...
    • What version of Excel are you using? 2003, 2007, 2010?
    • If you're not using 2007 or 2010, can you install a PDF writer on your machine?
    • What email client are you using?
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Sorry! I should have included more details in my original question! I am a newbie!
    I am using Excel 2007 and I am using Outlook as my email client.

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Okay, so here's how I would approach this:

    Step 1: Set up the table of email addresses
    • Set up a list with the following headers: Page Number, Name, Email Address
    • Fill the list in with the appropriate data (DO NOT leave a blank row between your headers and your data!)
    • Click in that list, go to Home-->Format As Table (It should find the entire table and have the "My table has headers" box checked already)
    • Click OK
    • Go to TableTools --> Design
    • On the far left, change the Table Name to: tblEmails


    Step 2: Download an install my Class module for email:


    • You can find it here
    • Follow the instructions there to install it (but don't worry about writing any code just yet.)


    Step 3: Coding the mail routine
    • Insert a new Module into your project and add the following code:

    Code:
    Private Sub EmailViaOutlook(sTo As String, sAttach As String)
    'Create the email object
        Dim oEmail As New clsOutlookEmail
        With oEmail
            'Add a recipient
            .AddToRecipient = sto
     
            'Set the subject
            .Subject = "The file you requested"
    
            'Set the body
            .Body = "Here is the file you requested."
     
            'Add an attachment
            .AttachFile = sAttach
    
            'Preview the email (or use .Send to send it)
            .Preview
        End With
        'Release the email object
        Set oEmail = Nothing
    End Sub

    Step 4: Code the routine to generate and mail the PDF

    • In the module you used above, insert the following code:

    Code:
    Public Sub GenerateEmails()
        Dim cl As Range
        Dim sTempPath As String
        Dim sFileName As String
        Dim sReportSheet As String
        Dim sEmailSheet As String
        
        'Define the names of your worksheets here
        sEmailSheet = "Emails"  '<-- The worksheet that holds your email table
        sReportSheet = "Reports"  '<-- The workshee that holds your reports
        sTempPath = "C:\Users\" & Environ("Username") & "\Desktop\"
        
        For Each cl In Worksheets(sEmailSheet).Range("tblEmails[Page Number]")
            'Record the output name for the file
            sFileName = cl.Offset(0, 1)
            
            'Remove any existing instance of the file
            On Error Resume Next
            Kill sTempPath & sFileName & ".pdf"
            On Error GoTo 0
            
            'Export a temporary copy of the file
            Worksheets(sReportSheet).ExportAsFixedFormat _
                Type:=xlTypePDF, _
                Filename:=sTempPath & sFileName & ".pdf", _
                From:=cl.Value, To:=cl.Value
        
            'Email the file
            Call EmailViaOutlook(cl.Offset(0, 2), sTempPath & sFileName & ".pdf")
        
            'Remove the temp file
            On Error Resume Next
            Kill sTempPath & sFileName & ".pdf"
            On Error GoTo 0
        Next cl
    End Sub
    Now, you'll need to make two changes to make this work for you:

    1. Update the sEmailSheet = "Emails" line, replacing EMails with the name of the worksheet that holds your email table (leave the quotes in place though)
    2. Update the sReportSheet = "Reports" line, replacing Reports with the name of the worksheet that holds the reports you want to email (again, leave the quotes in place)


    Step 5: Test It
    • Close the VBE
    • Save the Workbook
    • Open Outlook (It will work if Outlook is closed, it just takes longer)
    • Go back to Excel and Press Alt+F8
    • Run "GenerateEmails"


    At this point it may take a minute, but you should see Outlook draft messages come up for you, addressed to the correct person, with the correct attachment.

    You have the option of tossing them, or clicking send on each one. If you would rather send each automatically, just change the .Preview in the EmailViaOutlook routine to .Send and you should be good to go.

    I've attached a sample workbook where everything is hooked up for you to look at.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    ok, I think I have done everything in the list above but when I tested it, I received the following error message:

    Run-time error '1004':
    Document not saved. The document may be open, or an error may have been encountered when saving.


    When I choose "DEBUG", it goes to the
    'Export a temporary copy of the file
    Worksheets(sReportSheet).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=sTempPath & sFileName & ".pdf", _
    From:=cl.Value, To:=cl.Value
    What have I done wrong??

    I cant tell you how much I appreciate you doing all this. I love this forum!
    Tim

  6. #6
    Update! I got it to work!! I changed the sTempPath to reflect my computer's setup and it works perfect!!! You are absolutely a genius! Thank you soooooo much!! If you are ever in TN, I will buy you a coca-cola!

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,294
    Articles
    57
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Cool deal, glad you got it working!
    Ken Puls, FCPA, FCMA, MS MVP

    Learn to Master Your Data at the Power Query Academy (the world's most comprehensive online Power Query training), with my book M is for Data Monkey, or our new Power Query Recipe cards!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    I have created something similar to above. I have a spreadsheet with 90 email addresses. Using a for loop the macro iteratively updates a pivot table, sets the print area and then uses exportasfixedformat to export to PDF.

    I have attempted to run this code 35 times today. It successfully ran through the list only twice. The other 33 attempts, it failed producing the runtime error 1004. Document not saved.

    The really curious thing (for me) is that the error appears to occur randomly. On 33/35 runs, the macro successfully passed row 2. On 2/35 runs, it failed on row 2. 3 times failed on row 4, once failed on row 5 and so on.

    I cannot determine why it is failing. Can you help?

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •