VBA to create and sort new Table, print to PDF, email, then reset file.

  • Hello!

    I need help with finishing my excel file. The idea is (1) one of my employees goes into the file and (2) selects which items need to be restocked then (3) presses a button which (3a) copies the selected items to the TempTbl worksheet and (3b) automatically index(match) each item to the Fulfillment columns from InventoryTbl then (3c) alphabetically sorts this new table first by fulfillment then by item so that it may finally (4) print to PDF, email me the PDF, then wipe TempTbl clear. Once the email is sent, the form resets all dropdowns on the form to blank, saves the file, and notifies the user the email was sent with a thank you message, once they click "Ok" it closes Excel.

    *it will send the file from a microsoft outlook email account that's already logged into the Windows user.

    *i will ideally password protect and/or hide the Admin worksheet so that it is only accessible to an admin, the end user in this case should only interact with the form.

    *the file is hosted in a OneDrive folder and shared with local users on the PC. I imagine it might be logical to store the saved PDFs to a subfolder in the same shared folder as the file, lets call that Subfolder "PO Archive" for working purposes

    *I really only want the end user to be able to (a) manipulate the dropdowns, (b) add text to the text box under notes and and (c) submit the form. Ideally they won't see any of the formulas or behind-the-scenes machinations of how the file works (less room for them to mess it up) ... i know there is a way to hide formulas and lock cells, I can do this myself but just stating that I will probably continue to massage the file

    *I'm clearly not a professional at this, so if you see any opportunities to streamline this, please go for it!

    The file is attached.

    Thanks in advance for all your help!

  • LOL, I mentioned I would continue to massage the file, I realized I didn't discuss how I'd like the output to appear. The updated attached file has TempTbl filled in, this is more or less how I'd like to receive the PDF.

    Some additional thoughts:

    The printArea will need to automatically expand. On a day to day basis I'm restocking no more than 5 or 6 items, but should it ever be longer than that the printArea shouldn't leave anything out.

    Everything above Row 28 is basically copied over from the Purchase Order Form Worksheet.

    I'd like the saved PDF file name to be the purchase order number, extracted from cell J11 of either the TempTbl worksheet or the Purchase Order Form worksheet.

    As mentioned in the original specs, once the PDF has been emailed off it resets TempTbl to be blank, and also resets Purchase Order Form dropdown to blank and wipes the textbox as well.

  • Finished it myself, please find the necessary code below:

    This code helps in creating PO numbers that are specific to the user's initials:

    I saved the following as a single module since its all the "worker bees":

    And calling it all with a single button:

    Sub CompletedPO() 
    Call PrintAndSave 
    Call ResetDropDowns 
    Call SaveAndClose 
    End Sub 

    And you can see it all run together in the attached file. I hope someone else finds this helpful. Thank you to the community of coders all over the world who left breadcumbs for me to follow ♡♡♡♡♡♡♡♡♡♡

Participate now!

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