Hi There , I have the below code which works perfectly fine. The command button was first placed on the "Summary_Maint_List" sheet. I then created a User Landing Page (UI) called "UI_Annual_Planner" and copied the command button there so that the code can be activated from the UI page.
I also included a small code at the bottom (red) that un hides the Maint_List_Sheet and it opens that sheet.
The problem is, now that i have copied the command button to the UI sheet the code no longer works, when I copy it back to the Summary_Maint_List it works fine
does anyone know why that happens
Sub datesexcelvba()
Dim myApp As Outlook.Application, mymail As Outlook.MailItem
Dim mydate1 As Date
Dim datetoday1 As Date
Dim x As Long
lastRow = Sheets("Summary_Maint_List").Cells(Rows.Count, 1).End(xlUp).Row
'<<<<<<<<<<<<<<< Clear <<<<<<<<<<<<<<<<
Range("Maint_Summary[[Reminder 13]:[Days Difference 14]]").Clear
'<<<<<<<<<<<<<<<<<<<<<<<<<<<
DaysToAdd = Weekday(Date)
If DaysToAdd > 2 Then DaysToAdd = 0
datetoday1 = Date
For x = 5 To lastRow
If Cells(x, 11) <> "" Then
mydate1 = Cells(x, 12).Value
If ((mydate1 - datetoday1 <= 30) And (mydate1 - datetoday1 >= 30 - DaysToAdd)) _
Or ((mydate1 - datetoday1 <= 2) And (mydate1 - datetoday1 >= 2 - DaysToAdd)) Then
Set myApp = New Outlook.Application
Set mymail = myApp.CreateItem(olMailItem)
mymail.To = Cells(x, 7).Value
With mymail
.Subject = "HSE - Maintenance Reminder"
.Body = Cells(x, 6).Value & " " & Cells(x, 8).Value & " " & Cells(x, 12).Value & vbCrLf & "The above maintenance/revision is due on the specified date. " & vbCrLf & "Please inform your Regional HSE Advisor once completed and kindly upload the new record in your plant HSE folders on the N drive. " & vbCrLf & "Should you require any assistance please contact your Regional HSE Advisor, " & vbCrLf & "Kind Regards."
.Send
End With
Cells(x, 13) = "Yes"
Cells(x, 13).Interior.ColorIndex = 3
Cells(x, 13).Font.ColorIndex = 2
Cells(x, 13).Font.Bold = True
Cells(x, 14).Value = mydate1 - datetoday1
End If
End If
Next
Set myApp = Nothing
Set mymail = Nothing
With Sheets("Summary_Maint_List")
.Visible = True
.Activate
End With
End Sub
Bookmarks