I've found some code that based on some information in the current record exports to a new task in Outlook. It works great so far, surprisingly easier than I thought, except...
I want to base the reminder date on the day of the week that the due date is. I wanted to set up that, if the due date is Tuesday-Friday, there is just a 1 day difference in the reminder. But if the due date is Monday, I want there to be 3 days so that the reminder will show up on Friday and not Sunday when no one will be in the office. My code's below, the part I'm having trouble with is extracting the day of the week from the actual date that is recorded, which is in the Short Date format.
Code:
Private Sub btnAddTask_Click()
Dim OutlookApp As Outlook.Application
Dim OutlookTask As Outlook.TaskItem
Set OutlookApp = CreateObject("Outlook.Application")
Set OutlookTask = OutlookApp.CreateItem(olTaskItem)
If WeekdayName([ProposedSubmittalDate]) = "Monday" Then
With OutlookTask
.Subject = Me.JobNumber & " / " & Me.txtSegment.Value & " / " & Me.txtDesc.Value & " " & Me.txtPropSubDate.Value
.Body = "Job #: " & Me.JobNumber & vbCrLf & "Segment: " & Me.txtSegment.Value & vbCrLf & "Submittal Description: " & Me.txtDesc.Value & vbCrLf & "Proposed Submittal Date: " & Me.txtPropSubDate.Value
.ReminderSet = True
'Remind 1 working day before Proposed Submittal Date
.ReminderTime = DateAdd("d", -3, [ProposedSubmittalDate])
'Due on Proposed Submittal Date
.DueDate = Me.txtPropSubDate.Value
.Save
End With
ElseIf WeekdayName([ProposedSubmittalDate]) <> "Monday" Then
With OutlookTask
.Subject = Me.JobNumber & " / " & Me.txtSegment.Value & " / " & Me.txtDesc.Value & " " & Me.txtPropSubDate.Value
.Body = "Job #: " & Me.JobNumber & vbCrLf & "Segment: " & Me.txtSegment.Value & vbCrLf & "Submittal Description: " & Me.txtDesc.Value & vbCrLf & "Proposed Submittal Date: " & Me.txtPropSubDate.Value
.ReminderSet = True
'Remind 1 working day before Proposed Submittal Date
.ReminderTime = DateAdd("d", -1, [ProposedSubmittalDate])
'Due on Proposed Submittal Date
.DueDate = Me.txtPropSubDate.Value
.Save
End With
End If
End Sub