+ Reply to Thread
Results 1 to 12 of 12

print userform to pdf and then attach it to an email

  1. #1
    Registered User
    Join Date
    09-22-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    2

    print userform to pdf and then attach it to an email

    am looking for vba code to print a userform to a pdf and then attach the pdf to an email. I do not need to save the pdf, it can be deleted when the email is sent.


    Thanks,

  2. #2
    Forum Expert
    Join Date
    11-22-2016
    Location
    Cornwall,UK
    MS-Off Ver
    office 365
    Posts
    4,240

    Re: print userform to pdf and then attach it to an email

    Have a look at this other thread
    Last edited by kev_; 09-22-2017 at 02:49 PM.
    Click *Add Reputation to thank those who helped you. Ask if anything is not clear

  3. #3
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,473

    Re: print userform to pdf and then attach it to an email

    The code to print the Userform is
    Please Login or Register  to view this content.
    I have never found a way for it to print as a PDF, possibly setting the PDF print driver as the default printer?

  4. #4
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: print userform to pdf and then attach it to an email

    Hi homerjones_45

    Welcome to the Forum!!!

    If you'll attach a Sample File of what you're working with I'll be happy to look at it with you.

    Please attach a sample workbook the Structure of which is the same as your actual workbook.

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    INCLUDE ANY CODE YOU'RE USING OR HAVE TRIED.

    Remember to desensitize the data.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: print userform to pdf and then attach it to an email

    I would use a method similar to Stephen Bullen's API PastePicture method. In it, we can save the pic as a bmp and/or paste it. If one has Adobe Acrobat, not Reader, one can use a routine to easily convert a bmp to a pdf. Other 3rd party programs can do that too.

    Since the request was for a pdf, I used the paste method.

    e.g.
    In a Module:
    Please Login or Register  to view this content.
    In Userform1:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    09-22-2017
    Location
    Minnesota
    MS-Off Ver
    2016
    Posts
    2

    Re: print userform to pdf and then attach it to an email

    the spreadsheet is attached. I want to open the spreadsheet and click on the blue button which brings up a userform, after the user fills it out and presses the submit button it fills out the spreadsheet. Without clicking any more buttons I want it to print the userfomr to a pdf and attach it to an outlook e-mail. I don't need to save a copy of the pdf.

    Any help is appreciated.

    Thanks!
    Attached Files Attached Files

  7. #7
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: print userform to pdf and then attach it to an email

    Hi homerjones_45

    It's a bit late here in Ohio...I'll look at this tomorrow. I do need to repair my dishwasher in the AM...needs a new detergent dispenser...shouldn't take long.
    Back at you tomorrow.
    Last edited by jaslake; 09-23-2017 at 10:16 PM.

  8. #8
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: print userform to pdf and then attach it to an email

    I would recommend using the Tag property to store column numbers or letters to make code more clear and easier to maintain. There are some misalignments that I did not correct.
    Attached Files Attached Files

  9. #9
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,477

    Re: print userform to pdf and then attach it to an email

    Like Dave mentioned.
    Set your default printer to "Microsoft Print To PDF" and run this, it'll print UF1 to PDF.
    Please Login or Register  to view this content.

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: print userform to pdf and then attach it to an email

    Hi homerjones_45

    The Code in the attached appears to do as you require. Let me know of issues.
    This is the nuts and bolts of the Code.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: print userform to pdf and then attach it to an email

    The 2 API methods lets you makes it more automated without user action. I generally avoid Sendkeys sort of methods but John's API use should be fine. Sendkeys methods can fail when focus or timing is off.

    The UserForm1.PrintForm method requires interaction where you have to name the location and filename. If you always, set the path and file name the same, that can be very fast as that would be statically added to .Attachments.Add. Of course this can lead to the wrong pdf file being attached due to user entry in that dialog.

    You should probably Display your email rather than Send anyway. Of course not knowing your needs, you may want that more automated as well.

    If you want to use the PrintForm method, please ask if you want code to reset your default printer. I don't normally use that print driver myself so I would reset it in code if I used PrintForm.

  12. #12
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: print userform to pdf and then attach it to an email

    As a follow-up to this thread: In case someone is interested, I made jaslake's method into a function.
    Please Login or Register  to view this content.
    The method above and the other API method is included in the attached file. The other method has some issues in the VBA7 conversion attempt. If anyone has time to debug it, please report back if successful. It should be close. Simply close and reopen Excel if it errors to clear the memory overflow error. Since LongPtr return type is expected, there is no point in using the #ELSE parts.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 05-08-2017, 04:17 PM
  2. attach different worksheet and email them tdifferent email address through macro/vba/addin
    By arunverma004 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-03-2014, 08:20 AM
  3. Replies: 0
    Last Post: 09-12-2014, 04:12 PM
  4. [SOLVED] attach contents of textbox in userform as body of email vba
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-20-2014, 07:27 AM
  5. [SOLVED] Email Macro to attach a non active worksheet to outlook email
    By mickgibbons1 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-21-2013, 08:38 PM
  6. Help - Attach an image from the userform into the body of an email in lotus notes
    By dragontm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-12-2013, 05:35 AM
  7. SaveAs PDF and attach to email macro won't attach?!
    By Rerock in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-25-2012, 05:28 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1