+ Reply to Thread
Results 1 to 13 of 13

Auto e-mail reminder to my personal Outlook from excel SS

  1. #1
    Registered User
    Join Date
    05-23-2017
    Location
    Cleveland, Ohio
    MS-Off Ver
    Windows 7 Enterprise
    Posts
    5

    Auto e-mail reminder to my personal Outlook from excel SS

    I have commercial buildings I've visited that have various fire equipment devices. I would like to attend their annual testing. I need reminders 30 days in advance to schedule. All the info is in SS.

    I have a large SS that I do not want to sift through to get names of accounts and approx annual test dates. Some accounts are noted to not have any fire protection equipment.

    This is the first time I'm using this forum Hope it works.

    ~Mike
    Attached Files Attached Files

  2. #2
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Hi ArchAngel,

    A few questions...

    1. When you say "reminder" do you mean the workbook should send you an email?
    2. If the Fire Supression System is "none" should these be skipped?
    3. Should the code run whenever the workbook opens? Or is is always open and you want it to run on some kind of schedule?
    4. I assume you only need one reminder if the next test date falls within 30 days, and you don't want constant emails between 30 days and 0... also do you want any kind of past due notification? If you only want 1 reminder, can we add a column that we use to mark the notification email as sent?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  3. #3
    Registered User
    Join Date
    05-23-2017
    Location
    Cleveland, Ohio
    MS-Off Ver
    Windows 7 Enterprise
    Posts
    5

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Wow, I got a response. Amazing!

    Greetings.

    I hope that made sense. I keep a running track of each account, what I did/activity, when I did it, and other info. I just want a notification to myself via e-mail or SS notification that I need to get ball rolling on reaching out to people for scheduling this. I don't want an auto canned text and e-mail to accounts. i'll do that individually. Just notification to myself.

    #1a - If I could get an e-mail that would be great. really just some reminder so I can get ball rolling on scheduling. E-mail or SS pop-up or reminder.

    #2a - If none then yes, skip it. Some accounts have no sprinkler system, pump, etc. I guess I could just leave this cell blank.

    #3a - If it could run in the morning when I open the SS that would be grand.
    #3b - I always have this opened during day, I open SS in morning then close at night. I think run it once a day would be enough when I open it.

    #4a - Only one reminder is fine. No constant reminders. Just one would be good.
    #4b - Past due notification would be nice, but I don't think really necessary. Say I'm gone for 4-5 days then come into office, open SS and get all reminders for the past few days I've been gone, that would be fine.
    #4c - No need to add column.

    Not sure if there is an easy way to do this.

    thanks!

    ~Mike

  4. #4
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Hi again.... Sorry but this leads to a couple of more questions...
    You had outlook in the subject so I assumed you want emails, which is easy enough to do.... But just a popup mrssage when the workbook opens is even easier i guess... Either is fine just tell me what you prefer. Also, rmail or workbook message, do you want 1 per site, or just one summarized one?
    You also said no need for extra column, but we do need a way to mark each row as already dealt with, to know not to send notifications next time the code runs, otherwise you would get a message again for any site due in the next 30 days.

  5. #5
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    P.s. sorry for all the questions but it is to ensure you get what you want/need. And dont worry, this wont be hard to do once we nail down the details.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    As I work on some code, I realized that you have the same account and fire supression system listed multiple times for different Activities.... should the program ignore repeats of the same account? If so, does it matter which suppression system, or is it truly just one per account?

  7. #7
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Ok I made some assumptions:
    Only one email per account even if there are multiple activities, and I ended up using outlook.

    There are some items to edit in the code (I made red what you need to update), I assumed your columns in the sample match the actual sheet with real data, so keep those in mind. I did have to use column F to mark the reminder as sent to avoid you getting the same reminders day after day.
    You have to update the code with your real email address, and there is a ' (which comments out lines) in front of .Send that will need removing once you are satisfied that it works as desired.
    The sub is called auto_open, which will make it run whenever you open the workbook. You could also put the code in the workbook_open event instead if you prefer.

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    05-23-2017
    Location
    Cleveland, Ohio
    MS-Off Ver
    Windows 7 Enterprise
    Posts
    5

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Yea, I had it in mind to get e-mail but if it’s easier to get pop message that would be fine. I kind of prefer popup in excel now that I think or it. I should change subject, oh well.

    One per site. Workbook message seems better as mentioned.

    Extra colum is ok if it means not getting message over and over.

    Different accounts have same fire suppression device. Most accounts have building fire pumps and water sprinkler systems. But other accounts can have dry chem suppression systems or foam systems.

    It’s one per account. Again, This large sheet is basically a daily running journal of my activity and what accounts I work on and what type of fire suppression system they have, if any. So yes ignore repeats, I think. I simply want to know down the road/in future via popup what “account” is coming due for their annual “fire pump Test” or “Dry Chem test” or whatever it is I have in that column labeled “Next Approx Annual Test date”.

    This spreadsheet keeps getting loinger and longer as days go by and I document my activity for that account. If you filter out the dates from oldest to newst, you’ll see I work on different accounts on diferent days.

    So for account XYZ Comp, their next annual testing is in 7/20. I want to get a popup notifying me (around 6/20) that their annual testing is coming up and I have to schedule.

    When I e-mail a person complicated stuff like this I might just call them. Where are you located? (You don’t have to answer that if you don’t want to)

    ~Mike

  9. #9
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Hi Mike,
    I believe the code in my last post meets the requirements, give it a try without activating the .send line until you are happy with the result.... I did one email with that day's notifications, but can easily make them individual emails if you prefer that.
    I live about 1 hour outside of Ottawa, Ontario... In Canada, though I work in Ottawa.

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Looking more closely, it seems you might prefer a popup in the SS... if that is correct I'll change the code, I'd say the nice thing about an email is that it sticks around longer than just hitting "ok" on the message, so that you can refer to it until you have scheduled them all, and you can print it as well, which a popup won't do.
    Let me know and we shall take it from there

  11. #11
    Registered User
    Join Date
    05-23-2017
    Location
    Cleveland, Ohio
    MS-Off Ver
    Windows 7 Enterprise
    Posts
    5

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    That's true as well. E-mail can stay around longer then pop-up window.

  12. #12
    Registered User
    Join Date
    05-23-2017
    Location
    Cleveland, Ohio
    MS-Off Ver
    Windows 7 Enterprise
    Posts
    5

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Thanks so much!
    I'll let you know how it turns out. I probably won't get to it until next week though.
    This is a great forum. I use excel for a lot of things so I'm sure I'll be back here quite often.

    Have a good day.

  13. #13
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 365
    Posts
    5,057

    Re: Auto e-mail reminder to my personal Outlook from excel SS

    Yep, it is indeed a great forum, lots of expertise in different areas, I tend to stay mostly in the VBA section, and am a rookie compared to some of the guys, but we have a lot of fun helping others. Hope it works out, and I'll be happy to fine-tune once you have had a chance to test it out.

    If you are not back until next week, have a great weekend.

+ 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. [SOLVED] Automatic Outlook mail due date reminder based on Excel file
    By iamitp in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 08-18-2019, 05:51 PM
  2. [SOLVED] Auto Mail reminder from Outlook
    By Parth007 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-21-2016, 03:04 AM
  3. Reminder mail from excel to outlook
    By burc67 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2016, 09:19 AM
  4. excel reminder mail to outlook
    By burc67 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-03-2016, 08:30 AM
  5. Auto Reminder mail from Excel when the PC is powered on or signed in
    By Pramal11 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 02-21-2015, 12:55 PM
  6. Further study on Auto-mail for due date reminder
    By samd007 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-16-2013, 11:43 PM
  7. Send reminder mail from excel using outlook
    By arunsny in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-26-2011, 09:22 PM

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