A Macro That Will Send E-mail When Cell Is A Certain Value

  • Good morning!!


    I hope I am doing this right. It is my first time. I am currently deployed to Iraq, and my commander wants his Excel spreadsheet to do something special so here we are. I have no where else to go. We have a current listing which has a cell count down til an expiration date is met. He would like to have the system send an e-mail to the responsible commander of the unit when their cell reaches 60 days left, 30 days left, and then again when their account has officially expired. There are 58 sheets in the workbook with different units listed and different commanders, all with expirations dates differing from each other. All the countdown cells are in C18. I have no experience in macros, so please go easy on me. Also the cell C18 is a formula with conditional format. So let me break it down of exactly what he wants since noone has answered this question yet:


    1. I need a macro that will look through all 58 sheets at the C18 cell and see if the value is 60, 30, or 0.


    2. If it matches any of these values, then it needs to send a specific e-mail message through Outlook.


    3. The message will be sent to the individual responsible for that sheet in the workbook. Their name appears in the sheet.


    4. This e-mail should run automatically without any input from the user.


    5. This macro should run as soon as the "Account List.xls" is opened.


    6. This macro will only be run once per day.


    Thanks for all your help, and your support. GO ARMY!!

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value



    That code belongs in the Workbook code module.


    hth,



    Mavyak, AW2 (NAC) 1989-1998


    (Fly Navy!)

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Ok, I pasted the exact code you supplied, and it didn't work. My e-mail addresses are in C13, and so I changed all E18 to C13, saved the file, and reopened. When I did this it asked if I wanted to Enable Macros, so I clicked enable. Then it went through some process where it stated that it was attempting to send an e-mail, and asked if it was okay. I said yes go ahead, and then it gave me: "Run-time error '-2147467259 (8004005)': There must be at least one name or distribution list in the To, Cc, or Bcc box." error. I am not sure what I did wrong, but obviously something ain't right. Please help.


    PS. Don't tell anyone that I am getting help from a Navy guy, cause these guys would never let it go. LOL!!

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Does cell C13 contain an actual email address or just the name of the commander responsible for that particular worksheet?


    Another quick trouble-shoot would be to change the ".Send" to " .Display". That should open up all the emails created and force you to manually click "Send". It would also avoid the innate security feature from popping up stating a program is trying to send the email on your behalf. But most importantly, it will display what value is being populated in the "To" line of the email.


    Yet another thing to keep in mind is the code automatically creates a comment in cell C18 when an email is generated stating that the email for the respective day reminder (0, 30, or 60) has been sent. Each time the workbook is opened, if cell C18 contains a 0, 30, or 60 it then checks the comment on that cell to see if an email has already been sent. If it has, it doesn't send another (and vise-versa). That being said, until the macro is smoothed out and fully operational, you may need to delete the comments in cell C18 on every sheet prior to running the macro. Be careful, though, this could result in some commanders receiving the same email multiple times depending on where the code is crashing (whether it's before or after the send function of the code).

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    First of all, thanks for your help with this. Cell C13 contains the actual email address in blue and underlined (looks like a hyperlink to me). As far as the sending multiple e-mails, I have decided to use my e-mail address so I don't have every Captain, Major, LT Colonel, and Colonel screaming at me for loading their inbox with messages (also to prevent them from disregarding these e-mails thinking they are just a test especially once this thing is up and running). Another thing is when I get the run time error, I click the debug button and it highlights the .Send command. I am not sure why it won't extract the e-mail address out of C13, unless it needs to be a standard input, and not the hyperlink Excel likes to automatically create when any website or e-mail is typed in. I also tried the .Display command and it does pull the e-mail address from the C13 cell, but that kind of defeats the purpose of doing everything automatically. One other thing (and I know I am probably getting on your nerves now) is that Worksheet 1 is known as Main Menu, and should be skipped. The code is trying to send an e-mail to nobody cause it reads the main menu page first. I would love to send you a copy, but due to security reasons I can't send the spreadsheet with our unit's numbers on it. Sorry. Please help when you get a chance. Thanks again!!


    PS. Why can't the Army win just one time against the Navy?? Can you guys just let us win so we can pretend like our team isn't that bad??

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Here's what I think is going on. The code is testing the first page and possibly generating an email (Cell C18 of the Main Menu have a 0, 30, or 60 in it?) to no one since cell C13 is blank (or a bogus email address). To bypass that I wrote a couple extra lines of code to not process the sheet named Main Menu. Hopefully the following code will remedy the entire deal.


    Replace all your code with the following:


    As for Army/Navy, take it from a long-time Lions fan, I know heartache.

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Ok, here is the update. I recopied the code, and it said it was sending, and then ....nothing. It was the first time my computer and myself both had the deer in the headlights look. I deleted the comment in the cell, and saved and tried to open the sheet, and it still acted like it ran with no problems. At least this time the run-time error did not come up. Any ideas?

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Did you check the sent folder of Outlook to make sure the emails weren't sent? Also, were there any sheets that qualified (had a 0, 30, or 60 in cell C18)?[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Also, just for some confirmation that the process ran, change the following two lines of code:

    Code
    Set w = Nothing 
    End Sub


    to this:

    Code
    Set w = Nothing 
        MsgBox "Applicable emails sent to respective commanders."
    End Sub
  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Ok, new update. I think I got it to work. Here is the problem. We use our ID Cards, which have a computer chip in it, to login to any military computer allowing certain security priviledges. Every now and then the card will get stuck in a loop and will not be readable. Because of this the outlook will not work until you remove the card and reinput your pin. It seems to be doing great, but I will final test it tomorrow, run it by the commander, and give you a heads up of what he says. For now, we are changing shifts, and I am ready to get out of the office and back in my bunk. I will let you know, thanks again!!

  • Re: A Macro That Will Send E-mail When Cell Is A Certain Value


    Well, it works like a charm. Thank you so much!! The commander loves it and he said kudos to you for working with us so quickly. If any changes or modifications to the original request comes up, I will be back in here asking for some help again. I do like the idea of the MsgBox, so I will put that in as well. Thanks again for everything!!


    PS. As far as being patient on the wins, trust me I know, I am still waiting on my Dolphins to win at least one game and the Patriots to lose one game to protect my '72 Dolphins record. LOL!! Take care!!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!