IF Statement in VBA
- by Edmond
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.
