IF Statement in VBA

Question

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(Now()) = vbMonday Then

    Set omail = CreateItem(olMailItem)

    ROW_BEGIN = 1
    ROW_END = 72

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

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

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


    With omail

    .Subject = "M Daily Report"
    .BodyFormat = olFormatHTML
    .HTMLBody = "<a href ='" & fileFri & "'>Key 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"
    .Display

    End With

    Set omail1 = CreateItem(olMailItem)

    With omail1

    .Subject = "R Daily Report"
    .BodyFormat = olFormatHTML
    .To = "You"
    .Attachments.Add fileFri
    .Attachments.Add fileSat
    .Attachments.Add fileSun
    .Display

    End With

    Set omail2 = CreateItem(olMailItem)

    With omail2

    .Subject = "Mc Daily Report"
    .BodyFormat = olFormatHTML
    .To = "them"
    .Attachments.Add fileFri
    .Attachments.Add fileSat
    .Attachments.Add fileSun
    .Display

End With

Else

    ROW_BEGIN = 1
    ROW_END = 72

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


    Set omail = CreateItem(olMailItem)

    With omail

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

    End With

    Set omail1 = CreateItem(olMailItem)

    With omail1

    .Subject = "R Daily Report"
    .BodyFormat = olFormatHTML
    .To = "You"
    .Attachments.Add fileSun
    .Display

    End With

    Set omail2 = CreateItem(olMailItem)

    With omail2

    .Subject = "Mc Daily Report"
    .BodyFormat = olFormatHTML
    .To = "them"
    .Attachments.Add fileSun
    .Display

End With

End If

'ActiveWorkbook.Close
Set omail = Nothing

End Sub

I have code in vba, where if the weekday is monday, excel will generate 3 emails with 3 attachements/links. But if it is not Monday, excel will generate 3 emails with only 1 attachment/link. My issue is that In my excel spreadsheet there is a tab called Actual and it is populated with a date. If this date within my excel spreadsheet is changed on a monday, to any other day of the week, my vba code will still treat the program as if it is Monday. I need an IF statement that will allow the 3 emails with the 3 attachements/links to generate given the date typed in on the Actual tab within my spreadsheet. I hope this isnt confusing.

Answer 1

Currently, the line of code that evaluates equality to Monday (If Weekday(Now()) = vbMonday Then ) is checking against the current date, not the date on the sheet called "Actual". You should change that line to be something like so:

If Weekday(Worksheets("Actual").Range("A1").Value) = vbMonday Then

You'll want to replace A1 with the location of the date value that you're checking against, but that should return the date if it is properly formatted.

Answer 2

To specify a different action for Friday or Saturday, you would add a second statement using Elseif.

If Weekday(Worksheets("Actual").Range("A1").Value) = vbMonday Then
    'Do this
Elseif Weekday(Worksheets("Actual").Range("A1").Value) = vbFriday or _
Weekday(Worksheets("Actual").Range("A1").Value) = vbSaturday Then
    'Do this instead
Else
    'What happens for all other days
End If

The "Do this instead" if the day is Friday or Saturday could be as simple as "Exit Sub" which means nothing happens. Note that the "or" statement requires you to re-state all of the conditions. Pseudocode: "If today is Friday OR Saturday" will not work, you have to use "If today is Friday or if today is saturday".

If you found your needs to be even more specific you could set up a switch statement for each day of the week.