+ Reply to Thread
Results 1 to 17 of 17

email to notify or display a list of discrepancies / expirations

  1. #1
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Post email to notify or display a list of discrepancies / expirations

    Hi, what i'm looking for is very similar to this thread: http://www.excelforum.com/excel-prog...piry-date.html

    EDIT: my latest code and spreadsheet are on post #9

    Basically, we keep a spreadsheet of our contractors/subcontractors on file so that we can make sure that we aren't contracting with someone that either 1. doesn't have a business license, or 2. lets their insurance / business license expire.

    i've attached a 'dumbed-down' version of my spreadsheet with actual expiration dates, my question is;

    to remain compliant, we need to make sure that all of our contractors'/subcontractors' insurance/business licenses are current... what I'm thinking is a set of 'buttons' to run the report for a couple different things, OR 1 button to do everything. Basically, if a discrepancy is found (which would be determined if today's date is within X amount or past the date in a given cell), it would either display that information on a [separate] sheet or shoot an email to the master contractor or an email specified in a cell.. now these spreadsheets are always changing, we lose/gain new contractors constantly..

    the email or list of discrepancies would have to include the entire row and possibly the header row so that the person viewing it would be able to see what the expiration dates are for..

    is this possible? the reason i'm working on this is because a friend of mine is responsible for notifying the appropriate people of upcoming expirations and lately, she hasn't been able to keep up so I want to make sure her job is secure..
    Attached Files Attached Files
    Last edited by mrmarchuk; 02-10-2012 at 07:14 PM.

  2. #2
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    Thanks to Paul in this thread, So far, I've been able to take the code he wrote and managed to change some of it to kind of suit my needs:
    Please Login or Register  to view this content.
    this basically adds buttons in each column and shoots an email every time it finds a discrepancy in that given column. is there a way to get it to search and find ALL the expired/expiring dates on the spreadsheet and shoot off an email containing the entire row for the discrepancy? also, since my workbooks have multiple sheets, is it possible to get a button to search multiple sheets?

    i.e.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    so i know i can put in each separate cell via
    Please Login or Register  to view this content.
    but is there a way to just include the entire row?
    Last edited by mrmarchuk; 02-08-2012 at 02:22 PM.

  4. #4
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    so instead of including the entire row, i just changed it to:

    Please Login or Register  to view this content.
    to just grab the bits i need, so no need to answer that question any more.. however, i still have no answer as to if i could just have 1 button run all the commands that i currently have 4 buttons doing?

    edit: also, since i have multiple sheets per excel file, is it possible to have a button for each sheet to "SEND ALL DISCREPANCY EMAILS" from the first sheet in the excel file?

    all help is appreciated, i'm sure i will eventually get it by myself but it doesnt mean i cant ask for help, right? thanks.
    Last edited by mrmarchuk; 02-08-2012 at 01:19 PM.

  5. #5
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: email to notify or display a list of discrepancies / expirations

    Hi mrmarchuk
    Been following your Thread...you seem to be making significant progress. If you'd like personalized help, please post your file (proprietary info modified) along with your existing code and an explanation of what you'd like to happen.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  6. #6
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    Quote Originally Posted by jaslake View Post
    Hi mrmarchuk
    Been following your Thread...you seem to be making significant progress. If you'd like personalized help, please post your file (proprietary info modified) along with your existing code and an explanation of what you'd like to happen.
    i'm still trying to see what i can get worked out, since i left my computer at work but I posted my original file in my Original post, i have since, updated it and will post a newer file with what else i'm trying to do thank you, for a while there, i thought noone was going to reply :P lol

  7. #7
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Red face Re: email to notify or display a list of discrepancies / expirations

    For now, i have the code as such:

    Please Login or Register  to view this content.
    The problem is, We keep track of 2200 contractors (you'd think the company would have thought of this by now, huh?)

    anyways, we have 4 such excel files, each containing an average of 5 sheets with an average of 110 contractors per sheet.. what i have managed to piece together so far Saves ALOT of time but if i could get it to where i can just open up the spreadsheet and on the first sheet, have the button/s to run the commands, that would save even more time! now, i'm pretty sure 1 button per sheet is possible but is it possible to link each sheet to 1 "MASTER BUTTON"? if not, can someone help me compile 1 button to do what currently, my 4 buttons do?
    Last edited by mrmarchuk; 02-10-2012 at 02:17 PM.

  8. #8
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: email to notify or display a list of discrepancies / expirations

    Hi mrmarchuk

    So, as I understand it, you'd like to send emails based on Insurance Expiration Date, Cargo Insurance Expiration Date, ENOL Insurance and Business License Expiration Date for each sheet in the workbook (perhaps 5 or more worksheets) with the click of one button. Correct so far?

    If correct, what about the other workbooks? You want the same capability? Or do you want one button that'll cycle through ALL the worksheets in ALL the workbooks and send ALL the emails for ALL worksheets in ALL workbooks?

  9. #9
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    Quote Originally Posted by jaslake View Post
    Hi mrmarchuk

    So, as I understand it, you'd like to send emails based on Insurance Expiration Date, Cargo Insurance Expiration Date, ENOL Insurance and Business License Expiration Date for each sheet in the workbook (perhaps 5 or more worksheets) with the click of one button. Correct so far?

    If correct, what about the other workbooks? You want the same capability? Or do you want one button that'll cycle through ALL the worksheets in ALL the workbooks and send ALL the emails for ALL worksheets in ALL workbooks?
    if it's possible to get one button to cycle through all the workbooks, that would be amazing, however, 1 button in each workbook cycling through all the worksheets is what i was originally going for


    how it works is 1 person updates all of the workbooks (1 workbook=1 region, each worksheet is a separate office within the region) each sheet has it's own admin who's supposed to keep track of the expiration's, but almost no-one does, which is why i wanted this email function.. Basically, if i could get 1 button per worksheet. i realize i havent done this until now but i've constructed a spreadsheet more-or-less how it looks for us and attached it to this post:

    newest workbook as of 2/9/11, now that i have it in sheets, it gives me a runtime error ('-2147467259 (80004005)) and says there must be at least one name or distribution list int he to, CC, or BCC box.. but it's weird, because i HAVE the email addresses in the cells, and it sends the email out after that error pops up, the only difference is, now it doesnt ASK Outlook for permission... :
    Please Login or Register  to view this content.
    more or less the same thing, just with the main page how and with the worksheets (facilities) there as well.. 1 button on each "SUMMARY" page would actually probably be best, that way if another facility is added, that can be added to the code, and will keep it much simpler, in my opinion

    thanks again for your help Jaslake

    edit: even if 1 button were to 'push' the existing buttons, that would be alright that might actually even be better since if there's an error with 1 button, the others will still more-or-less work
    Last edited by mrmarchuk; 02-10-2012 at 02:19 PM.

  10. #10
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: email to notify or display a list of discrepancies / expirations

    Hi mrmarchuk
    Well, this is good
    now it doesn't ASK Outlook for permission
    It's my experience that doesn't happen in Excel/Outlook 2007. You're profile indicates Excel 2003 yet you uploaded an .xlsm file. So, what version(s) are we developing for?

    Please don't shout...it's distracting.
    Last edited by jaslake; 02-10-2012 at 12:18 AM.

  11. #11
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    sorry I didn't set it right when i set up my profile.. I'm using office 2007

    edit: updated the workbook in post #9, removed my email address :P
    Last edited by mrmarchuk; 02-10-2012 at 02:24 PM.

  12. #12
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: email to notify or display a list of discrepancies / expirations

    Hi Sergey
    I've added a UserForm and a Button to Summary sheet that will allow you to select 1 or more Facilities to process all Command Buttons of 1 or all Facilities.

    You'll need to change some things in each worksheet (I've already done so for Facility1 and Facility2).
    You need to change the CommandButton_Click event from Private to Public for each CommandButton_Click event (look at the code for Facility1 and you'll see what I mean).
    Also, to eliminate the runtime error, you need to redirect this line of code in all CommandButton_Click events
    Please Login or Register  to view this content.
    In Facility1 and Facility2, I've arbitrarily changed it to
    Please Login or Register  to view this content.
    You have data in Column B below the range of cells being processed...ergo, the runtime error.

    Let me know of issues.
    Attached Files Attached Files
    Last edited by jaslake; 02-10-2012 at 04:45 PM. Reason: Upload File w/o email per OP request

  13. #13
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    it works like a charm can you do me a favor? in the attachment you posted, please remove the emails :P someone's bound to download it and hit the button to test it.. lol.

  14. #14
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: email to notify or display a list of discrepancies / expirations

    Hi Sergey
    Done, as you requested
    in the attachment you posted, please remove the emails
    If that satisfies your need, I'd appreciate it if you'll please mark your thread as "Solved".

    To mark your thread solved do the following:
    - Go to your first post on the thread
    - Click edit
    - Click Advance
    - Just below the word "Title:" you will see a dropdown with the word No prefix.
    - Change to Solved
    - Click Save

  15. #15
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    done. thanks again

  16. #16
    Forum Guru jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: email to notify or display a list of discrepancies / expirations

    Hi Sergey
    You're welcome...glad I could help.

  17. #17
    Registered User
    Join Date
    02-07-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2007
    Posts
    49

    Re: email to notify or display a list of discrepancies / expirations

    helP? you did most of the work :P thank you again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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