1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Auto Generated Email in Excel

Discussion in 'Business Applications' started by DiverHodge, Jan 4, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Firstly Hi,
    I'm Hodge and this is my first post on this forum, please (HELP)

    I have been looking around the various forum's trying to figure this out myself but with little success. There are many variants of this problem throughout these forums with many solutions, but none of which i've been able to adapt or use.

    I basically run a diving team with 27+ divers and have various spread sheets to manage the team, from equipment servicing to personnel run out dates. I have been trying to find/create a Macro that will automatically send me an email, with Cc's option, when cells in column M & O turn yellow or red. Then copy all the data within that row and email the address in column P as an attachment. I have looked at Ron de Bruin's code, and tried to adapt it without success. Once I have the correct code, I believe i will be able to adapt it to most, if not all of my spreadsheets.

    Hopefully someone has used this type of code before for the same reasons, Sorry must mention that I'm working on MS Office For Mac 2011, but don't think the process is much different. I don't really know much about VBA, so any help will be welcomed.

    I've attached one of my spreadsheets just incase.

    Thanks in advance for any help you may be able to offer.

    Hodgey
     

    Attached Files:

  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hoggey,

    Welcome to the forum.
    When using "Conditional Formating" Excel does not detect the changes.
    What you can do is use code the will look at the cells referenced in the Conditional range
    and determine if it fits the same criteria. If it does send the email.
     
  3. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Hi Charles,
    Thanks for the reply and advice, you wouldn't happen to know how how I would start this code would you. As you have stated, I have used Conditional Formatting to highlight cells as criteria is met. My problem is trying to reference these cells in VBA code and trigger an Outlook email from that. As I stated above, this is something I've been looking in to from a while but I can adapt anything I have found. Any further guidance you could offer would be greatly appreciated.

    Thanks Hodge
     
  4. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Hi Charles,
    Thanks for the reply and advice, you wouldn't happen to know how how I would start this code would you. As you have stated, I have used Conditional Formatting to highlight cells as criteria is met. My problem is trying to reference these cells in VBA code and trigger an Outlook email from that. As I stated above, this is something I've been looking in to from a while but I can adapt anything I have found. Any further guidance you could offer would be greatly appreciated.

    Thanks Hodge
     
  5. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    We can use a loop that would go down column "M" and "O" to check the value.
    If you can tell me the range of the value you wish to see if it to send an email.
    What is it for "Red" and "Yellow". Ie 1 to 365?
    With this we need to determine if the email was sent so that we do not send it again.
    As mention Excel does not detect a change caused by "Conditional Format".
    You can use a "Command" button to run the code.
    And to possibly a Worksheet Change event. This is predicated on the last entry that you would use when
    filling out the form.
    I would use the Command button myself.
    Now how would you determine if the Email was sent? Add a column for Sent?
    LMK
     
  6. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Hi,
    Thanks again for your quick reply

    The parameters I used for conditional format were
    1>365 = Green
    366>543 = Yellow
    544>1000 = Red

    I also used Formula (=IF(L8="","",TODAY()-L8))

    I had hoped that I would be able to leave the document open and maybe have excel run a daily check at a particular time, then once a cell triggered some type of event the complete row containing all details would be sent directly to Outlook and emailed to the respective addresses. If this is not possible then a command button would be fine, and yes I will have to add a column for previously sent emails, so I have sight of what has already been send. Sorry didn't think about that one.

    I sorry that i don't have a better knowledge of excel to provide you with a better more accurate description of what I am trying to achieve.

    thanks again for the assistance you've given me.

    Hodge
     
  7. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Hi,
    Thanks again for your quick reply

    The parameters I used for conditional format were
    1>365 = Green
    366>543 = Yellow
    544>1000 = Red

    I also used Formula (=IF(L8="","",TODAY()-L8))

    I had hoped that I would be able to leave the document open and maybe have excel run a daily check at a particular time, then once a cell triggered some type of event the complete row containing all details would be sent directly to Outlook and emailed to the respective addresses. If this is not possible then a command button would be fine, and yes I will have to add a column for previously sent emails, so I have sight of what has already been send. Sorry didn't think about that one.

    I sorry that i don't have a better knowledge of excel to provide you with a better more accurate description of what I am trying to achieve.

    thanks again for the assistance you've given me.

    Hodge
     
  8. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    Will try to have something in the next couple of days.
    Sorry to be so slow, but,,,,,
     
  9. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    HI,

    You mention Mac excel 2011 is this the system your using?
    Just want to make sure. The code for sending the mail in the Mac environment is different than windows.
    I have the code worked out to validate the data, am working on the email.

    Please note I have no way to test the Email code.
    Also will the range of data you show change.
    Or will the current worksheet stay the same.
     
  10. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    I found some code for the Email. I can not test.
    You will see a button for Test".
    This is only a test. So let me know what happens.
    I added a column for Email sent.
    You can look at the code and add the "CC" (I hope).
     

    Attached Files:

  11. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Hi,
    Yes I'm running Office for Mac 2011 and thanks for the code you sent me, I ran it and it came up with a compile error. I could see the error highlighted but didn't really understand it. I have attached a screen shot of the message, hopefully it will mean something to you.
    Thanks
    Hodge
     

    Attached Files:

  12. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    I think while your in the code module you will see "Tools" if you do select it and then you see "Reference" select it.
    Take a look at the pop up to see if says any thing "Missing" if it does deselect it and then scroll down to see if you can see a "library" with the same name or similar name and select it.
    Then compile to see if this took care of it.
    I'm in the process to see if I can load 2011 on my wife's mac. Not sure I can.
    I had a Mac, but it gave out...so it's back to the old pc...
     
  13. DiverHodge

    DiverHodge Thread Starter

    Joined:
    Jan 4, 2013
    Messages:
    22
    Hi,
    It's Hodge again,

    Did what you said in your reply and something started to work once I press the test button, but I think there be some sort of bug, it mass produced the work sheet 80 + times on my desk top. Do you have any idea's

    Thanks

    Hodge
     

    Attached Files:

  14. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    I'll look at the code.
    Also I tried to download 2011 to my wife's Mac, but the excel is for a "Intel" base system.
    If you know of an 2011 that is non Intel based let me know.
     
  15. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    My error. Left a validation for the "Email" address out.
    See if this works.
     

    Attached Files:

  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083758

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice