Macro mailto with all currently visible sheets as each a separate pdf attachement in mail

Hello,

With the help of members on this forum I got the code below that Works fine the way it is, but in the Workbook a user has the ability to add ducplicates of a specific sheet by a button and if a user has done this then offcourse these "Variable" sheets will not be included in the code herunder and added as seperate pdf attachements.

Thus my thought was now to convert the code below to print the ThisWorkbook instead. I found out that when using Thisworkbook.PrintOut it automatically ONLY prints out the visible sheets at the moment. This way I can manipulate what to be printet by hiding sheets in the beginning of the macro and then make them visible again in the end of the macro after the PrintOut is done.

Thought I would copy that same idea into the code below, which is now based on attaching 2 specific Worksheets.

So my question is:

How to convert this code to add the ThisWorkbook instead of just 2 specific sheets?

In other Words, How do I add ALL currently VISIBLE sheets as pdf attachements (each visible sheet as a seperate pdf Attached in the mail)?


Thanks in advance


P.S. I tried to change the

Please Login or Register  to view this content.

and deleting the one for Sheet2 With

Please Login or Register  to view this content.

but that did not work. If I do that then NO attachements are added at all.




This is the code:

Please Login or Register  to view this content.

Hi,
try this

Please Login or Register  to view this content.

Thank you 6StringJazzer

try this

Please Login or Register  to view this content.

Thanks john55

I also struggle with another problem now. The users have to wait for about 4-5 minutes before the "For Each ws In Sheets....." to start.

After Macro button is pressed the empty email instantly shows but then nothing happens until about 4 minutes has passed. Then all of the sudden a msgbox pops up showing a progressbar for the creation of each sheet beeing published as pdf.

It takes WAAAy too long. The funny thing is that after its completed, if I then close the email and then run the code again it runs in less than 10 seconds. If I close the workbook and open again the first time i runs this code it always takes 4-5 minutes.

IN other words, the first time the macro is run after workbook is opened, it runs in SLOOOOOW mode. Second time and all times after that it runs normally.

Any suggestions on how to speed things up ?

By the way, they all run Office 2010.

hi,
I have no idea why the macro takes so long to create all pdf files on yr side, I tried it with 4 sheets with colored tables and it took few seconds to attach them, close it and tried it again and I had the same result, few seconds.
I am really sorry, maybe try another approach, another code....

as I told you, it takes 4-5 seconds to attach the sheets as pdf, and I tested it 3 times, same result.
maybe someone else can help you with an idea...
I thought I could be of help!

Cheers

anytime, you are wecome, thank you for the rep and feedback. I am sorry I can not help you regarding the bug.