Excel 2016, VBA, Multiple sheets as separate attachments in 1 email

Hi,

I can't seem to solve the problem to add multiple attachments from different sheets (all from the same workbook), where i wish to add each sheet as separate workbook as an attachment into one email through a VBA macro script.

I did find several sites that show how to add a single workbooksheet as an attachment, which is easy enough to get working. This principle uses the 'active workbook / activesheet' to create a new workbook in the background from a single existing sheet. then add it as attachement and after that kill the temporary workbook on the harddrive.

Somehow i got it to get this script working for 2 separate workbooksheets as an attachement in 1 email, although i have no clue why this works, and more sheets give an error '53' or other errors.

As additional info, i use a form control button to get the macro working where i added some additional lines in the emailscript to get some information in the body. subject and other parts of the email. I also need that button to copy some data to the sheets before they are copied. Now this all works fine for the script below with 2 separate attachments.

I still need to add one more sheet as a separate workbook attachement to that same emailscript. If anyone can help me overcome this problem without receiving errors i would be very gratefull.

My current already working script is:

Please Login or Register  to view this content.

Hi Alpha Frog,

Thanks for the reply.
i notice you cleaned up my current code with some additional dim and better logic in where which part of code should be placed.
I'll try and test your stated codescript tonight or tomorrow, then i'll get back here to state if it works

Regards,

Arnoud Holtzer

Hi,

Thanks for the given code. With a little adjustments i got it fully working now
This code is used for emailing with multiple attachments and auto signature and data extracted from the workbook
to use in multiple ways, for example as body, mailadresses, template data related to buttonlocation and more.
Below the final working code:

Please Login or Register  to view this content.