Hi!
I am new to programming in excel, but am trying to make a macro for the first time. I want it to search through a column in my document with dates, figuring out how many days until this date, and send out emails to specific people when this particular date is coming up (6 months prior and 3 months prior). It worked in the beginning, but then I added some columns with information, and now I keep getting error 9 "Subscript out of bounds". I also added a PivotTable, which means I now have three sheets in my workbook. I only need the macro to go through the main sheet of information. How could I specify this in the code I have used?
Here is my code:
And the line that is highlighted when debugging is:
lastrow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
I am new to programming in excel, but am trying to make a macro for the first time. I want it to search through a column in my document with dates, figuring out how many days until this date, and send out emails to specific people when this particular date is coming up (6 months prior and 3 months prior). It worked in the beginning, but then I added some columns with information, and now I keep getting error 9 "Subscript out of bounds". I also added a PivotTable, which means I now have three sheets in my workbook. I only need the macro to go through the main sheet of information. How could I specify this in the code I have used?
Here is my code:
Code:
Sub datesexcelvba()
Dim myapp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim mydate2 As Long
Dim datetoday1 As Date
Dim datetoday2 As Long
Dim x As Long
lastrow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row
For x = 3 To x = lastrow
mydate1 = Cells(x, 7).Value
mydate2 = mydate1
Cells(x, 15).Value = mydate2
datetoday1 = Date
datetoday2 = datetoday1
Cells(x, 16).Value = datetoday2
Cells(x, 17).Value = mydate2 - datetoday2
If mydate2 - datetoday2 <= 180 And mydate2 - datetoday2 > 90 Then
Cells(x, 13) = "< 6 måneder"
Cells(x, 13).Interior.ColorIndex = 6
Cells(x, 13).Font.ColorIndex = 1
End If
If mydate2 - datetoday2 <= 90 And mydate2 - datetoday2 > 0 Then
Cells(x, 13) = "< 3 måneder"
Cells(x, 13).Interior.ColorIndex = 46
Cells(x, 13).Font.ColorIndex = 1
End If
If mydate2 - datetoday2 < 0 And mydate2 - datetoday2 > -30000 Then
Cells(x, 13) = "Utgått!"
Cells(x, 13).Interior.ColorIndex = 3
Cells(x, 13).Font.ColorIndex = 2
End If
If mydate2 - datetoday2 = 180 Then
Set myapp = New Outlook.Application
Set mymail = myapp.CreateItem(olMailItem)
mymail.To = Cells(x, 12).Value
With mymail
.Subject = "Kontrakten din utløper om 6 måneder"
.Body = "Du må titte på kontrakten din med XXX, den er i ferd med å gå ut." & vbCrLf & "Takk, Marte"
.Display
'.Send
End With
Cells(x, 8) = "Ja (6 mnd)"
Cells(x, 8).Interior.ColorIndex = 3
Cells(x, 8).Font.ColorIndex = 2
Cells(x, 8).Font.Bold = True
End If
If mydate2 - datetoday2 = 90 Then
Set myapp = New Outlook.Application
Set mymail = myapp.CreateItem(olMailItem)
mymail.To = Cells(x, 12).Value
With mymail
.Subject = "Kontrakten din utløper om 3 måneder"
.Body = "Du må titte på kontrakten din med XXX, den er i ferd med å gå ut." & vbCrLf & "Takk, Marte"
.Display
'.Send
End With
Cells(x, 8) = "Ja (3 mnd)"
Cells(x, 8).Interior.ColorIndex = 3
Cells(x, 8).Font.ColorIndex = 2
Cells(x, 8).Font.Bold = True
End If
Next
Set myapp = Nothing
Set mymail = Nothing
End SubAnd the line that is highlighted when debugging is:
lastrow = Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row