Multiple Macros on Single Sheet

Hi, I originally had an excel workbook with multiple sheets and each sheet had a macro to automatically generate an Outlook email if a cell in a certain column contained a specific value. I have to essentially re-create the workbook with all of the information on one sheet, but I would still like the macro to work on multiple columns within the same sheet. However, I cannot figure out how to properly duplicate the macro to continue running on the same sheet for multiple columns. If anyone can please help me with this I would really appreciate it. The code for a single column is below. Thank you in advance!

Please Login or Register  to view this content.

Thank you for your response.

I could extend the columns out but then I wouldn't be able to distinguish the information that is output in each column reference. I need to have different email outputs for different columns and I don't think that this would allow me to do that right?

Unfortunately I don't think that will work either, because ideally I would like to send the email to different recipients as well if the different columns are chosen. If there was a way to just duplicate the macro and have excel run all of them on the same sheet then I think that would be the best way.

Is there anything that you think can do that?

Thank you!

This looks like it will work, but it doesn't seem like the macro knows where to look now. You removed the "Target Range" of cells. When I tried to place that line back into the code then VBA compiler gave me an error.

Weird, I changed the value from "Yes" to "YES" and now it worked! Thank you so much, I think this is exactly what I need.

Hi Bernie,

I know this is an older thread but I'm hoping maybe you can help me on an additional problem I'm having in this macro. I have an additional column that I want to send an email based off of a cell value and not just on whether the cell says "Yes." I still want to keep all of those "Yes" conditionals, but I want to add in another reference if one particular column has a value > 1,000. So my issue is in finding a way to add in this value based automatic email while still keeping the "Yes" based emails in there.

Could you possibly help me out in figuring this additional layer out?

Thanks!

Eric

Bernie,

Thanks so much for the quick reply here. So that addition seemed to work, somewhat. You can see below that the column that I'm specifying for the > 999 is column F. When I added in your code, and the Target.Column = 6, I do not get the email output when I enter a value greater than 999 in column F. However, I DO get the email output after I delete the value that I just entered from the cell in Column F.

Please Login or Register  to view this content.

This code is followed by the Sub Mails

Eric

Bernie,

When I changed to that now I am not getting any output based on column F.

Eric

Bernie,

I would actually like the macro to run in every row moving forward. So if a row in column F happens to have a value greater than 1000 then it'll spit out the email with information from that row. That is how all of the "Yes" valued outputs are working currently, they track whatever row that "Yes" was entered in and return information specific to that row.

Here is the attached sheet with most information removed. I left some information in the most recent entry, row 790. In the file you can see a number of columns that track "Yes" and then spit out information based on that entry. However, you can see the relevant column "F" with the value amount.

Eric

I tried the original convention you sent over first and didn't work so I messed around with that sign a few times before replying back.

Ok thanks.

However, values greater than 1000 in column F are still not calling to Outlook6 Sub mail.

Yeah so what I would like to do is have the code run if any of the values in the specified columns are Yes and also run if the value in column F is greater than 1000.

Bernie,

Thanks again for all the help. This is closer - what the code you sent does is send out the email if column F is greater than or equal to 1000, this part is good. 2 things about it that won't work though is that now none of the "Yes" columns run and column F only runs if another "Yes" column has been entered. I changed the code to the below (removed your If Target.column line) and this allows all of the "Yes" columns to still run. It also allows column F to run if > 1000, HOWEVER, it's still only running in column F if another "Yes" column is entered. I need column F to independently run if > 1000.

Please Login or Register  to view this content.

Also, I've noticed that the email will run if column F is greater than 1000 AND another "Yes" cell is entered, but it doesn't run Mail_small_Text_Outlook6, it runs the mail outlook code for whatever column the "Yes" was entered in.

I would want a totally independent email chain to be sent out. So each "YES" column has an independent email body and recipients that go along with it and I want the same but for column F > 1000.

At your last suggestion, I actually had an idea to try something else. It actually got a bit closer, now what I just explained above is essentially achieved, however, now EVERY column that has a "YES" simultaneously sends an email if any new "Yes" columns are checked. So previously, lets say for example I clicked "Yes" in column 12 - then only column 12 email chain would automatically be sent out. THEN if I clicked "Yes" in column 16 - then only column 16 email chain would be sent out. With the code below, EVERY column that has "Yes" gets simultaneously created each time a new "Yes" is selected, which I do not want. However, the code below has allowed Column F to independently generate it's own email chain with a value entry greater than 1000.

Please Login or Register  to view this content.

That looks like it did the trick!

Thank you so much Bernie, this is a huge help. Much appreciated.