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: Excel automatic email

Discussion in 'Business Applications' started by mrbluecharge, Jun 26, 2012.

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

    mrbluecharge Thread Starter

    Joined:
    Jun 26, 2012
    Messages:
    7
    Hi Everyone,
    my name is steve and this is my first post. I have been asked to compile a system to recall calibrated items within the company i work for, I must stress tht my expierience with excel is very limited.
    Attached is the system so far (It is the work of many butchered examples off the net, and several people who know a little at work).
    I am trying to achieve a solution that when an item comes within three months of its expiry date the date column turns yellow, when 30 days or uner it turns red (Possibly gets transfere to another sheet)and then emails a short mail incluing cells B,C,D an L of that row to the "Area Owner" . I'm using office 2007, anyone think this is possible? or better still help me with this problem.
    I would appreciate it so very much!

    By the way, the password is ; Kalibratedbyme (capital K)
    Regards,
    Steve
     

    Attached Files:

  2. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,055
    Welcome to TSG mrBlueCharge. I'm not real smoothe in excel and could kludge something together with a vbscript. someone else could server you better. Seems I've seen a similar solution here before.

    But (the reason for my post) your spreadsheet is password protected :(
    Good luck
     
  3. mrbluecharge

    mrbluecharge Thread Starter

    Joined:
    Jun 26, 2012
    Messages:
    7
    Sorry all the password is. kalibratedbyme (capital K)

    Thanks again
     
  4. mrbluecharge

    mrbluecharge Thread Starter

    Joined:
    Jun 26, 2012
    Messages:
    7
    Any solution would be fantastic draceplace thank you!
     
  5. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,055
  6. mrbluecharge

    mrbluecharge Thread Starter

    Joined:
    Jun 26, 2012
    Messages:
    7
    Hi draceplace,

    I have just tried the solution you gave, i finally got it o work, but it is not really suitable as all my cells will constantly filter by date leaving the macro button not alligned with its specific row, if you could look again sometime i would be very grateful

    PS the password Kalibratedbyme strats with a capital "K"

    Regards,

    Steve
     
  7. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,055
    Just saying this a place to start, the macro generates an email and reads cells. You would need to alter it to read each line of the worksheet and look at the Calibration due days (the capital K worked!). When greater than xx then yellow, When greater than 90 then red and email.

    You might pull that code and start a new thread or read some of the other threads and see if one is closer to what your looking for. This has been done before you just have to find it and tweek it a little.
     
  8. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,055
    I think you'll like this. Unzip and put Drop_XL_SYS_CAL_Email01.vbs in the same folder as your excel stuff. Then drag and drop your excel spreadsheet on top of it and it should process for emails.

    Disclaimer - this stuff may blow everything up. Use at your on risk. I have office 2010 and window7 but this should work for you
     

    Attached Files:

  9. mrbluecharge

    mrbluecharge Thread Starter

    Joined:
    Jun 26, 2012
    Messages:
    7
    I'll get right onto and try it, as soon as I have any result I'll let you know, thanks again for your time, I'm at my witts end!!!
     
  10. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,055
    Any luck with this lastest code?
     
  11. mrbluecharge

    mrbluecharge Thread Starter

    Joined:
    Jun 26, 2012
    Messages:
    7
    The code works brilliantly, I really cant believe how someone is willing to spend their own time and effort to help someone else.... All i can say is thank you so much this really does show there are good people out there.

    Regards,
    Steve.
     
  12. draceplace

    draceplace

    Joined:
    Jun 8, 2001
    Messages:
    2,055
    Glad it works for you, I modified an 'emailer' I had written for my own use to match up to your spreadsheet.

    Depending on your user security settings and how your email is configured you may be able to change the line

    MyItem.Display to MyItem.Send and the email will go out immeadiatly. I find it helpful to review the emails before they go out.
     
  13. anishmathw

    anishmathw

    Joined:
    Jul 19, 2012
    Messages:
    3
  14. 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/1058647