IF Statement in VBA

Question

I have a If statement as followed:

If Weekday(Worksheets("Actual").Range("C1").Value) = vbSunday Then

What this If statement does is create 3 emails with attachments and links of data from Saturday and Friday, to send if the date entered in the worksheet is a Sunday. If Sundays date is the first of a month i.e.(August 1, 2010), making Saturday (July 31, 2010) and Friday (July 30, 2010), the If statement doesnt recognize the month change and therefore is unable to create the 3 emails with the necessary attachments and links.

How can I create/change my If statement so that when the 1st of a new month falls on a Sunday or Monday and I have to pull data from the previous month, it will recognize the month change and create the correct attachments and links??

Here is the complete code I have for creating the email...

Private Sub sendemail(esubj)

    Sheets("Actual").Select
    myfridate = Cells(1, 3).Value
    myfridate = DateAdd("d", -2, myfdate)
    myfridate = Format(myfridate, "mm-dd-yy")

    Sheets("Actual").Select
    mysatdate = Cells(1, 3).Value
    mysatdate = DateAdd("d", -1, myfdate)
    mysatdate = Format(mysatdate, "mm-dd-yy")


If Weekday(Worksheets("Actual").Range("C1").Value) = vbSunday Then

    Set omail = CreateItem(olMailItem)

    ROW_BEGIN = 1
    ROW_END = 72

    Sheet1.Activate
    Range("I7").Select
    fileSat = "\\firework\public\FINANCE\Daily Report\FY10\Key Indicator\"
    fileSat = fileSat & Left(Range("I7"), 3) & Right(Year(Date), 2)
    fileSat = fileSat & "\Key Indicator Daily Report - " & mysatdate & ".xls"

    Sheet1.Activate
    Range("I7").Select
    fileSun = "\\firework\public\FINANCE\Daily Report\FY10\Key Indicator\"
    fileSun = fileSun & Left(Range("I7"), 3) & Right(Year(Date), 2)
    fileSun = fileSun & "\Key Indicator Daily Report - " & mysundate & ".xls"

    Sheet1.Activate
    Range("I7").Select
    fileFri = "\\firework\public\FINANCE\Daily Report\FY10\Key Indicator\"
    fileFri = fileFri & Left(Range("I7"), 3) & Right(Year(Date), 2)
    fileFri = fileFri & "\Key Indicator Daily Report - " & myfridate & ".xls"


    With omail

    .Subject = "M Key Indicator Daily Report"
    .BodyFormat = olFormatHTML
    .HTMLBody = "<a href ='" & fileFri & "'>Key Indicator Daily Report - " & myfridate & "</a><br><a href ='" & fileSat & "'>Key Indicator Daily Report - " & mysatdate & "</a><br><a href ='" & fileSun & "'>Key Indicator Daily Report - " & mysundate & "</a>"
    .To = "me.com"
    .Display

    End With

    Set omail1 = CreateItem(olMailItem)

    With omail1

    .Subject = "R Key Indicator Daily Report"
    .BodyFormat = olFormatHTML
    .To = "you.com"
    .Attachments.Add fileFri
    .Attachments.Add fileSat
    .Attachments.Add fileSun
    .Display

    End With

    Set omail2 = CreateItem(olMailItem)

    With omail2

    .Subject = "K Key Indicator Daily Report"
    .BodyFormat = olFormatHTML
    .To = "them.com"
    .Attachments.Add fileFri
    .Attachments.Add fileSat
    .Attachments.Add fileSun
    .Display

End With

ElseIf Weekday(Worksheets("Actual").Range("C1").Value) = vbFriday Or _
    Weekday(Worksheets("Actual").Range("C1").Value) = vbSaturday Then

Else

    ROW_BEGIN = 1
    ROW_END = 72

    Sheet1.Activate
    Range("I7").Select
    fileSun = "\\firework\public\FINANCE\Daily Report\FY10\Key Indicator\"
    fileSun = fileSun & Left(Range("I7"), 3) & Right(Year(Date), 2)
    fileSun = fileSun & "\Key Indicator Daily Report - " & mysundate & ".xls"


    Set omail = CreateItem(olMailItem)

    With omail

    .Subject = "M Key Indicator Daily Report"
    .BodyFormat = olFormatHTML
    .HTMLBody = "<a href ='" & fileSun & "'>Key Indicator Daily Report - " & mysundate & "</a>"
    .To = "me.com"
    .Display

    End With

    Set omail1 = CreateItem(olMailItem)

    With omail1

    .Subject = "R Key Indicator Daily Report"
    .BodyFormat = olFormatHTML
    .To = "you.com"
    .Attachments.Add fileSun
    .Display

    End With

    Set omail2 = CreateItem(olMailItem)

    With omail2

    .Subject = "K Key Indicator Daily Report"
    .BodyFormat = olFormatHTML
    .To = "them.com"
    .Attachments.Add fileSun
    .Display

End With

End If

'ActiveWorkbook.Close
Set omail = Nothing

End Sub

Answer 1

Simply add a check before your IF statement which determines the correct mysundate based on whether the day of the Sunday is 1 or the day after the Sunday is 1.

If DatePart("d", mysundate) = 1 _ 
        Or DatePart("d", DateAdd("d",1,mysundate) ) = 1 Then
    mysundate = DateAdd("m",-1,mysundate)
    mysatdate = DateAdd("m",-1,mysatdate)
    ...
End If