CODEWITHSUNDEEP
excelvba
Arelius
Arelius

Reputation: 90

How to determine the current week number?

My goal is to put the current week number in the subject of outgoing mail.

Below gives me this week + 1.

I used to have 2016, now have 365. Problem is still here.

I tried Format(Now - 1, "ww") but it doesn't give current week - 1 week, my logic behind this was Format(Now - 1, "YYYY-MM-DD") which outputs today - one day's date.

Sub SendMail()

    Dim olApp As Outlook.Application
    Dim olEmail As Outlook.MailItem
    
    Set olApp = New Outlook.Application
    Set olEmail = olApp.CreateItem(olMailItem)
    
    With olEmail
        .BodyFormat = olFormatHTML
        .Display
        
        '.HTMLBody = "<p style=font-size:11pt;font-family:Calibri>Text here, but deleted in this moment..</p>" & .HTMLBody
        '.Attachments.Add "C:\temp\Bok1.xlsx"
        
        '.To = ""
        .Subject = "v" & Format(Now - 1, "ww") & " - PR11"
        
        '.Send
    End With

Set olApp = Nothing
Set olEmail = Nothing

End Sub

Upvotes: 0

Views: 239

Answers (1)

BigBen
BigBen

Reputation: 50162

Format has optional parameters:

  • FirstDayOfWeek
  • FirstWeekOfYear

Based on the comments, it looks like you're looking for:

Format(Date, "ww",,vbFirstFullWeek)

where vbFirstFullWeek means:

Start with the first full week of the year.

Upvotes: 2

Related Questions