Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    New Lounger
    Join Date
    Dec 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Quick set reminder from excel to outlook

    Hi all,

    I've been having a look through the forum and tried to edit a few complicated calendar reminders but have made a hash of them...

    I was wondering if anyone could give me a hand creating a simple calendar entry which takes the details and simply sets a reminder dependant on the expiry date of certification,

    eg.

    (See attached spreadsheet)
    Reminder set for 2 months before expiry date 12/12/2014
    Forklift : <Cell1>
    Make : <cell2>
    Model : <cell3>
    Serial Number : <cell4>
    Year : <cell5>
    Capacity : <cell6>

    LOLER Expiry date : <cell8>

    Subject just needs to be something like "Forklift recertification required"


    I'm currently using the following code, which works ok but duplicates the reminders every time you execute the macro...

    Sub OutlookReminder()
    With CreateObject("Outlook.Application")
    For j = 2 To 10
    With .CreateItem(1)
    .Subject = Cells(j, 4).Value & " " & Cells(j, 1).Value
    .Start = Cells(j, 8).Value - 60 + TimeValue("09:00")
    .Duration = 60
    .Save
    End With
    Next
    End With
    ActiveWorkbook.Save
    Range("A1").Select
    ActiveSheet.Shapes.Range(Array("CommandButton1")). Select
    Selection.Verb Verb:=xlPrimary
    Range("A1").Select
    ActiveSheet.Shapes.Range(Array("CommandButton1")). Select
    End Sub



    Test macro.xlsm
    Last edited by Phantom_Zone; 2014-12-11 at 09:33.

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Phantom,

    Consider this code where the user selects any cell in the row of a forklift then clicks your "Create Reminder" button. The code will check to see it Outlook is running then creates a reminder to start 60 days prior to the due date (LOLER Expiry). When the reminder is open, it will contain the details of the task. Unlimited number of tasks can be created but the must be created one at a time.

    HTH,
    Maud

    Reminder.png

    Add to a standard module:
    Code:
    Public Sub CreateTasks()
    '----------------------------
    'CREATE AND SET VARIABLES
    Dim OutlookCheck As Object
    Dim num As Long
    '----------------------------
    'TEST IF OUTLOOK IS OPEN
    On Error Resume Next
    Set OutlookCheck = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If OutlookCheck Is Nothing Then
        MsgBox "Outlook is not open, open Outlook and try again"
        Exit Sub
    End If
    '----------------------------
    'CREATE REMINDER USING ADDTOTASKS SUBROUTINE
    num = Selection.Row
    AddToTasks Cells(num, 8), "Forklift recertification required:", 60, num
    End Sub
    
    
    Public Sub AddToTasks(StartDate As String, expiry As String, StartReminder As Integer, Row As Long)
    '---------------------------------------------
    'CREATE AND SET VARIABLES
        Dim dteDate As Date
        Dim olApp As Object
        Dim objTask As Object
    '---------------------------------------------
    'VALIDATE IF STARTDATE IS IN CORRECT FORMAT
        If (Not IsDate(StartDate)) Then
          MsgBox "Please make sure that the dates are in the correct format and" & Chr(13) & _
          "you have selected a cell in the project row you wish to create tasks for."
          GoTo ExitProc
        End If
    '--------------------------------------------
    'CALCULATE TASK REMINDER DATE
        dteDate = CDate(StartDate) - StartReminder
    '--------------------------------------------
    'CREATE TASK ITEM
        On Error Resume Next
          Set olApp = GetObject(, "Outlook.Application")
        On Error GoTo 0
        Job = "Job: " & Cells(Row, 1) & Chr(13) & "Make: " & Cells(Row, 2) & Chr(13) & _
            "Model: " & Cells(Row, 3) & Chr(13) & "Serial Rowber: " & Cells(Row, 4) & Chr(13) & _
            "Year: " & Cells(Row, 5) & Chr(13) & "Driver: " & Cells(Row, 7)
        If Not olApp Is Nothing Then
          Set objTask = olApp.createitem(3)
          With objTask
            .StartDate = dteDate
            .Subject = expiry & " Due Date: " & StartDate
            .reminderset = True
            .Body = Job
            .Save
          End With
        Else
          GoTo ExitProc
        End If
    '------------------------------------------
    'CLEAN-UP
    ExitProc:
        Set olApp = Nothing
        Set objTask = Nothing
    End Sub
    Attached Files Attached Files

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

    Phantom_Zone (2014-12-12)

  4. #3
    New Lounger
    Join Date
    Dec 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Maud,

    This looks great! Could you possibly add a bit to verify if a task has already been to avoid duplicates? Every time you create a reminder it recreates already saved ones!

    Really appreciate the help!

  5. #4
    New Lounger
    Join Date
    Dec 2014
    Posts
    3
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Actually... My bad! I see you click on a cell and then create the reminder for that single item!
    That'll work great maud, many thanks!

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Phantom,

    I added some code to insert the date and time the reminder was created to alert the user.

    reminder2.png

    HTH,
    Maud
    Attached Files Attached Files

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

    Phantom_Zone (2014-12-12)

  8. #6
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Reminder in outlook of excel file

    I am trying to make a reminder in outlook about an excel file regarding to expiry dates..kindly help me in it.. will wait ur kind suggestion.

  9. #7
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I am trying to make a reminder in outlook about an excel file regarding to expiry dates..kindly help me in it.. will wait ur kind suggestion.

  10. #8
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    I would suggest posting a sample file with an explanation of what you are trying to achieve.

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

    khurram6601 (2015-02-18)

  12. #9
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Ok i m sending u the sample ..kindly respond me as soon as possible. i just want to add 3 days before reminder in outlook on this excel file..thanks for ur replying. i just want to ad reminder on expiry date..3 days before
    Date Customer name LG LG amount Project Expiry date Renewel Reminder

  13. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Khurram,

    Are you sending the sample?

  14. #11
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I already sent u the sample.in last msg i wrote all headings regarding to sheet .there is an expiry date column i want reminder on that 3 days before. how i can attach an excel file in the msg?

  15. #12
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Here is the sample

    did u get the sample
    Attached Files Attached Files

  16. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 646 Times in 590 Posts
    Khurram,

    Here is your revised sheet with the reminders that will be created 3 days prior to the expiry date. A date/time will be stamped in column K indicating that the reminder has been created. The Date/Time also serves as a stop mechanism to prevent a duplicate reminders from being created if the code is run again in the future. Below are images of the spreadsheet and the Outlook Reminders

    reminders1.png

    reminders2.png

    Changing any of the columns or rows will require changes to be made in the code as well.

    HTH,
    Maud
    Attached Files Attached Files

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

    khurram6601 (2015-02-21)

  18. #14
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Thanks a lot. It was really gud. Can u plz give me favor..i need to add more than 50 entries... Can u apply this formula at least 50 rows. If possible?

  19. #15
    New Lounger
    Join Date
    Feb 2015
    Posts
    7
    Thanks
    2
    Thanked 0 Times in 0 Posts
    AND I ALSO WANT REMINDER ON COMING DATES. its just showing the reminder on previous dates..is that possible that when the expiry date is near by or before 3 days of expiry reminder should be activated..becoz we can take some action before expiry date. if possible..kindly make a sample for me.

Page 1 of 2 12 LastLast

Posting Permissions

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