Excel vba sending email validation list

Hello Experts,

I have one File with me. I want to create a button in sheet 1. The Column A shows list of locations. So once I select the the location from the drop down list (say I select "Searcy" as my location) to whom I want to send emails. So all the 3 rows which has the records/data for Searcy location, Excel should automatically attached this file to Outlook (there should be only 3 records to this attached file in Outlook).

This should also contain the Subject line and the body. In my email Subject Line and body will be same for all the locations I'm sending e-mails to, only the location will be changed.

Let me know if you still require any additional clarifications.

Many Thanks,
Prakash

Dear Gautam,

We can assume anything for the Subject line and the body text:
For Eg: Subject : Dashboard Reports for Feb 2015

Body : Dear All,

Thank you for submitting Kaizen focused on improvements.

It is really good thing that we are participating very enthusiastically to build this culture in SCMSSC.

And about the drop down - i mean to say, it's the filter I have applied to the header to my last attachment. A1 is named as location, so once I apply filter to Searcy Plant, only that records shoudl get emailed.

Dear Vikas,

I've assigned the Macros to Button. Below are the highlights when I tested this Macros:
1. When Filtered as per my requirement and clicked on the Button, email gets delivered to the added recipient.
2. The whole sheet gets emailed included the button and Macros.
3. I also observed that once I hit the button to send email, the Num Lock on my keyboard gets off.

What basically I wanted -- that when I click the button to send email, only the filtered records (say for Searcy Location) should get copied to the new workbook and should get saved if required to the given path. If there are 100 records of which there are 20 records for Searcy, so only the records of Searcy should get emailed.

Thanks,
Vikas

Dear Vikas,

It's working perfectly..... now lastly 1 question....... how do I assign this macro to a button.....
Now I want a button on my sheet.... and once I filter my required selection and click on the button, email should get delivered to added recipients.

Many Thanks Vikas

Dear Vikas,

Wonderful............ It's working very perfectly. If anytimee u r in Pune, you owe a beer from me.