Emailing an Excel Spreadsheet Print Range as a .pdf

[kogersdad]

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

[Ken Puls]

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?

[kogersdad]

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.

[Ken Puls]

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:

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:

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.

[kogersdad]

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

[kogersdad]

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!

[Ken Puls]

Cool deal, glad you got it working!

[Beckie]

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?