Page 3 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 30 of 33

Thread: Email completed PDF

  1. #21


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

    I just emailed myself a pdf with the code. When I received it it showed it as a pdf but tried to open in notepad. Any thoughts? It would not recognize it when I tried to open with Adobe.

  2. #22
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hmmm... no... that's weird...

    What version of PDFCreator are you using? I'll test here as well.
    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. #23
    I'm using v1.2.3. It stores the file in the folder as a pdf that can be opened. When it emails it out is when it is having issue.

  4. #24
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    I'm seeing that here too... that is really weird. If the file is created fine, it should attach fine... I wonder what is going on here...

    I may not be able to get you an answer on this one tonight, Randall. It's almost 11pm my time, and I've got to work in the morning. I'lll try a few tests on my side and get back to you though.
    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. #25
    I understand Ken. Thanks again! This forum is a huge resource.

  6. #26
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Got it.

    So as it turns out, we test if the file shows up in Windows Explorer. What I didn't realize is that the code runs so fast that the file shows up, but the details aren't completely written to it before it gets uploaded to the outlook email. So basically you have a process that looks like this:
    • Routine triggers write
    • File created
    • Writing starts
    • File uploaded to outlook
    • Writing finishes
    This explains why the file on the desktop opened... it was complete. But the outlook one was uploaded too quickly.

    I've inserted a 1 second pause in the code to fix that issue. (It's the Application.Wait line). If you're still getting the error, try changing that to a 2 or 3 second delay instead.

    Let me know if that fixes it up for you.
    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.

  7. #27
    I had to expand the time window to 3 seconds. Is there anyway to keep selected sheets from coming up in the userform as an option to print?

  8. #28
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Of course!

    Change the Userform's Initialize event to this:

    Code:
    Private Sub UserForm_Initialize()
        TextBox1.Value = "C:\Users\" & Environ("Username") & "\Desktop\test pdf"
        Dim ws As Worksheet
        For Each ws In Application.ActiveWorkbook.Sheets
            With ws
                If .Visible = False Then
                    'Sheet not visible, so ignore it
                Else
                    'Choose which sheets to ignore
                    Select Case .Name
                        Case Is = "HideMe", "DontShow"  '<-- list the sheets you want to ignore
                            'Don't want to show the above, so ignore them
                        Case Else
                            Me.lstAvailable.AddItem .Name
                    End Select
                End If
            End With
        Next ws
    End Sub
    You'd need to change the "HideMe", "DontShow" to the names of the worksheets you want to hide.

    I also changed the code so that it defaults to the active user's desktop folder, rather than the hard coded one.

    With regards to the pause, I'd increase it to 4 seconds, just to be safe. If you have something else running, it could slow down the process and trigger the error again, so best add the second for insurance.
    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.

  9. #29
    Ken,
    Thanks again that was exactly what I was needing! One final question, do you have any code that will allow you the option to print from the userform to your default printer. Our guys in the field have to print a paper copy for the customer and they will do the PDF for our own storage. If there is a way to add an additional command button to the user form that they can click that will send the selected sheets to the default printer, but not close out the userform so they can send the same sheets to the pdf/email command. This would make it a one stop shop for what we need.

    Randall

  10. #30
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,346
    Articles
    56
    Blog Entries
    14
    Excel Version
    Excel Office 365 Insider
    Hi Randall,

    Try this. I did make a change to the userform to accomodate this, and moved/renamed the code for the buttons to make it clearer on the back end a bit. So if you're copying this to your own project, I'd recommend deleting your userform, and copying this one into your file.

    Hope it helps,
    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.

Page 3 of 4 FirstFirst 1 2 3 4 LastLast

Posting Permissions

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