Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Set up Outlook reminders from Excel 2013 based on date generated by a formula

    I know just enough about VB code in Excel to be dangerous . I'm much more adept with VB in Access.

    For years I've been used this wonderful function to insert reminders in Outlook from an Excel spreadsheet:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cel As Range
    Dim strEntryID As String
    Dim olkAppt As Object
    Static olkApp As Object
    Const olAppointmentItem As Integer = 1

    Set rng = Intersect(Target, Union(Range("G:G"), Range("G:G")))
    If rng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If olkApp Is Nothing Then Set olkApp = CreateObject("outlook.application")
    For Each cel In rng
    If cel.Row > 1 Then
    Select Case cel.Column
    Case Columns("G").Column
    strEntryID = Range("AA" & cel.Row)
    Case Else
    Application.EnableEvents = True
    Exit Sub
    End Select
    If strEntryID <> "" Then
    On Error Resume Next
    Set olkAppt = olkApp.session.getitemfromid(strEntryID)
    On Error GoTo 0
    End If
    If olkAppt Is Nothing Then Set olkAppt = olkApp.CreateItem(olAppointmentItem)
    End If
    With olkAppt
    .Start = Format(cel + TimeSerial(9, 0, 0), "ddddd hh:mm AM/PM")
    .Duration = 30
    .Subject = Range("A" & cel.Row) & " " & Cells(1, cel.Column)
    .reminderminutesbeforestart = 1
    .reminderset = True
    .Save
    Select Case cel.Column
    Case Columns("G").Column
    Range("AA" & cel.Row) = .entryid

    End Select
    End With
    Set olkAppt = Nothing
    Next
    Application.EnableEvents = True

    End Sub


    This works great when I physically enter the date in the cell. Now I've set up a new spreadsheet where the date I want to insert is generated by a function--=IF(F2="Monthly", EDATE(E2,1), IF(F2 = "Quarterly", EDATE(E2, 3), ""))-- if it makes any difference. My previous code doesn't seem to trigger because the cursor never enters the cell. Is there a way to edit this code to do what I need?

    Many thanks in advance!

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Mari,

    You have presented some code that is interactive with a spreadsheet. Would it be possible to post the file for testing/resolution?

    The code in the Worksheet_Change event should fire with both a direct entry of a value or the change of a value as a result of a formula as long as it is the active sheet. If sheet1 has the Worksheet_Change event code, is sheet1 the active sheet at the time the value on sheet1 is changed by the formula? In other words, entering a value on sheet2 that changes a value on sheet1 does not fire the Worksheet_Change event code for sheet1 because it is not the active sheet. It will however fire any code in the Worksheet_Change for sheet2.

    Maud

  3. #3
    New Lounger
    Join Date
    Dec 2015
    Posts
    2
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hi Maud ... that was my thought, so I'm wondering why it doesn't work. The file is attached. Thanks for looking at it!
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Mari,

    Even though your post displays the code's header correctly, your file actually has the header as:

    Private Sub Change(ByVal Target As Range)

    and not

    Private Sub Worksheet_Change(ByVal Target As Range)

    Mari.png

    After making the correction, your code ran fine.

    HTH,
    Maud

  5. The Following User Says Thank You to Maudibe For This Useful Post:

    Mari_Fec (2015-12-29)

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Mari,

    One suggestion if I may. Whenever I use Excel to send an email, I add code to create a comment in the trigger cell with a timestamp that serves 2 functions. First, as a history to look back if and when an email was sent. Second, as an inhibitor to resend a duplicate message/email/task reminder. A popup message box can be programmed prompting a Y/N response as to whether to send a new email or not. This may be a consideration if applicable I your program.

    Maud

Tags for this Thread

Posting Permissions

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