Sending worksheets to a specific email if criteria is met

krazeydla

New Member
Joined
Dec 14, 2015
Messages
7
Thanks for helping me out...I need help. I am trying to send an email if there is an X in a specific column. So send an email to all Chair committee who have an "X" in the EMP column. And be able to switch it up for the other Units.

The Email attachment would be another sheet in the workbook...

I was able to find one from rondebruin's website, were I can send the worksheet with specific email info in the macro code...what I am trying to do is combine that, with this one...If they have the X, then they get the worksheet with the email message. Please help. If further info is needed, please let me know. Thanks.



UnitEmailMGRSUPEMP
Chair Committeeoscar15@mail.comX
Chair Committeeoscar14@mail.comX
Chair Committeeoscar13@mail.comX
Chair Committeeoscar12@mail.comX
Chair Committeeoscar11@mail.comX
Blanket Staffingoscar9@mail.comX
Blanket Staffingoscar8@mail.comX
Blanket Staffingoscar7@mail.comX
Blanket Staffingoscar6@mail.comX
Blanket Staffingoscar5@mail.comX
Onion Repair Teamoscar4@mail.comX
Onion Repair Teamoscar3@mail.comX
Onion Repair Teamoscar2@mail.comX
Onion Repair Teamoscar1@mail.comX
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Hi krazeydla
I don't see a reply to this, so just adding my comment. I'm not sure if I understand fully what you need. Will you be emailing 3 separate lots of emails - a MGR list, a SUP list and a EMP list? If this is so and you want it all automated then you can add a separate Worksheet to hold all your data, then using filter for each list copy this to a new worksheet and from there use Ron De Bruin's solution until all filters have been sent. An IF statement should cover the different worksheet that needs to go to the separate list.

Apologies if I misunderstand your requirements.
rgs
Ginny
 
Upvote 0
Hey Ginny,

Thanks for the info, I will try to clarify....

What I would like is after you run the report...

One email to MGRs of the Chair Committee

One email to MGRs of the Blanket team

One email to MGRs of the Onion Repair Team

and so on for the SUP and EMP.

I would like the to press the run button and it sorts and separates and emails everything. I have a macro to sort the data to different tabs per Units....So it would pull the data from the "data" worksheet and send it to the different Unit worksheets (the Chair sheet, Blanket sheet, and Onion sheet).

The next step would be to have the master email list and click run, and it would sort through the list and send the respective unit worksheet to the specified email list (from above^)..

I hope this helps...Please help!!!
 
Upvote 0
Hi krazeydla,

Have a look at the following: Mail Range/Selection in the body of the mail. Ron de Bruin has some marvellous tutorials and examples.

also: https://msdn.microsoft.com/en-us/library/ff519602(office.11).aspx

I have used similar to create automatically sent out emails to hundreds of people with an individual excel file as attachment. My process is usually to have 5 worksheets (maybe 6). These work with Outlook.

First is where I kick-off from a command button when the data is ready
Second holds the cells for the email message (you can't hold too much text in a single cell - the data won't hold formatting)
Third worksheet holds the data and 1 column of this is the email address for each row
Fourth worksheet is where I filter the unique email addresses to. This may be a little complex in your situation as you have to filter over 3 columns.
You will need to add coding to identify the last email address, copy the first and delete until you reach the last.
Fifth worksheet is where the data is copied out to (formatted) and from here is copied out as a new file for attaching to the individual emails.
Sixth worksheet - Sometimes it's hard to hold the formatting after copying the data out to a new worksheet, so I usually have a formatted blank ws and I include coding to copy then paste-formatting.

rgs
Ginny
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top