VBA to email specific data from a table to multiple people

[Teblol]

Hi All,

I have an excel sheet that has data in columns A through X. My end goal is to create an email for each account, and each division to send to managers
in my office. We have four different warehouses that hold stock. If an order comes in and the stock is not available in the default warehouse we must
request to move them to another warehouse. If an order comes in and an item is not already allocated we need to request to allocate that item. My sheet
allows us to automate the request part of the process however I have been unable to come up with a way to create a code that will automatically send
an email to each of the different divisions once all of the requests have been created.

In my example I have two different accounts. Under one of the accounts there are 6 different divisions. This means an email needs to be sent with the
items for that division to a specific person. Not only that, but all of the requests must be in order. If a few items need to be moved to 1001 then it must
appear as below on the email:

Can I move to 1001?
Item1 x"quantity needed"
Item2 x"quantity needed"

Then the next set of requests would be listed with a space below that:

Can I move to 1002?
Item3 x"quantity needed"
Item4 x"quantity needed"

If there is not a way to do this in one step, I created what the results may look like if I were able to come up with a code to first extract the data to new columns
in order of how they should appear. The results can be found in columns AA through AG. However, if someone is able to come up with a code to do this all at once,
the emails should appear as below.

First email:
To: "Specific email address for division 1"
Subject: "Account name"

Can I allocate in 1031?
9130203H048 x96

New email:
To:"Specific email address for division 6"
Subject:"Account name"

Can I allocate in 1001?
93012Z x72

Can I allocate in 1002?
93012Z x144

This process would repeat until all of the divisions are covered for the first account and would then need to be done for the next account. I am praying someone out there
is able to come up with something because this would save me and coworkers a whole lot of time.

[mikeTRON]

I opened the sample file and cannot understand what you are trying to accomplish.

What specifically can we help you with?
This forum does better by solving explicit problems instead of solving an entire project for you, so I would suggest scoping in and working on each step individually.

[Teblol]

Sorry I thought I was really clear. I am not looking for people to "solving an entire project for me". I simply need to be able to have a code that when I click a button, it will create a new email for each account number and division within the account number that I have open on Sheet1. So in my example I have two accounts. The first account number is 6274. There are 6 different divisions currently open on the account - 1, 6, 9, 13, 11, 12. Therefore I need 6 emails created that list the items for that division (column E), the quantity on the order (column O), and the request (column X) listed in the body of the email. I suppose I will leave it there for now and see if we can come up with something. Thank you for the reply and please let me know if you would like me to clarify anything up.

[Teblol]

Ok so there have been no hits so I either haven't explained what I am looking for properly, or it just isn't possible. Let me try to simplify it even more.

1. I have a data table that is on sheet1 and the range is A:X.
2. In column F there can be up to 6 different numbers - 1, 6, 9, 11, 12, and 13.
3. For each number that is listed I need an email created. So if only the number 1, 9, and 13 are listed, I need 3 emails created.
4. Continuing with the example of only having the number 1, 9, and 13 listed, the email that was created for the number 1 I would like all rows
that have the number 1 in column F to be put into the body of the email. All rows that have a 9 in column F should be put into the body of the email
created for the number 9. All rows that have a 13 in column F should be put into the body of the email created for the number 13.

Lets start there for now. And to be honest, at this point if someone is able to help me do this for just 1 number at a time that would be great.

[mikeTRON]

Your request is 100% possible it is just too large of a request for ME and likely the other forum participants to knock out in our spare time.
This is why I suggested you break your project into steps and see what you can do on your own and when you need specific help on ONE of the steps you can come ask for advice.

You can take offense to my wording again, but I am trying to help YOU help yourself.

If you want my advice, I would use 6 pivot tables to natively and automatically split out the data into the 6 potential reports, unless that 6 is just a random number and the actual number will be much more, then I would loop through filter options in ONE pivot table using VBA.

[Teblol]

I did not take offense to your reply. I tried to take your advice and break it down to a smaller request. Perhaps you are right and what I am asking would take too much time for someone else to come up with. I have been working the past two days on an answer for myself. Normally I spend hours on google prior to posting on here. I thought that this may be a simple request if I had only known the code however your response makes me think otherwise. Back to google I go.

[Teblol]

I appreciate the tip. I have actually been trying to do just that. I have the below code I am using for each number but I have to create 6 different macros for each number. Is there anyway that in the Value position I can have it be the data in a specific cell rather than the number 1?

Please Login or Register  to view this content.

[mucbeanz]

Very easy to do but there are few layers . . if you want email code i'll add it below or just google it ron v brun i think his name is very good.


first things first

you'll need a loop down column "f" . . using a if statement to pull out the the item and copy to each number temp new sheet make sure you variable each sheet ( 1 = sht1 , 3 = sht3 etc ) .
once the a thru z is complete assign email each sheet out to its respective owner


I've made quite a few on these sheets up. they're very easy to do . . mind you my first one was quite a challenge but like mentioned earlier i could very easily throw it together but sounds like you want someone to do it all.

i will help you with the functions or line items, how very versed are you in VBA? can you handle coaching? It really helps when you break it all down into small steps. i.e. sort and create, distribute, then the glorious msgbox function saying its been done too the user.

[Teblol]

Yeah sorry I didn't mean to offend anyone. I didn't realize how extensive my request was. I have realized it now and I posted just one step that I need help with at the moment. I am much much much better with formulas. Unfortunately when it comes to VBA I am able to follow along however creating my own from scratch is tough.

[mucbeanz]

ok lets work this thru

step 1
your first step you want to create a new temp sheet for each location and assign it a variable

step 2
loop through column f and identify each number if number exist copy that line to its respective sheet

step 3

format your temp sheets so its pretty


step 4

email each sheet

step 5

delete each sheet




don't worry about nothing right now till you sort a sub to create a temp new sheet for each location



i have to goto work right now but ill help when i can

[Teblol]

I appreciate the help! I have come up with everything up to Step 3. I will work on more of it tomorrow.

I have two macros that I will combine at the end. I did't want to add the macro that deletes the worksheets so I can see each step right now.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Teblol]

OK so I have figured out how to do it. Below are the 3 codes I am using. If I run them one at a time they work perfectly however when I try to run them together by either combining them or calling them the part that sends the emails does not work. The code is very sloppy since I had to learn how to do each step, but hopefully you will be able to read it. If someone could advise what could be causing the email part not to work when they are combined it would be greatly appreciated. I do not receive any error messages.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[mucbeanz]

not sure how how well that code would work TBH it isnt making any sense to me i like to view things simplify

Please Login or Register  to view this content.

i have to got work . . . will post again later tonight

[Teblol]

For the new worksheets code I have the names I want each worksheet to be in column AA. The code uses the data in column AA as the new sheet names. But I know for a fact each code I listed works. When I run them one at a time the process goes 100% as I need it to. But when I call them all together, or combine them, the sending of the emails does not happen. It appears as though it simply skips the code.

[mucbeanz]

Please Login or Register  to view this content.

[mucbeanz]

This is edited email code from ron V bruin Best code ive found that really works well.


if its alway the same email if code it in and use a dry loop through each sheet

Please Login or Register  to view this content.

Please Login or Register  to view this content.