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: Send an automated email (outlook) from Excel spreadsheet dependent upon comle

Discussion in 'Business Applications' started by ColinStewar, Oct 30, 2013.

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

    ColinStewar Thread Starter

    Joined:
    Oct 30, 2013
    Messages:
    5
    Hi,

    Im quite new to this excel programming thing and could really do with some help. :(

    I need to send an automated email to 3 recipients (always the same 3 email addresses) when a number (formatted from a countdown of days to go) is 10 or less. Also i need a different automated email to be sent when a date is manually entered into a different cell.

    I have managed to get the current date and time on my spreadsheet and used the format to work out the days to go to the deadline.

    I have looked over all different types of forums but unfortunately because i'm still very green when it comes to excel i get lost and confused when trying to do this.

    Is there anyone out there who can treat me as an alien and help me through this step by step.???
     
  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Colin


    You'll need to attach your spreadsheet (with personal information dummied up) highlighting the areas that will trigger the emails
    Also needed is to know the contents you want for the email - what information from the spreadsheet do you want to include in the email and in what format
    Finally, what is the sequence of events e.g. do you want to send an email every day after the cut-off point; do you want to record if and when an email has been sent etc.


    It's a relatively easy thing to do but you'll need to get a good plan for how you want all this will work and then we can certainly help you out.
     
  3. ColinStewar

    ColinStewar Thread Starter

    Joined:
    Oct 30, 2013
    Messages:
    5
    Hi XCubed

    Thank you for the prompt reply and you offer of help!!

    Are there some contact details that i can reach you on?? Cannot work out how to attach a file to this thread!!

    look forward to your reply
     
  4. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    If you click on the big "REPLY" button just below this post you'll see in the menu bar above the input area the Paper Clip icon that will allow you to upload a file. Give it a try.
     
  5. ColinStewar

    ColinStewar Thread Starter

    Joined:
    Oct 30, 2013
    Messages:
    5
    HI XCubed,

    Please see attached spreadsheet (Checklist1) that i need the help on...

    This is what i need:

    1st Email:

    Column D from rows 9-51 have been traffic light colour coded on the following countdown. 21 days plus in green, 11-20 days to go in orange, 1 -10 days to go in red. These work from the Deadline dates in column C against todays date in L4. i have filled in the first 3 deadline dates to show the days to go operating.

    What we require is an email to be sent to [email protected] and [email protected], when those rows in column D hit red, i.e.the days to go number is 10.

    The email Subject is to read - URGENT Deadline Date Approaching - Checklist 1
    The body of the text is to read - Please address XXX deadline date on Checklist 1
    The XXX is to represent the description item in the relevant row in Column B. i.e if cell D11 shows 10 days to go, then the XXX in the body of the text should read- bananas. If this cannot be done or is too much to ask then just take out the XXX reference to ColumnB.
    I will be looking to use the same spreadsheet layout for different clients. Would it be possible for the subject and also the body of text in the email to automatically pick up the workbook name (i.e Checklist1, Checklist 2 and so on??) or would we have to manually insert the workbook name in the macro for each spreadsheet??

    2nd Email:

    The other email is based upon Column F rows 9-51. when we manually fill in the date in these rows in column F we require an email to be sent to the same 2 email addresses, [email protected] and [email protected]

    The email Subject is to read - URGENT Submission Confirmed on Checklist 1
    The body of the text is to read - Please note XXX has been confirmed on Checklist 1
    As on Email 1, the XXX is to represent the description item in the relevant row in Column B. i.e if cell F11 shows a date, then the XXX in the body of the text should read- bananas. Again If this cannot be done or is too much to ask then just take out the XXX reference to ColumnB.
    Again, i will be using the said spreadsheet layout for different clients. So again would it be possible for the body of text to automatically pick up the work book name (i.e Checklist1, Checklist 2 and so on??) or would we have to manually insert the workbook name in the macro for each spreadsheet??

    Ideally, if the date in column F has been completed prior to the columnD being red (10 days to go), then i do not need Email 1 to be sent.

    The emails only need to be sent once.

    On Email 2, would / could this work without Outlook being opened? i.e if the spreadsheet was on dropbox and someone else opened it and inserted the confirmed date in column F would / could the email still be sent??

    Hopefully the above makes sense and is something that you can help out on.

    I appreciate that i am asking a lot and it may well be too challenging or not possible to do all of the requests above, but anything that you could help on would be truly appreciated!! :)
     

    Attached Files:

  6. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Colin


    I think I have everything you asked for except more detail on how the emails will be triggered.


    For the first email I have set it up so that the email will be sent when the file is opened.
    The second email will be sent automatically if a date is entered in column F. I've put in a message so that you can cancel the email if an error has been made


    Both of these can be a little risky if 1. you don't close the file or 2. you are not the only user of the file as this quote would indicate...

    There may be ways around this but, for sure, if there are multiple users of the file the exercise becomes much more complex so you'll need to explain the number of users and what triggers you want for the emails. As to your (quoted) question - the email could probably still be sent but from the mailbox of the user changing the date. I suspect this is not what you want.


    Give this a try and see how it suits and what changes you may want.
     

    Attached Files:

  7. ColinStewar

    ColinStewar Thread Starter

    Joined:
    Oct 30, 2013
    Messages:
    5
    HI XCubed...

    thats great... thank you so much!!!

    one question i have.. when i fill in the date in Column F, how can i change the default email addresses??i.e. the [email protected] to an actual live emaill address??

    thanks in advance
     
  8. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    Hi Colin


    The email addresses are hardcoded into the macro so you'd have to edit the macro. If you want you can enter the addresses in a cell and the macro can pick that up. This approach would be advisable if the addresses change (which they will sooner or later).


    Let me know if you want to do this and which cell will contain the addresses and I'll make the changes.
     
  9. ColinStewar

    ColinStewar Thread Starter

    Joined:
    Oct 30, 2013
    Messages:
    5
    hi XCubed,

    I managed to work out where to change the addresses late last night, but thank you for your offer.

    Once again, I really appreciate all of your help on this, in my mind you are a genius!!

    All the best

    C.
     
  10. 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/1111816

  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