Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jun 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using excel to create task reminders in outlook

    Hi all
    I've been trying to adapt macros in the forum for this but have failed by myself.

    I've got a spreadsheet in Excel 2010 which is updated most days. When a new entry is put in I was trying to use vba to create a reminder in outlook.
    The information needed is in column B (subject), column A (body) and for it to be the working day before the expected date in column E
    The existing information is stored in this sheet as a record.

    There are multiple worksheets, some (or occasionally all) are updated daily.
    If anyone can help that would be great.

    EDIT: this spreadsheet is on a shared drive and there are 3 people who will edit it. It's fine if all reminders go to one person but I wouldn't want to duplicate it to all 3!
    Attached Files Attached Files
    Last edited by freightgeorge; 2015-06-09 at 10:13.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Perhaps the below can link can provide assistanc

    http://www.jpsoftwaretech.com/using-...rs-in-outlook/
    Also from the site
    About JP
    I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there.

    TD

  3. #3
    New Lounger
    Join Date
    Jun 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried that but didn't have much luck. have spent some time going through tutorials the last couple of days though so I'll give it another go. Thanks

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    George,

    Using a class that I wrote for task reminders, here is your modified workbook that will create reminders set for the day prior to the date in column E and due on the date in column E. The subject and body are extracted from the columns you specified. The code will prompt you to open Outlook if closed. The code will cycle through the rows and once a reminder has been created, an "X" is placed in column G to inform you and the code not to create a duplicate task reminder. Note: Column E must be formatted as a date

    HTH,
    Maud

    FreightGeorge1.png

    FreightGeorge2.png

    Code:
    Public Sub SetReminders()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim Task As ClsTaskEmail
    Set Task = New ClsTaskEmail
    '--------------------------------
    'SETUP TASK
    If Task.OutlookCheck = False Then Exit Sub
    LastRow = ActiveSheet.Cells(Rows.Count, 5).End(xlUp).Row
    For I = 3 To LastRow
        If Cells(I, 7) <> "X" Then
            Task.TaskStartDate = Cells(I, 5)
            Task.TaskSubject = Cells(I, 2)
            Task.TaskBody = Cells(I, 1)
            Task.TaskReminderset = True
            Task.TaskReminderDate = Cells(I, 5) - 1
            Task.TaskImportance = 2
            Task.TaskCreate
        End If
        Cells(I, 7) = "X"
    Next I
    End Sub
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Jun 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you, that is really helpful of you.

    I've just got a couple of questions on that attachment.
    I'm actually in the UK so I've changed the date to our format (dd/mm/yy) and as that doesn't display time of arrival I've put that in a separate column beside it. Just trying to avoid the error I think would happen with input.

    The X was actually coming up in column F, in the file so I've put Cells (I, 8) = "X" to shift it over and account for my additional column



    When I've tested it with multiple lines though it is only picks up on the last entry. Is it possible to get it to add multiple reminders if had say 4 shipments or should I just run the macro after each entry?
    Attached Files Attached Files

  6. #6
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,640
    Thanks
    115
    Thanked 651 Times in 593 Posts
    George,

    I revised the workbook so that it will create multiple reminders when the code runs. If you want the reminder to be created a second time, delete the "X". Also, column E is formatted as dd/mm/yyyy hh:mm so the column with the time alone has been removed.

    Let me know if there needs to be anymore adjustments.

    Maud
    Attached Files Attached Files

  7. #7
    New Lounger
    Join Date
    Jun 2015
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Awesome, many thanks

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
  •