Sending worksheets to a specific email if criteria is met

[krazeydla]

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.



[table="width: 500, class: grid"]
[tr]
[td]Unit[/td]
[td]Email[/td]
[td]MGR[/td]
[td]SUP[/td]
[td]EMP[/td]
[/tr]
[tr]
[td]Chair Committee[/td]
[td]oscar15@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Chair Committee[/td]
[td]oscar14@mail.com[/td]
[td][/td]
[td]X[/td]
[td][/td]
[/tr]
[tr]
[td]Chair Committee[/td]
[td]oscar13@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Chair Committee[/td]
[td]oscar12@mail.com[/td]
[td][/td]
[td][/td]
[td]X[/td]
[/tr]
[tr]
[td]Chair Committee[/td]
[td]oscar11@mail.com[/td]
[td][/td]
[td][/td]
[td]X[/td]
[/tr]
[tr]
[td]Blanket Staffing[/td]
[td]oscar9@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Blanket Staffing[/td]
[td]oscar8@mail.com[/td]
[td][/td]
[td]X[/td]
[td][/td]
[/tr]
[tr]
[td]Blanket Staffing[/td]
[td]oscar7@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Blanket Staffing[/td]
[td]oscar6@mail.com[/td]
[td][/td]
[td][/td]
[td]X[/td]
[/tr]
[tr]
[td]Blanket Staffing[/td]
[td]oscar5@mail.com[/td]
[td][/td]
[td][/td]
[td]X[/td]
[/tr]
[tr]
[td]Onion Repair Team[/td]
[td]oscar4@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Onion Repair Team[/td]
[td]oscar3@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]Onion Repair Team[/td]
[td]oscar2@mail.com[/td]
[td][/td]
[td][/td]
[td]X[/td]
[/tr]
[tr]
[td]Onion Repair Team[/td]
[td]oscar1@mail.com[/td]
[td]X[/td]
[td][/td]
[td][/td]
[/tr]
[/table]

 

[krazeydla]

Please help...I may not have done a good job with the wording of what I am looking for.

 

[Ginny in Ireland]

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

 

[krazeydla]

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!!!

 

[Ginny in Ireland]

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