How Do I Batch Send All Worksheets in One Excel Workbook as Attachments In Separate Emails

[CherieLewis]

I found the following code, which works great, but it embeds the worksheets in the email. How do I tweak it so that each worksheet is an attachment to the email? I have 13 worksheets in a workbook and they all have a unique name, so I'm not sure if that plays in to this.

 

[Siddharth Rout]

Whenever you want to write code, first envision what it should do. Next if you prefer, write the logic down on a piece of paper or type it in a notepad. Only when the logic is clear will you be able to code.

Now couple of questions

Since each worksheet is going as an attachment in a separate email, is there any criteria as to what should be the email address or the subject or email body content? Or you want to simply loop through the worksheets and send them 1 by one to the same email recipient with the same subject etc etc. What is the logic that you want to follow? If you can explain all of that in your post, it will be easier for me to assist you in your query.

 

[CherieLewis]

I am trying to accomplish an easier way to send out commissions to our sales reps. Currently, our controller attaches each excel worksheet from a workbook and email one by one. There are 13-20 reps that could receive the email. The worksheets are named for the rep. I was hoping to make the process faster. The subject would be commissions. It would be nice if I could tie an email address to the rep name on the worksheet, but I could also work with a macro that fills in a generic email and I can change it. I don't believe the controller adds any message in the body of the email. Does this help?

 

[Siddharth Rout]

If I have understood you correctly, then this should be your logic.

To make your job easier and for the macro to work without any errors do this

1. Create a Sheet called, say, "Employee Data". In Column A, put employee names, In Column B, add their email addresses.
2. Loop though the above employee data and check if there is any worksheet with that name. And if there is, then simply copy that worksheet to a new workbook and then email it.