I am new to VBA and I have been trying fir the past weeks to get a code to work and I need help.
Anyways I have a spread sheet that has some dates in it and I need excel to send email notifications to everyone that has access to the file on a specific date reminding them to update it.
My problem is that the code does not seem to effect the processes on the due date but when I run/test is independently it runs smoothly but nothing happens on the due date.
The final codes I used which I thought will work but didn't is
- Code: Select all
Sub datesexcelvba()
Workbooks("NAME.xlsm").Worksheets("Sheet2").Activate
Dim mydate1 As Date
Dim mydate2 As Long
Dim x As Long
Dim tod As Date
tod = Now()
For x = 2 To 10
mydate1 = Cells(x, 2).Value
mydate1 = mydate2
'If cell.Value = mydate2 And cell.Value <> “” Then
If mydate1 >= tod Then
Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Interior.ColorIndex = 3
Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Font.ColorIndex = 2
Workbooks("NAME.xlsm").Worksheets("Sheet2").Range("B" & x).Font.Bold = True
End If
Next x
SendReminderMail
End Sub
ub SendReminderMail()
Dim OutApp As Object
Dim OutMail As Object
Dim strto As String, strcc As String, strbcc As String
Dim strsub As String, strbody As String
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strto = "email"
strcc = ""
strbcc = ""
strsub = "Tracking sheet Notification"
strbody = "Hi, The Spreadsheet has to be updated for this week. Kindly ignore if already updated."
With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody
.Display
End With
SendKeys "%{S}"
Set OutMail = Nothing
Set OutApp = Nothing
debugs:
If Err.Description <> "" Then MsgBox Err.Description
End Sub
I used the sendkey because the server I am working on has security restrictions which would not allow automated emails to be sent.
Guys please help