CODEWITHSUNDEEP
vbadateoutlook
Jimmy Larkin
Jimmy Larkin

Reputation: 17

How to determine the workday prior to ReceivedTime?

The following code saves Outlook attachments and adds the date that the email was generated to the end of the name.

I am trying to add the previous workday.

Public Sub K_P3(itm As Outlook.MailItem)
    
    Dim objAtt As Outlook.Attachment
    
    Dim saveFolder As String
    saveFolder = "W:XXXX\"

    Dim dateformat As String
    dateformat = Format(itm.ReceivedTime, "yyyy-mm-dd Hmm ")
    
    For Each objAtt In itm.Attachments
        
        objAtt.SaveAsFile saveFolder & _
          Left(objAtt.FileName, InStrRev(objAtt.FileName, ".") - 1) & _
          " " & dateformat & _
          Mid(objAtt.FileName, InStrRev(objAtt.FileName, "."))
        
    Next
End Sub

Upvotes: 0

Views: 195

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Try this:

    dateformat = Format(application.workday(itm.ReceivedTime),-1), "yyyy-mm-dd") & _
                 Format(itm.ReceivedTime, " Hmm")

EDIT: forgot you were using Outlook:

dateformat = Format(Workday(itm.ReceivedTime,1), "yyyy-mm-dd Hmm")

Some code to (I think) mimic Excel's workday function:

Sub Tester()
    Dim arrHolidays
    arrHolidays = Array(#12/25/2019#) 'array of holidays
    
    Debug.Print Workday(#12/26/2019#, -1)              '12/25/2019
    Debug.Print Workday(#12/26/2019#, -1, arrHolidays) '12/24/2019
End Sub

'Find the workday `days` ago or in the future starting from `dt`.
'Ignores weekends, and optionally also an array of dates passed to `arrHols`
Function Workday(dt As Date, ByVal days, Optional arrHols As Variant = Empty)
    Dim rv, i, dy, delta, hol As Boolean
    rv = dt
    i = 0
    delta = IIf(days < 0, -1, 1) 'what direction are we headed?
    Do While i <> days
        rv = rv + delta
        dy = Weekday(rv, vbMonday)         'Monday=1
        If dy <> 6 And dy <> 7 Then        'not a weekend?
            'Int() below removes any time component from the date
            If Not IsEmpty(arrHols) Then hol = InArray(Int(rv), arrHols)
            If Not hol Then i = i + delta  'not a holiday?
        End If
    Loop
    Workday = rv
End Function

'Is value `v` in the array `arr` ?
Function InArray(ByVal v, arr) As Boolean
    Dim i As Long
    If IsEmpty(arr) Then Exit Function
    For i = LBound(arr) To UBound(arr)
        If arr(i) = v Then
            InArray = True
            Exit Function
        End If
    Next i
End Function

Upvotes: 1

Related Questions