Auto Save-as & Send Email Notification

I know this can be done - but I don't know how to execute it. I've never used macros / VB so please have your suggestions start at the beginning. (although I do already have the Developer tab enabled on my toolbar)

I have a form that I've created and saved as a template. The form will be loaded on all desktops. I want staff to be able to open the form, complete it and then save as in a particular shared network drive. I don't want them to have to look for the location - I want it to go to that folder automatically. I'd also like the file name to be auto generated so that the format is always the same and never duplicated.

Second, after the form is saved I want an email to be sent out, notifying certain individuals that the form was completed. The persons who receive the email will be dependent upon certain fields in the sheet (ex: Mary & Tom will receive the email if Mary's name was listed as the Manager in cell A1).

The email addresses will be located on another tab in the workbook.

Thank you in advance for your response.

April, The Nerdy Nurse

Also - if the macro to save-as can be a clickable link rather than a keystroke, that would be ideal. And the email notification should actually be a send as PDF to a particular individual.

Hello Nerdy Nurse,

Welcome to the Forum!

You should post a copy of the workbook. It will easier to add the needed code directly to the workbook and post back a working version.

Please provide before and after examples of your data along with any notes you would like to include.

If your workbook contains any sensitive information, please redact that information before you post.

How To Post Your Workbook

1. At the bottom right of the Reply window, Click the button Go Advanced
2. At the top of the Your Message Window, in the first row of icons, Click the Paperclip icon.
3. Click the Add Files button at the top right in the dialog box. This displays the File Manager dialog.
4. Click the Select Files button at the bottom middle of the dialog.
5. In the new window Find the file you want to upload, select it, and Click Open.
6. You will now be back in the File Manager dialog. Click the bottom Middle button Upload File.
7. Wait until the file has completely uploaded before you exit the File Manager dialog.

Hello Nerdy Nurse,

Perhaps my request was not clear. There are several points in your post that need that need clarification.

1) What is the Save As destination path?

I want staff to be able to open the form, complete it and then save as in a particular shared network drive.

2) What does a typical file name look like? Will the name be based on information on the worksheet? If so, which cells are involved?

I'd also like the file name to be auto generated so that the format is always the same and never duplicated.

3) Managers names appear only in cells E19:E22.

The persons who receive the email will be dependent upon certain fields in the sheet (ex: Mary & Tom will receive the email if Mary's name was listed as the Manager in cell A1).

Leith - Thanks for replying. I am a little hesitant posting specific information. But, I have found this code and have everything I need except how to attach the current workbook to the email. Can you help with that part?

Please Login or Register  to view this content.

Hello Nerdy Nurse,

The code you have will attach the Active Workbook to the email.