Outlook tasks export

[eww]

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.

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

[pbaldy]

The WeekdayName function is expecting a numeric value between 1 and 7 representing the day of the week. You can either use the Weekday or DatePart functions to return that. I'd skip the WeekdayName function and just test the result of either of those. By default, Monday would be 2, but there's more info in Help.

[eww]

Sorry I should have mentioned that I did try this a few different ways first. My very first approach was

If DatePart([ProposedSubmittalDate], "w") = 2 Then...

and it wasn't recognizing this. I then tried the WeekdayName function and tried setting that equal to 2 and "Monday" with no luck.

I think the problem it is having is that the actual week number/name isn't listed in the date format, although I don't know why it would have a problem pulling it from that date, seems like something this program should be capable of doing. The actual error that I'm getting is "Invalid procedure call or argument"

[pbaldy]

You have the arguments reversed:

?datepart("w", date())
6

By the way, you have a lot of duplicated code. Personally, I would do more like:

...
.ReminderSet = True
If DatePart(...) = 2 Then
  .ReminderTime = DateAdd("d", -3, [ProposedSubmittalDate])         
Else
  .ReminderTime = DateAdd("d", -1, [ProposedSubmittalDate])  
End If
'Due on Proposed Submittal Date             
.DueDate = Me.txtPropSubDate.Value
...

[eww]

Woot! I did have it backwards. To my defense, the book I use for reference had it listed that way, but once I actually look at it in the editor, it's clear which way it goes, just an error in the book I suppose.

And you're right about the redundancy, I had it your way first but when I first got an error I changed it to the expanded version because I thought there might be a problem with that statement inside the With statement or something. Changing it back as I speak.

Thanks for your help!

[pbaldy]

Happy to help. As to the redundancy, it will work fine either way. I'm just lazy.