automatic saving with file name equal to cell reference

[gassiusmax]

Hi All,

I am trying to setup a way by macro to export one of my excel sheets into pdf (this bit I can do), what I am trying to do is automatically set the file name as the contents of one particular cell for example if cell A1 contains 'ABCD' I need the file exported pdf to be named abcd.pdf

and then automatically send this pdf file by email

Thanks for any input

Danny

[Greg M]

Hi there,

Try using the following code:

Please Login or Register  to view this content.

You can change the values of the sCELL_ADDRESS and bOPEN_AFTER constants to suit your own requirements.

I assume that you will have already selected the Current Directory to be the folder into which you want the PDF file to be saved, otherwise you will probably need to specify it somewhere in the above code.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[gassiusmax]

Hi Greg,

Thanks for that, I could not seem to get the code functioning but I think that was me. I managed to get this code working below

Private Sub CommandButton1_Click()
'This macro opens the SaveAs option with the defult file path "you have to set this file path below" coverts the whole sheet into .pdf file format
'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False

pdfName = ActiveSheet.Range("D4")
ChDir "C:\DROPBOX\DATA PLAN CONFIRMATIONS" 'This is where youo set a defult file path.
fileSaveName = ActiveSheet.Range("D4")

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fileSaveName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

I can't seem to delete the PDF after it always says the file is in use when all programs are closed

Is there a way to automatically email the pdf once it is created to email address that a stored in a range in the spreadsheet

Cheers Danny

[Greg M]

Hi again Danny,

Take a look at the attached workbook and see if it does what you need.

Please Login or Register  to view this content.

For convenience, all the significant data values have been defined as module-level constants.

A pre-check is included to ensure the existence of the folder in which the PDF file is to be created.

The code includes email .Subject and .Body values which you can change to suit your requirements.

The temporary PDF file is automatically deleted as soon as the email has been created.

Hope this helps - please let me know how you get on.

Regards,

Greg M

[Jim885]

Cross-posted to: http://www.mrexcel.com/forum/excel-q...end-email.html

[gassiusmax]

Hi Greg,

your sample was perfect but I can not seem to copy that into my sheet, I have attached the sheet for you as it seems to fall at the start but essentially this should work on two sheets,

Weekly Confirmation
Name of PDF is hidden in D4,
Email Addresses are in 'Financials'!F5:F10

Amazon Invoice
Name of PDF is in C19
Email Addresses are in 'Financials'!K5:K10

On both sheets their is a coded button that allows the save as PDF function to work and for all purposes it is good except for it seeming to lock the PDF so that I can not delete it if I tried, I get error message saying 'this file is in use by another application' when it is not

What Am I doing wrong to transfer the code? How do I make deleting possible? And how can I make sending the email automatic without the popup to manually click send?

really sorry for all this but I am falling over quick on this

Thanks

Danny

[Greg M]

Hi again,

I've attached a modified version of the latest workbook you posted.

To run my code within your workbook you need to change the CommandButton_Click routines in the worksheets involved, i.e.

In the "Weekly Confirmation" worksheet Code Module you need:

Please Login or Register  to view this content.

and in the "Amazon Invoice" worksheet Code Module you need:

Please Login or Register  to view this content.

You didn't mention anything about the "Driver Invoice" worksheet so I have not made any changes there.

I've included some dummy email addresses for testing.

On my system the temporary PDF files are deleted without any problems - I'm not really sure why you are receiving the error messages you mention.

See the following code for automatically sending the email as soon as it has been created

Please Login or Register  to view this content.

Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[gassiusmax]

HI Greg, Getting their slowly with it, the code to create the pdf is the one that stopped me deleting after

Please Login or Register  to view this content.

This is what I originally used to create the pdf, it is this one that when excel and all pdf documents are closed my PC will not allow me to delete or rename the file because of the 'it is still open in another application' warning.


In your code though with the email address how am I able to change the symbol that seperates the email addresses from , to ; as outlook is not liking it.

THank you so much for your help

Danny

[Greg M]

Hi again Danny,

Sorry about the mistake in my code - I don't use Outlook all that often, so I forgot which character Outlook uses to separate email addresses

The following code should do what you want:

Please Login or Register  to view this content.

As always, please let me know if this works for you.

Regards,

Greg M

[gassiusmax]

Hi Greg just wanted so say a massive thank you, with your help I have learnt a lot and been able to get the spreadsheet working to requirements (until the next brainwave), one of my biggest things was not understnading the code properly because I was only looking at the code assigned to the actual button I did not know that CTRL+R reveals all the codes once I found that it was much easier.

Thanks Again

[Greg M]

Hi again Danny,

Many thanks for your feedback - I'm delighted that I was able to help.

As you've found out, you can learn an awful lot by battling your way through VBA code problems!

My version of "until the next brainwave" has always been "until the Bright Ideas department gets to hear of it!"

Best regards,

Greg M