VBA button that emails from a dropdown list based on cell values

[Chocobits]

Hi!

I'm looking for assistance in creating a VBA button that adds a drop down list of people to email that appear in one sheet, and emails them based on
a list of contacts with contact info on a second sheet. For instance, sending out schedule changes.

I would simply attach the template I have but the attachment button doesn't seem to work(?).

Here's a screenshot of Sheet1

Sheet1.PNG

Here's a screenshot of Sheet2

Sheet2.PNG

Sheet1 has information that changes daily - We'll call it Schedule Changes
Sheet2 is the contact list for each store, their manager and respective district manager - We'll call it Store Information

I know the format of this spreadsheet could be improved but I don't have control of the format of the incoming spreadsheets

The object of this speadsheet is to use information in Store Information sheet to associate store number with store manager, and store manager with district manager, and then create a VBA button with drop down list of district managers that appear in Schedule Changes sheet, and send an email to the store, store manager, and district managers associated with the district manager that appears in the Schedule Changes tab, as well as to OtherEmail1 and OtherEmail2.

For Example:

Clicking the button and selecting "DistrictManager1" will email Store1 and Store2, the managers of Store1 and Store1, and DistrictManager1, as well as OtherEmail1 and OtherEmail2.

District Managers that don't appear on this tab won't appear in this dropdown list (if possible)

The email will have a format something like this:

Subject: Schedule Changes

Body: Schedule Change Notice

The contents of Row 1 (the labels) from Schedule Change sheet
The rows corresponding to that district manager from Schedule Change sheet

Your help would be greatly appreciated because I have a number of other spreadsheets in desperate need of some kind of automation and
I don't have a lot of experience pulling information from multiple sheets from a VBA perspective.

[Chocobits]

Also, if I needed to create a third sheet, which pulled in the store manager and district manager based on the store numbers that appear in the Schedule Change sheet and put their contact info from the Store Info sheet, how simple would that be?

[Chocobits]

Here's a rough idea of the template I am working with.

[Chocobits]

I spoke with my girlfriend who is a little more excel savvy than I am, and she seemed to think VBA might not be necessary for anything but the automated email. TBH if I could just figure out how to sort the information such that when I paste the new Schedule Changes into sheet1, the information would be auto populated into a sheet3 which would have a format with columns like this: Store Number, Date of Schedule Change, Updated Schedule, Store Manager, District Manager, Store Email, Store Manager Email, District Manager Email. From that I could copy paste for email notifications, but eventually I would like to be able to generate emails based on selecting District Manager from a drop down list. Any help would be greatly appreciated!

[Chocobits]

If there is another way to accomplish what I'm trying to do I'd be open to hearing about that as well!

[Chocobits]

Hmm, no takers yet for help. Is what I'm trying to do very difficult?

[Chocobits]

I know my verbiage might not be entirely up to snuff. I've kind of been thrust unwillingly into the realm of spreadsheet administration If anyone needs clarification on what I am trying to do before offering assistance, I am glad to oblige, and do feel free to ask!

[Chocobits]

48 hour bump.

[darkorder]

My recommendation would be to make your xlsx an xlsm macro enabled workbook and then do all of this with a DistrictManager selection button and an Email button.
1) You will need VBA installed for your Excel.
2) These buttons will need to be ActiveX controls.
3) If you have all of the data you need on your two sheets I do not believe you would need anything to be copied to an additional sheet.
4) There are a number of posts discussing how to email via VBA so I will not cover that but here is how to populate your DistrictManager button.

Please Login or Register  to view this content.

[darkorder]

Here is an example VBA email code that should work with your template.

I commented out the .Send command (see comment) and added a Reset sub for editing purposes.

IMPORTANT: Ensure that the names, of the column to be checked, are consistant accross sheets. The example you posted had DistrictManager1 on the 'Schedule Changes' sheet and District Manager1 on the 'Store Information' sheet. If the name that is being checked is not the same the code will not find the appropriate emails.
EG: JamesKirk is not James Kirk

Please Login or Register  to view this content.

[Chocobits]

Very intense replies! I will need time to test that and even longer to figure out what I am doing, but I appreciate the response!