+ Reply to Thread
Results 1 to 16 of 16

Automatic E-Mail notifacation based on Date in cell

  1. #1
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Automatic E-Mail notifacation based on Date in cell

    Hi All,
    First off, I need to let you know that I know nothing about writing VBA code. any Help/Direction is greatly appreciated.
    I know Auto E-Mail has been addressed in other posts but I am inexperienced when it comes to writing VBA code, I have looked at several posts and tried to decipher the layout of the codes, some of it I can figure out, most of it is beyond my skillset.
    What I am trying to do is create a sheet with a list of machines in column A that need greased on a 90 day interval based off a date that is entered in column B. When the date in column B is reached an automatic E-Mail is sent with Outlook, this notification would repeat when the next 90 day interval elapsed.
    Attached is a basic idea of what I need to do.
    Attached Files Attached Files

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell

    Will all the dates be filled in? Or will the next 90 days only be filled out after the lubrication happens?


    Will you be running this daily?
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    what I was thinking is, a starting date would be entered in say A3 and the E-mail would be sent 90 days after. When the Lubrication actually happens the date would be entered in A4 and the next 90 day period would begin.

  4. #4
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    I should also add that I am trying to understand the Conditional Format so I can apply it to the cells, I am not sure if it will interfere with the VBA Code

  5. #5
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    Sorry, To answer you last sentence, if that's what I need to do, it wouldn't be a problem opening the worksheet on a daily basis, can it be setup to check itself daily and send all the machines that are due?

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell

    Please Login or Register  to view this content.
    Change the email address to the one you would like to send it to. If you want the email send automatically then uncomment '.send

  7. #7
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    WOW....... OK I selected Cell C1, opened the visual Basics tab, inserted a Module, and Pasted the Code in to the box.

    And how would I go about testing the setup?
    I have entered my E-Mail into the code, do I need to delete the note in green (set Email address here)
    Last edited by Scott59; 03-20-2014 at 12:05 PM.

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell

    I made a few changes. See what you think of this Solus Edit.xlsm

  9. #9
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    I need to retain the dates of Lubrication on the Sheet for Historical Purposes, If I have a bearing go bad I need to have a reference to when and how often the lubrication occurred.
    Also If I change the "90" reference in the code to say "60" the notification will happen every 60 day's correct?
    Right now I have over 100 machines in different area's of the plant, different areas have different supervisors, Will I need a column to insert the individual emails into for the code to referance? I will also need to CC multiple supervisors on the Emails.

    Solus, Thank you soooooooo much for helping me,

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell

    Here it is in your original format with a supervisor column. Click the button to test. Solus Edit.xlsm

  11. #11
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    Dude...... U are awesome, Thank you

  12. #12
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell

    Glad to help. And welcome to the forum!

    If you are happy with the solution please mark the thread as [SOLVED] using the thread tools at the top.

    And with any thread you can show your appreciation for anyone who has helped by clicking the * below their post.

  13. #13
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    Hay Solus are you still out there? I sure could use your help again?
    I Have been experimenting with the 2 Codes you provided(one with Auto Due Date and one with E-Mail Column) they are great!!!
    Is it Possible to Get the Code with the Auto Due Date(the 1st Edit) with the supervisor Column?
    basically combine the two.
    Last edited by Scott59; 03-20-2014 at 04:04 PM.

  14. #14
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell

    This would lose track of your past lubrication dates. If that is not an issue I will try to work it together.

  15. #15
    Registered User
    Join Date
    03-20-2014
    Location
    Iowa USA
    MS-Off Ver
    2010
    Posts
    9

    Re: Automatic E-Mail notifacation based on Date in cell

    Hi Solus
    Thanks for getting back to me. No its not an issue, i will have to figure out something else for that, The code with the Supervisor E-Mail is how i will need to go becouse of the different areas in the plant. if you can work the auto due date into the the code that would be Great,
    Would you have any suggestions on how i could save the dates ? maybe have them move to Sheet 2? im not sure how to make this happen, i may have to just enter the dates manualy.

  16. #16
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Automatic E-Mail notifacation based on Date in cell


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Automatic E-mail, through Excel according to date
    By G.Bregvadze in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-08-2013, 06:20 AM
  2. automatic email notifacation
    By jonnymarsh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-15-2013, 04:47 AM
  3. [SOLVED] Macro to Automatically E-mail Reminders based on date in Cell
    By RichTea88 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-22-2013, 09:10 AM
  4. Automatic mail on due date (30 days or closer)
    By medioman in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-16-2012, 05:32 AM
  5. Automatic e-mail to recipient on given date
    By jad70 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-22-2011, 05:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1