Forwarding excel files to an email address

Hi,

Put the following macro in your workbook BeforeSave event

Please Login or Register  to view this content.

Hi,

There is no inbuilt Excel functionality that will do what you want without macros so one way or another you will need to use a macro if you want to automate your process. And why have you got such an aversion to macros and VBA? Sometimes - as here, this is the only way to achieve what you want. Don't restrict your knowledge merely because understanding at least something about macros needs a little effort. Most things that are useful in life require that.

But I don't see why there should be a problem anyway

Your invoice master should be held as an Excel Template file (.xltm extension) and not a regualr .xlsx file without any variable data. In the template you should include the macro I gave you. Then every time you want a new invoice you open the template, enter your variable details and save it with whatever filename you want. This will automatically save as a .xlsm file and hence won't overwrite the template file which is available then for the next and all subsequent invoices. That's the beauty of a template file. Unless you actively take steps to save it as a template you can never overwrite it.

OK.

1. Make sure your workbook contains all the information that doesn't change and that will remain consistent each time you raise an invoice.
At this stage build in any validation lists that you need in order to populate any cells that you want to use as drop down 'pick list' items - usually customer names, addresses, your product codes, descriptions, prices etc. Put any validation lists on a sheet which is hidden before you save the template.

2. Now go to the Visual Basic Environment. Either Alt-F11, or from the Ribbon choose 'Developer' then 'Visual Basic'. In the left hand Project pane find your workbook name and double click it. From the left hand drop down box above the right hand pane choose 'Workbook' and in the right hand drop down box choose the 'BeforeSave' option. You'll then see the 'Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)' procedure appear in the right hand pane.

3. Copy and paste the code I gave you previously in this procedure and then close Visual Basic or ALt-F11 agaon back to Excel.

4. Use File Save As, pick the 'Other Formats' and from the 'Type' box pick Excel Macro Enabled Template (*.xltm), enter the name to give to the template and OK to Save and then close the file.

Now to use the template choose 'New' and then look for the template in the 'My Templates' and select and open it. Proceed to create your invoice and when finished click the Save option and the automatic email will be created and sent.

The only caveat is that you need Microsoft Outlook for this macro to work. If you have something else then post back.