+ Reply to Thread
Results 1 to 26 of 26

How set a reminder in outlook with VBA Excel

  1. #1
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    How set a reminder in outlook with VBA Excel

    can someone tell me if this is even possible?

    On a work sheet i have a range of B:1-D:4 looks like the following.
    Quote # Customer Date
    1234 test1 11/5/2017
    5678 test2 11/10/2017
    9012 test3 11/16/2017

    when this is entered, I would like to set a reminder in outlook two weeks after the date in column D 2, D 3, D 4.
    Is this possible and if yes, then how?

  2. #2
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    Outlook has a method for that. I like to set a field/column value to show that it processed.

    e.g.
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    Kenneth,
    Thanks for the reply.
    I am new to outlook and not much more advanced in VBA. Where can I find information on how to do this?
    Thanks

  4. #4
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    Ron de Bruin has several examples. The 3 methods for a Body are Body, HTMLBody, and WordEditor.

    https://www.rondebruin.nl/win/s1/outlook/bmail2.htm

    If you post what works for one, we can show you how to do it for all of them. We just need to know things like: To, Subject, Body, etc. The date would just be the cell value + 14. It would then be sent at midnight on that day.

  5. #5
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    maybe I am misunderstanding? What you are talking about sounds like an e-mail?
    I want a notification reminder if possible?

  6. #6
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    Yes, email is a reminder. Tasks in Outlook have a recurring option. Were you wanting an Outlook Task "reminder"?

  7. #7
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    yes. A task reminder

  8. #8
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    I have this code...But It wants to set an appointment. I want to set a task reminder.

    Sub AppointmentAutomation()

    Dim OutApp As Object
    Set OutApp = CreateObject("Outlook.Application")

    Dim oAppt As AppointmentItem
    Dim oPattern As RecurrencePattern
    Set oAppt = OutApp.CreateItem(olAppointmentItem)
    Set oPattern = oAppt.GetRecurrencePattern
    With oPattern
    .RecurrenceType = olRecursWeekly
    .DayOfWeekMask = olMonday
    .PatternStartDate = Sheets(4).Range("D2")
    .PatternEndDate = Sheets(4).Range("D2")
    .Duration = 1
    .StartTime = Sheets(4).Range("D2")
    .EndTime = Sheets(4).Range("E2")
    End With
    oAppt.Subject = Sheets(4).Range("B2") & " " & Sheets(4).Range("C2")
    oAppt.save
    oAppt.Display

    Set OutApp = Nothing

    End Sub

  9. #9
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    Please paste code between code tags to keep structure. Click the # icon on the reply toolbar to insert the tags.

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    Kenneth,
    I hope you had a wonderful weekend. Thanks for all your help with this code.
    However, I am new to VBA and don't understand why I can't seem to get it to work.
    I changed things for what I needed but keep getting (Run-time error 91: Object variable or With block variable not set) on this line. Set oNS = oApp.GetNamespace("MAPI")

  11. #11
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    Please Login or Register  to view this content.

  12. #12
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    Did you add the Outlook object as I showed in the comment?

  13. #13
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    Yes sir. Mine is Microsoft Outlook 14.0 Object Library

  14. #14
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    I guess it "works" now?

    I don't know why you decided to do it all in one routine but that is up to you. I did not see where you set the value of dReminderTime.

  15. #15
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    I did miss setting the dReminderTime. thanks
    But, no it still gives the run-time err0r

  16. #16
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    Kenneth,
    This is a very good learning experience for me. Can I ask what you mean by "decided to do it all in one routine?" did I miss something or do something wrong?

  17. #17
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    No, there is nothing wrong with what you did.

    I tend to code in reusable routines so when I do similar tasks, I don't have to do it all over again. I call it being lazy since I reuse it to help a lot of people. So, Main() only has one line of code, the call to the Sub. Here is another example.
    Please Login or Register  to view this content.
    Note how I did the time input. The date is tomorrow. I would have to see your code to see why your time input is not what you want.
    Last edited by Kenneth Hobson; 11-20-2017 at 01:34 PM.

  18. #18
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    Please Login or Register  to view this content.

  19. #19
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    You do not need to use CDATE() nor FORMAT() when using the # delimited dates and times. Here I just used FORMAT() to make the value into a date string for concept illustration. While you can use string, I would do the dates and times using the numerical day number instead. Note the last example where I combined date and time.
    Please Login or Register  to view this content.
    Date+Time is just a day+dayFraction as shown in the title of the last MsgBox. Cdbl() was used to show the actual number.
    Last edited by Kenneth Hobson; 11-20-2017 at 02:07 PM.

  20. #20
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    instead of this CDate(#9:00:00 AM#) I tried TimeValue (08:00).
    But it still has the reminder set for 5 pm. I need the reminder to go off at 8 am

  21. #21
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    The input to TimeValue() is a string as I showed in post #9 and #17 which has the 8 PM that you wanted. Note how you have to add 12 to get the PM values in TimeValue(). Think of it as military time.

    You can use # format for dates and times as I showed in my last post and this one. Using parts of your #18...
    Please Login or Register  to view this content.

  22. #22
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    so...If I am understanding correctly. #20:00:00 # = 8:00 PM.
    I want it to be 8 AM. But even then...with the #20:00:00 # it still seemed to set the reminder for 5 PM.
    I am sorry if I am making this harder than it should be. I'm just so lost.
    Last edited by ColemanJames; 11-20-2017 at 02:49 PM.

  23. #23
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    No, 20 was for the timevalue.

    Use the AM PM option for the # method as I did in my last post. Either method works. As I explained, CDATE() is not needed unless you want to convert a date string to the numerical date. I often use that for a date string from a Userform's TextBox control. Date strings vs. number formatted dates can look the same but are not. Excel does try to convert Strings to actual dates. I don't like to rely on that.

    TimeValue()=a fractional date. # format is a date. Time in the # format is a fractional day. I could set the time value as 05. That is 0.5*24=12. 0.5 is 12 PM. So, if we don't use TimeValue() or # method for fractional day (a time value), we could do math. (12+8)/24=20/24=0.833 days = 8 PM.

    Try some tests like I first showed where I set date as DATE (today) and the time to Now + TimeValue("00:05:00"). Your reminder will popup around 5 minutes from Now (when you ran it).

    Once that works, try a one for date of DATE+1 and time for just after you get on, maybe #8:00 AM#. Using my Sub, you can do both in one Main() sub.
    Last edited by Kenneth Hobson; 11-20-2017 at 03:09 PM.

  24. #24
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    I'm just not understanding? I will have to leave the reminder set at 5 pm instead of 8 am.
    Thanks for all your help.

  25. #25
    Registered User
    Join Date
    07-25-2017
    Location
    Oiho
    MS-Off Ver
    2010 standard
    Posts
    90

    Re: How set a reminder in outlook with VBA Excel

    With the code below, please explain what part of this is setting the reminder for 5 PM

    Please Login or Register  to view this content.

  26. #26
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: How set a reminder in outlook with VBA Excel

    dReminderTime is the variable for Time.

    Do yourself a favor, either use DIM with Option Explicit as first line of code, or don't. I prefer it myself. Before I run code I Compile it. Using Compile (in the VBE's Debug menu) before a run will well, help you debug/catch errors before runtime.

    I don't know why you did dReminderTime that way. I guess you got it from my first post where I used a comment to show the string method. I used the following line of code there for that variable for the more reliable method. You are using a variable that does not exist in that line. You have concatenated extra space characters. So, you have two issues with that line of code.

    I guess you should change :
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Of course if E2 is a string, that won't work but then Compile would have showed you that problem. Use CDATE() around string dates when you want to do date math or convert a string to date format.

    This "might" work if you want to force Excel to make a guess at date conversion.
    Please Login or Register  to view this content.
    Date Time Tests:
    Please Login or Register  to view this content.
    I could show you some DateSerial() and TimeSerial() examples too but I think the Time for that might be for another Date...
    Last edited by Kenneth Hobson; 11-20-2017 at 04:31 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Setting a Reminder in Outlook via Excel
    By yka in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-18-2018, 01:05 PM
  2. Reminder mail from excel to outlook
    By burc67 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-03-2016, 09:19 AM
  3. excel reminder mail to outlook
    By burc67 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-03-2016, 08:30 AM
  4. How to create an excel reminder to Outlook? is it possible?
    By Maximilian88 in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-20-2015, 04:11 AM
  5. Outlook Email & Reminder from Excel
    By rucker222 in forum Outlook Programming / VBA / Macros
    Replies: 1
    Last Post: 03-14-2014, 04:43 AM
  6. Create Outlook Reminder on Excel and transfered to outlook by macro
    By Benjamin2008 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-21-2013, 03:23 PM
  7. Creating a reminder in excel and having a pop up in outlook
    By nazismart in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-03-2013, 04:58 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1