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.

How to make Excel send email alerts

Discussion in 'Business Applications' started by Carly Nicholson, Nov 28, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. Carly Nicholson

    Carly Nicholson Thread Starter

    Joined:
    Nov 28, 2008
    Messages:
    10
    Hi there,

    I am looking for a way that Excel can automatically generate an email alert for my colleagues that is triggered by data in my Excel file. I haven't generated the Excel file yet as the advice you give me may have an impact on how I go about it. Basically, the database will be a record of marketing activity we have undertaken as a company and will include dates for us to complete follow up actions. If possible, I would like for an email to be generated when todays date matches up with the follow up date. This should go to the staff member whose details are against that entry.

    I hope this makes sense!

    I have seen a previous thread which appeared to be on the right tracks, but it has been closed so I can't see the outcome!

    Many thanks,

    Carly.
     
  2. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Hi, Carly. Questions:
    1. What will trigger the mail to be sent? Will the file always be open and Excel needs to be on some kind of timer? Or do you want to, on open of the file, check the data, and run the macro?

    2. Will you have your list of email addresses in a) a column, b) all in one cell, c) other (explain)?

    3. What is your default email client? (When you open an Excel file and hit File-->Send to... which email program opens?)
     
  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Carly & Anne, there was an exactly the same Thread within the last year, I think it was posted by computerman, I will see if I can find it.
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
  5. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Well, you know me, OBP. I'm not about to start coding, but I know what information the coders need. :)

    Happy Holidays to you!
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,836
    Anne and you, I hope you keeping well?
     
  7. Anne Troy

    Anne Troy

    Joined:
    Feb 14, 1999
    Messages:
    11,746
    First Name:
    Anne
    Absolutely. :)
     
  8. Carly Nicholson

    Carly Nicholson Thread Starter

    Joined:
    Nov 28, 2008
    Messages:
    10
    Hi Anne,
    In answer to your questions:
    - The mail should be sent when the date in the cell matches today's date. The Excel file won't be open all the time, though it will probably be opened once daily. Therfore, I'm guessing that the best option is to run the macro when the file is opened. The email should (ideally) give a message along the lines of "Please follow up on your sales/marketing lead today as stated in the attached Excel file".

    -
    As I haven't put the Excel file together yet I can easily include the relevant email addresses wherever necessary. Whatever is easier.

    -
    I am using Outlook and my OS is Windows XP 2002.

    Thank you.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    See attached. Includes a dynamic named range "JobNums" which is defined by the number of entries in column A.

    Then there's some code in the workbook module:

    Private Sub Workbook_Open()
    For Each Cell In Range("JobNums")
    If Cell.Offset(, 1) = Date And Cell.Offset(, 2) <> "Yes" Then
    MsgBox "Job " & Cell & " needs an email."
    Cell.Offset(, 2) = "Yes"
    End If
    Next Cell
    End Sub


    So the basic syntax is ... check every cell in "JobNums"; if the date to the right = "today" and the value 2 to the right <> "Yes" then do something.

    So far "something" is just (a) display a message that "Job n needs an email." (b) update value 2 to the right to "Yes".

    To go any further we need to know the actual layout of your data.
     

    Attached Files:

  10. Carly Nicholson

    Carly Nicholson Thread Starter

    Joined:
    Nov 28, 2008
    Messages:
    10
    Thanks bomb #21, however I was ideally looking for the Excel file to generate an email to the respective staff member, not a pop up message. I have attached a file which may help to show more clearly what the data will look like. As you can see, the staff details are in column A and B and the follow up date is in the last column (H). I am looking for an email to be sent to the staff member when the follow up date equals today&#8217;s date. Can this be done?

    Please note, this is a draft version of the Excel file, therefore I can change it if required.

    Thank you!
     

    Attached Files:

  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Edit the address you posted to something non-functional ASAP or you'll get loads of spam.
     
  12. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Yup, appreciate that ; but it was just an example. :)

    So, if (e.g.) Carly has 4 follow-ups today, should there be 1 email or 4 emails?
     
  13. Carly Nicholson

    Carly Nicholson Thread Starter

    Joined:
    Nov 28, 2008
    Messages:
    10
    Hi,

    I know it was just an example - sorry if I sounded ungrateful! :)

    I would like for the email to be generated once only please.

    Thanks.
     
  14. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Sorry for the delay, connection problems from hell today.

    Your Sheet1 will need an extra field (Email sent?) to mark off when emails are sent. Without that, emails will be sent every time the file is opened which could be > once a day, which will just end up being confusing/annoying.

    See the Sheet3 table. It needs to include all staff. The formulas in column B calculate number of follow-ups "today" where "Email sent?" is blank per person.

    Sheet2 is blank ... for now.

    Select Sheet1, then run Macro1. Note the additional 1s in column I.

    Switch to Sheet2. The "records" due "today" where "Email sent?" was blank have been copied over (except cols H:I not required).

    From there, we can use code by Ron de Bruin to do the actual mailing. This one could be tweaked to mail a copy of Sheet2 to each person, but that brings up two more questions:

    1. Can each "mailee" receive a list of all due follow-ups, or must he/she only see his/her own?

    2. What's the mail client?
     

    Attached Files:

  15. Carly Nicholson

    Carly Nicholson Thread Starter

    Joined:
    Nov 28, 2008
    Messages:
    10
    This is looking good! In answer to your questions:

    - Ideally I would like for the recipient to only see their own follow ups, but if this proves very difficult, I would be happy for them to see others as well.

    - Microsoft Office Outlook.

    Thank you.
     
  16. 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/773873

  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