Create folder, save as pdf, and email file

HI Guys,

Would you please be so kind to assist with my macro. What i would actully like to do with the code is to save different sheets to pdf file. For example sheet name "13101" & "13102", the name of the file should be taken from specific cell in another worksheet for example sheet:"test" Cell:"A1:A2". But the trick is also that I would like the macro to create the folder automatically with cell contents in sheet ("test") cell("B1:B2"). And then I would like the macro to email the pdf file to en employee. This will be done on an monthly basis as an monthly asset report.

So to sum up i would like to do the following:
1) Create folder based on an specific cell content in another sheet in the workbook
2) Save a selection of worksheets as pdf in the folder created in step 1, the name of the file will be derived from the same sheet as step 1 name is derived
3) Email the files created in step two to an employee.

It would be greatly appreciated if you could assist as I'm an rookie when it comes to VBA. I have tried to copy macros from the forum and try them but I am struggling with even getting them to work without combining them.

Thanks in advance!

Hey Oliver,

Thanks for the reply!!! I'm going to try to test it now. As I mentioned I'm a total rookie, so lets hope for the best!!

Yet again thanks for your help it is much apreciated!!

Len

Hi,

I think this covers the first two points, not sure about emailing though...

Please Login or Register  to view this content.

Is 'Monthly Report - Capital Spares Issued' then name of the folder you are creating? If so, make sure it doesn't already exist as this would cause the path file access error.

Also check the file path is exactly right as well. You might have already done it but best thing to do is copy the path straight out of the address bar.

I have Changed the directory as the file allready existed, I therefore added another extention to the file path, but the error still occurs. Could this be that because the file is on a network drive this could cause the error?:

New strDir is as follow:
strDir = "Z:\4000 Product & Project Controlling\4900 CapEx & Customer Accounts\4960 CapEx Process\4965 Capital spares\Monthly Report - Capital Spares Issued\New Report"

".....\New Report"

new report is the folder/directory i want to add.

Thanks for the help

The fact it is a network drive shouldn't matter per se, I don't know if user access/editing rights might effect this though. Maybe try it on another drive with a simpler path name?

Apart from the folder already existing or the path being incorrect I don't know what else may cause this error.

What error does it come up with?

Probably a silly point, but I'm assuming you have adobe acrobat/reader installed as well?

Run time error 75, Path/File access error

Jip Adobe is installed, sorry for all the multiple posts but I'm a total rookie at this.

Thanks!

Sorry that was the wrong error: Error is:

Runtime Error'5' Invalid procedure call or argument

@ anteagles20 & Oliver Vistisen

I have figured out the macro, thanks for both of you guys help.

Excellent, glad it worked out Anteagles certainly did the legwork!

Hi Oliver,

Would you please be so kind to assist me with the email function. How do you run the code? and then also can I write multiple of the email functions as I have about 15 report saved to pdf and they have to go to 15 different people.

Thanks in advance for your assistance.

The Outlook code I provided you with needs to be placed within your exsisting subroutine (sub) after the generation of the pdf files. The subroutine in turn has to be placed within ThisWorkbook section, not within one of the sheets or a module, for the outlook code to work.

Also my appologies, I did not include the declration of the outlook object variables in my code earlier. You need to do so by placing these declarations before the code:

Please Login or Register  to view this content.

Note: Outlook will ask you to accept excel sending Emails on your behalf. Ensure you do so!

There is more on this here.



If you're looping through a range with an Email in each cell, this is how I would go about it:

Please Login or Register  to view this content.

Try that and if it doesn't work throw up your code here and I'll have a peek.

Oliver

Sorry for the late reploy, been away recently!

Here is how I would apply this:

Please Login or Register  to view this content.

You can see I've placed all the sheet names in an array arSheets and looped through that array. Each loop will generate a file name, generate a pdf and Email it to a recipient.

You can make a named range of all the sheets you want to apply this code to and change the loop to go through that rather than an array. Will be more dynamic than manually designating the sheets within the vba code.


p.s. Dont forget to tag your code!

Hi Oliver,

Thanks for the Code, I have now tried to run the code as provided by you i get the following error on the following section:

Please Login or Register  to view this content.

Runtime Error 9

Subscript out of range.

Sorry for not using the code tags, will most certainly be using this in the future.

@arlu1201 Thanks for adding the code tags!

Thanks for all your guys help!

Does that Sheet 16201 exsist within the workbook? If not, that would explain the error.

Hi Oliver,

Yes the sheets do exsist in the workbook, I hav tried renaming them and tested them again but with no success.

Could it maybe be ".name" at the end?

Thanks!

work around to simplify it further would be:

Please Login or Register  to view this content.

This should remove the issue all together. given the array a fixed size and just using strings rather than workbook names.

Ok that works to get past that error, thanks. Now I recieve error on following

Please Login or Register  to view this content.

I have replaced "arSheet(x)).export with "arSheet(5))." then the macro works, but the problem is it then only saves the one one sheet over and over but under different names. The email function also works but with the problem as indicated that it saves same sheet but with different name and emails.

Hope this makes sense.

Sorry for all the back and forth correspondence, but your help is really appreciated!!!

Replacing arSheet(x) with arSheet(5) will get you exactly the outcome you are experiencing. I take it it is worksheet 16231 that keeps being Emailed? With each loop you are declaring only 5th item in the arraym, getting you the same result each time. The idea of the loop is to start with item 1, then go through each one until the last item, hence the x.

Undo your change and list the error you are getting

Hello Oliver,

I have tried it without adding the (5) and only with the (x) at the start and recieved the same error as before: Subscript out of range.

As you can see I'm a complete rookie thanks for all your help. This would help me alot in my other reports as well!!!
Cheers

Hi Af,

I ran just the bare-bones loop mechanics (code below) and it works without getting error 9.

Please Login or Register  to view this content.

Try the full code as follows
(Only changes are to x, as I made a mistake originally and started the array from 1, not 0 as I was supposed to!)

Please Login or Register  to view this content.

Hi Oliver,

Thanks!! I do not get any errors, the file gets created but it does not want to send through Outlook. I have checked sent items but there is no trace of the email.

Is there a piece of code that will create the mail but not send automatically, then i can test it like that? Or do you maybe know of any other reason for it not sending.

Thanks!

You will get a Message box that pops up each time you try to send an Email in Outlook. You have to select [Accept] for it to send, otherwise Outlook will not allow it.

You can generate the Emails without sending by commenting out the .Send segment of code, as so:

Please Login or Register  to view this content.

Oliver,

Thanks for all your help!!! This is working perfectly and exactly as I wanted it to.

Have a great day!!

Excellent, glad it's working!