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.

Email notification from Excel

Discussion in 'Business Applications' started by satrigger, Oct 9, 2019.

Advertisement
  1. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Hi I am trying to get Excel to email me when a number in a cell counts down to 30, I am new to vba and struggling, any help would be appreciated
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Hi, have you even tried searching the forum? There are similar questions that have been asked (and answered) which you could adapt to your needs with a little macro editing.
    I suggest you also include the Excel version and OS you're using
     
  3. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Hi Keebellah, I did find a macro to edit and use, thanks for your reply(y)
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Great, happy coding :)
     
  5. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Hi Keebellah
    I wondered if you could assist again, I have managed to get the vba code working for my spread sheet, it alerts me when cells in a column = 30, however I am trying to get it to check the same column in each sheet, it only does the first sheet. I have tried different additions to the code but cannot get it to work correctly

    Thanks
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    No problem, but without the file (make sure to attach it with non-private data and invalid email addresses) it's hard to explain.
    You can of course add a loop to check eacg worksheet that needs to be checked, and the column checking remains the same, but, without the file, It's easier to show than explain
     
  7. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Hi Keebellah
    I have uploaded a dummy file with email removed
     

    Attached Files:

  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Got the file, will take a look
     
  9. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    Okay, here's the corrected code for any number of worksheets that have the saem lay-put and starting with able rows on row 8.
    It has an extra check to see if row 7 is the correct header, checking if C contains asset number and D dispatch ....
    All checked in lowercase just in case.
    Hope this does the job
     

    Attached Files:

  11. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Hi Keebellah
    It works perfectly, I will add the data in that I took out for security and run it again
    Many thanks for your help again, very much appreciated (y)
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    I do have some points that you should pay attention to.
    There is no check if there is a (valid) email address in the email column and maybe you should also add checks if the asset number is not empty, and things like that.
    Once you have code like this and you want it to run without a hitch then you should be prepared for issues that may disrupt the process.
    You already have the Reminder sent but you could also use this cell to enter something like Invalid or missing email address or something else, then you will also have to change the check to sent the email, instead of <> "Mail" you could say <> "" that means there is nothing in that cell.
    Think about it.
     
    satrigger likes this.
  13. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Ok, understood Keebellah, I will try this, thanks again for your help
     
  14. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,601
    First Name:
    Hans
    I separated the sending part so that you can use it for other processes, just pass the parameters and the module can be used with other macros.
    I also added a check if the email cell is not empty
     

    Attached Files:

  15. satrigger

    satrigger Thread Starter

    Joined:
    Oct 9, 2019
    Messages:
    11
    Hi Keebellah
    I have added some more sheets to the workbook, for some reason the code doesn't work on the new sheets, I have looked through the code but cant work out why, could you shed any light, maybe its something simple I am missing

    Thanks
     
  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...

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

  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