Hello. I’m hoping someone can assist or point me to a solution to what I’m looking for. Below is an example layout of my datasets.
I have a spreadsheet with 2 tabs. The first tab has a list of all repairs that are in the building. The second tab, has all the store email addresses. My goal is to be able to quickly prepare daily repair update summaries to send to the stores.
I have macros built that will refresh these datasets when the user presses the “Refresh” button. I want to add a second button that allows will do the following:
There’s about 90 different stores in a given day that will receive one of these emails. I’m open to any suggestions or changes as long as I get to the end solution of being able to send the stores daily updates. Let me know if this is possible or if something like this has been done somewhere else. I appreciate the help! Thx!
Tab 1: Repairs
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Store #[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Desc[/TD]
[/TR]
[TR]
[TD]987654[/TD]
[TD]S123[/TD]
[TD]1/1/18[/TD]
[TD]Backorder[/TD]
[TD]Memphis[/TD]
[TD]Replace Chip[/TD]
[/TR]
[TR]
[TD]987435[/TD]
[TD]S123[/TD]
[TD]2/1/18[/TD]
[TD]Backorder[/TD]
[TD]Detroit[/TD]
[TD]Replace Button[/TD]
[/TR]
[TR]
[TD]897235[/TD]
[TD]S123[/TD]
[TD]3/1/18[/TD]
[TD]Backorder[/TD]
[TD]Atlanta[/TD]
[TD]Replace Glass[/TD]
[/TR]
[TR]
[TD]156832[/TD]
[TD]S100[/TD]
[TD]3/3/18[/TD]
[TD]Backorder[/TD]
[TD]Dallas[/TD]
[TD]Replace cover[/TD]
[/TR]
</tbody>[/TABLE]
Tab 2: Store_Info
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Store#[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]Email[/TD]
[/TR]
[TR]
[TD]S100[/TD]
[TD]AL[/TD]
[TD]Saban@alabama.com[/TD]
[/TR]
[TR]
[TD]S123[/TD]
[TD]FL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S200[/TD]
[TD]WA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a spreadsheet with 2 tabs. The first tab has a list of all repairs that are in the building. The second tab, has all the store email addresses. My goal is to be able to quickly prepare daily repair update summaries to send to the stores.
I have macros built that will refresh these datasets when the user presses the “Refresh” button. I want to add a second button that allows will do the following:
- Break each store’s data into their own file, including line 1 with the column headers, (Orders on tab 1 are always sorted by Store#) and save the file as the Store# and current date.
- Next, the VBA would open an email in Outlook, look at tab 2 and find the correct store email address based on the store number.
- The email doesn’t need to send. I just want it to be able to prepare the draft.
There’s about 90 different stores in a given day that will receive one of these emails. I’m open to any suggestions or changes as long as I get to the end solution of being able to send the stores daily updates. Let me know if this is possible or if something like this has been done somewhere else. I appreciate the help! Thx!
Tab 1: Repairs
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Order #[/TD]
[TD="align: center"]Store #[/TD]
[TD="align: center"]Date[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Location[/TD]
[TD="align: center"]Desc[/TD]
[/TR]
[TR]
[TD]987654[/TD]
[TD]S123[/TD]
[TD]1/1/18[/TD]
[TD]Backorder[/TD]
[TD]Memphis[/TD]
[TD]Replace Chip[/TD]
[/TR]
[TR]
[TD]987435[/TD]
[TD]S123[/TD]
[TD]2/1/18[/TD]
[TD]Backorder[/TD]
[TD]Detroit[/TD]
[TD]Replace Button[/TD]
[/TR]
[TR]
[TD]897235[/TD]
[TD]S123[/TD]
[TD]3/1/18[/TD]
[TD]Backorder[/TD]
[TD]Atlanta[/TD]
[TD]Replace Glass[/TD]
[/TR]
[TR]
[TD]156832[/TD]
[TD]S100[/TD]
[TD]3/3/18[/TD]
[TD]Backorder[/TD]
[TD]Dallas[/TD]
[TD]Replace cover[/TD]
[/TR]
</tbody>[/TABLE]
Tab 2: Store_Info
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="align: center"]Store#[/TD]
[TD="align: center"]State[/TD]
[TD="align: center"]Email[/TD]
[/TR]
[TR]
[TD]S100[/TD]
[TD]AL[/TD]
[TD]Saban@alabama.com[/TD]
[/TR]
[TR]
[TD]S123[/TD]
[TD]FL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S200[/TD]
[TD]WA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]