Having issues setting Outlook reminders from excel userform

[tbollenbach]

I am using multiple userforms to gather info. I'm not using any worksheets.

I have a command button that creates an outlook task reminder that tells me what I need to close at a set time and date, 3 days from the time it was created, and it work great except I need it to not set any tasks on the weekend.

I'm thinking I need to set it for a 5 day work week , so a task created on friday will not count sat and sun as part of the 3 days.


Private Sub CommandButton1_Click()
Dim OLApp As Outlook.Application
Dim OLTk As Outlook.TaskItem
Set OLApp = New Outlook.Application
Set OLTk = OLApp.CreateItem(olTaskItem)

With CreateObject("Outlook.Application").CreateItem(3)
.Subject = "Test Subject"
.StartDate = Now
.DueDate = Now + 3
.ReminderTime = Now + 3
.Body = "This is a Test"
.ReminderSet = True
.Save
MsgBox "An Outlook Reminder Has been set"
End With
End Sub




I did not make this, I found it and adjusted it to work for what I need it to do....

any help would be appreciated

 

[Norie]

You'll need to check if the 3rd day from the current date falls on a Saturday or Sunday and if it does change the no of days so the reminder is set for the following Monday.

Perhaps something like this.

.DueDate = IIf(Weekday(Date, vbMonday) > 2, Date + 8 - Weekday(Date, vbMonday), Date + 3)

 

[tbollenbach]

Thank you for your response, I tried to use your information but I am unclear on what number I should change. I tried multiple times but I am unsure if it will work since I am using the "Now" option. How can I test it? Will I need to make a statement for every day?

 

[Norie]

You shouldn't need to change anything in the code I posted apart from perhaps Date to Now.

 

[tbollenbach]

Thank you again for your response, I am new to VBA and have been trying to teach myself. I am still running into issues, all this is doing is setting a outlook task for Monday. I take a lot of calls every day and create almost 100 cases for people daily. If I don't hear back from my customer in 3 days I close the case. so what I am trying to do is every time I create a case with my userform the last button I hit creates an Outlook reminder. That reminder pops up after 3 days and tells me what case to close, down to the minute. I need the code to "skip" Sat and Sun. So if I create a case on Wed the reminder needs to pop up on Monday, if created on Thurs then pop up on Tues..... maybe this code will work but I am confused on what your code is saying. could an IF statement be used?..... like

IF .duedate = vbsaturday then .duedate = vbmonday

 

[Norie]

Eh, I have used an if statement, an immediate if statement Iif.

The statement actually checks what the day of the week is and if it's after Tuesday it moves the reminder to the following Monday.

It's it's before Tuesday it adds 3 days.

If you run the code now it will always give you Monday, because this is Friday.

 

[tbollenbach]

But since it is Friday it should set the reminder for Wed, not Monday. It needs to be for 3 business days. will that code still work?

 

[Norie]

You didn't really mention business days.

That makes things a lot easier.

.DueDate = Application.Workday(Now, 3)

 

[tbollenbach]

You are awesome ... thank you so much. I knew it had to be a simple fix. It works very well, I do have 1 last question.... the reminder time. How do you get it to set for the exact time. As of right now it is default 12:00, how would you set it to be exactly 3 business days down to the minute? This is what I have...

Private Sub CommandButton1_Click()
 Dim OLApp As Outlook.Application
    Dim OLTk As Outlook.TaskItem
    Set OLApp = New Outlook.Application
    Set OLTk = OLApp.CreateItem(olTaskItem)
    
    With CreateObject("Outlook.Application").CreateItem(3)
        
        .Subject = "Test Subject"
        .StartDate = Now
        .DueDate = Application.WorkDay(Now, 3)
        .ReminderTime = Application.WorkDay(Now, 3)
        .Body = "Test"
        .ReminderSet = True
        .Save
        MsgBox "An Outlook Reminder Has been set"
   
    End With
End Sub

 

[Norie]

I did notice that the time was stripped when Application.Workday was used, so I had a think about it.

I've not tried this but I think it might work.

.DueDate = Application.WorkDay(Now, 3)+Time
.ReminderTime = Application.WorkDay(Now, 3)+Time