Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    trigger Email from Excel (2003)

    Lounger,

    Just toying with an idea to trigger an email reminder from a calculation within a spread sheet. The calculation indicates when a activity has "One Month Left" before it expires and then "Expired" when it has actually expired - this is based on a date and how long the activity is valid. But before I look into this further i needed to know if it is possible.

    Any thoughts (I hope this makes sence)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trigger Email from Excel (2003)

    Would you want the e-mail to be sent the moment the cell value changes? If so:
    - Will the workbook be open 24 hours a day, 7 days a week?
    - Will the workbook be recalculated continually?
    Or would you want the e-mail to be sent when the workbook is opened?
    Or something else?

  3. #3
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: trigger Email from Excel (2003)

    Thanks for the reply Hans,
    The workbook would be provided to numerous people, so a bit hard to say, however I would suspect most people would open the workbook daily so it would generate an email when the workbook is opened or if possible if would be good if there was be away to combine two options when recalculated and/or when opened.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: trigger Email from Excel (2003)

    If some manual intervention is OK, you can put code in the Workbook_Open event in the ThisWorkbook module. The following code assumes that B3 contains "One month left" if an e-mail has to be sent, that B4 is originally blank - it will be filled with "Mail sent" after the e-mail has been generated - and that B5 contains the e-mail address of the recipient.

    Private Sub Workbook_Open()
    If Worksheets("Sheet1").Range("B3") = "One month left" Then
    If Worksheets("Sheet1").Range("B4") = "" Then
    FollowHyperlink "mailto:" & Worksheets("Sheet1").Range("B5")
    Worksheets("Sheet1").Range("B4") = "Mail Sent"
    End If
    End If
    End Sub

    If you want the mail to be sent automatically, you would need to automate Outlook (if that is your e-mail client).

  5. #5
    4 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    487
    Thanks
    75
    Thanked 2 Times in 1 Post

    Re: trigger Email from Excel (2003)

    Thanks once more Hans,

    Will give it a go

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •