Use Submit button to convert Worksheet(s) to PDF and email via Outlook

  • Hello All,

    I have a Macro that is largely doing what I need, but I would like to make the following changes to it.

    1. I would like the PDF to be saved in the same location as the Excel file it was created from. Currently it is asking me to select a location.
    2. I want to have it create a PDF of either the active worksheet or both the active and second worksheet based on the value in one specific cell. Currently it is only converting the active worksheet with no reference to any cell.
      • If cell I16 equals either "New Install" or "Install" then the active sheet is converted along with worksheet 2 which is titled "Install Travel Lot" and emailed. The current macro names the PDF as the active worksheet which will always be worksheet 1 as that's where I have placed my Submit Button. I would like this to remain in place regardless of whether it's converting worksheet 2 or not.
      • If cell I16 equals anything else, including remaining blank then only the active worksheet is converted and emailed. I16 currently contains a Data Validation List just in case that makes any difference
    3. The PDF is being named after the active worksheet. I would like the PDF to be named after the file name instead

    Here is the current macro I have. It is fully functional with the exception of my wish list above. Thank you in advance for any help I may receive on this!

    Edited 3 times, last by Excel Noob: I resolved one of my requests of including a To: address in the email. Discovered the PDF is being named after the active worksheet and not the file name. Want the file name. ().

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!