VBA to save a sheet as a new workbook and email (using outlook) to a certain email id

Hello,

I have an excel file which includes multiple sheets ( say 40 sheets with different names). As a regular bi weekly task, I am required to send every sheet to a particular email id ( means take 40 sheets, save every sheet as per the name of that sheet and send it out to a different email ids. So net, i end up saving 40 files and sending 40 different emails with one attachment in each email). PS : All the 40 email ids are written in one of the sheets in the file.

Is there a VBA code which can help me in cracking this? Appreciate any help on this. Please let me know for any further details on this.

Thanks!

Thanks for the suggestion. I found one code in previous posts which fits my requirement.

However, when i am using the below code : I am getting the following error - "Run Time error '429' , ActiveX component can't create object" on the following line of code

Please Login or Register  to view this content.

Below is the overall code i using for this

Please Login or Register  to view this content.

Yes. I do. and i checked all my libraries are checked in outlook and excel as well.