Protect each worksheet before emailing out

Hi All,

I am looking for some help here. I have a worksheet of 30 tabs for each driver to show their weeks work, I want to email each driver every week their respective sheet.
I found some code online as follows which works very well and emails the individual sheets to each driver, but I really want to protect each work sheet that the driver receives so they cannot be changed, how would I do this. The range on each sheet would be A1:K42.

Thanks

Please Login or Register  to view this content.

Rather than save the sheet to a new workbook and then as a temporary .xlsx file, save it as a .pdf file. Then all they can do is look at it, no changes possible. Also more portable.

TMS,

Thanks for the reply, this is a pretty great idea, what do I need to change in my code above to make this work and save as a PDF file?
Do you have code example that I could use?
Thanks

On holiday at the moment with no computer in sight

But just record a macro while you save the active sheet as a .pdf file. Then change the file name structure in the same way you have for the .xlsx file

TMS,

Thanks for the reply, I have had sometime today to look at this further. After recording a macro and saving as a .pdf file, this is what the code is, but unsure where to place this code.

Please Login or Register  to view this content.

Ok, that code creates a .pdf file with the temporary file name (so you can still delete it later). And that works for the sheet you are processing.

You don't, therefore, need to copy the sheet to a new workbook, save the newly created active workbook, or even define a workbook object so you can keep track of it.

So replace the workbook creating and saving code with your .pdf exporting code.

Thanks TMS,

I don't understand what you mean sorry, I placed that code where it talks about .xlsx but I get and 'Object required" runtime error 424.

Please Login or Register  to view this content.

What object is this looking for or should I place this somewhere else?

Untested, but fill in the .pdf bit and try this:

Please Login or Register  to view this content.

Thank TMS,

I am stuck, here is what I have but getting runtime error 91..., what am I doing wrong?

Please Login or Register  to view this content.

TMS,

I have modified my export part of the PDF with this,, I now receive the email but no attachment and I get an error saying file not found at the Kill TempFilePath & TempFileName & FileExtStr line.

Please Login or Register  to view this content.

As I said, I can't test anything. Why did you add wb.TempFilePath? That wasn't in the code you recorded.

Perhaps this will help: http://www.contextures.com/excelvbapdf.html

I added wb.TempFilePath because nothing else would work, I kept getting errors.
I will review the link you sent.

Thanks!

Thanks TMS,

Ok, I have made progress today, I am now receiving the email with the PDF file attached, but I am now receiving an error message when I try to open the PDF file, see attachment.

Also, here is the vba code:

Please Login or Register  to view this content.

Looks to me as though you are still saving and attaching a workbook object, the difference being that you are making the extension .pdf. Hence why Acrobat can't open it.

As I have pointed out in a few posts back, I was getting file not found, so I went back to the original code.
Since TMS is on holiday and no access to a computer, can someone else please offer some assistance so I can finish off my project?

Thanks

Three basic things to do:

1. Build a full file path for the saved file including the temp directory location, the file name based on the sheet name and date, and the extension, which needs to be .pdf
2. Export the current/active sheet using the file/path name from step 1
3. Create your email and attach the file using the file/path name from step 1

Then 4. Delete the temporary file exported in step 2 using the file/path name from step 1.

You have code or examples for each step, so it's just a case of linking them together.

Because you are exporting a .pdf file, you can get rid of the checks for the Excel version. You don't need to copy the sheet to a new workbook and save it. Forget all that. Just focus on the three steps listed above.

You don't need to Dim and Set/Create a wb object. You are replacing that by exporting the sheet as a .pdf file.

Not sure how I can say it differently

In case the instructions above don't help, I've asked for others to take a look.

Ron has a whole section on emailing out PDF files which can be found here

http://www.rondebruin.nl/win/s5/pdf.htm

the one that matches most with what you want is
Mail_Every_Worksheet_With_Address_In_A1_PDF

Replace A1 with A37

Please Login or Register  to view this content.

and the tempfilename

Please Login or Register  to view this content.

it should work

see attached file created based on Ron's code
can't post full code due to HTML in it

Thanks TMS!

I appreciate your patience and assistance with me, I am still learning this language, some I am beginning to understand, the correct syntax is what is rough for me, I have a long way to go.
Your explanation is very clear and step by step.

Thanks

G'Day Humdingaling,

Thanks for your code example, this worked nicely and has giving me the results I am looking for and has helped me get my original code working along with TMS's detailed explanation.

Thanks again, bonza mate!!!!

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.