Email alert not firing with VBA even though the condition is satisfied.

[abraham30]

Hi,

I have created a macro that will alert 70 days prior to the date available in column J. When I have used the same macro in some other sheet, it is working where as it is not working with this sheet.
Can anyone guide me in which part I made the mistake.



Sub ABCDEFG()
Dim rc1, rc2 As Long
Dim sht1 As String
sht1 = "GSK"
rc1 = ActiveWorkbook.Sheets(sht1).UsedRange.Columns.Count
If ActiveWorkbook.Sheets(sht1).AutoFilterMode Then ActiveSheet.AutoFilter.ShowAllData
Dim i As Long
For i = 14 To rc1
If ((ActiveWorkbook.Worksheets(sht1).Cells(i, 7) = "PSUR" Or ActiveWorkbook.Worksheets(sht1).Cells(i, 7) = "EU PSUR" Or _
ActiveWorkbook.Worksheets(sht1).Cells(i, 7) = "PBRER") And ActiveWorkbook.Worksheets(sht1).Cells(i, 31) = "" And _
ActiveWorkbook.Worksheets(sht1).Cells(i, 33) = "" And Trim(ActiveWorkbook.Worksheets(sht1).Cells(i, 10)) >= Date And _
Trim(ActiveWorkbook.Worksheets(sht1).Cells(i, 10)) <= Date + 70) Then
Call Fire_mail(i, "Alert Email")
End If
Next i
End Sub


Private Sub Fire_mail(x As Long, str As String)

Dim App As Object
Dim item As Object
Dim sMsgBody As String
Dim sht1
sht1 = "GSK"
On Error GoTo ***

If (str = "Alert Email") Then
esubject = ActiveWorkbook.Worksheets(sht1).Cells(x, 3) & " Product/License Configuration File and Missing data"
sMsgBody = "Dear friend" & "," & vbCr & vbCr
sMsgBody = sMsgBody & "Please update me on the below GSK product" & vbCr & vbCr

sMsgBody = sMsgBody & "Regards" & vbCr & vbCr
sMsgBody = sMsgBody & "ON BEHALF OF GSK"
ebody = sMsgBody

End If


sendto = ActiveWorkbook.Worksheets(sht1).Cells(x, 57)
ccto = "ABCD@XAX.COM"
Set App = CreateObject("Outlook.Application")
Set itm = App.CreateItem(olMailItem)
With itm

.Subject = esubject
.To = sendto
.CC = ccto
.Body = ebody
.Display
End With
Set App = Nothing
Set itm = Nothing
***:
End Sub

[Logit]

In which sheet does it function ? Sheet # and Sheet Name ?

In which sheet does it not function? Sheet # and Sheet Name ?

Is the above macro/s identical to the other that works ? If not, what is different ?

[abraham30]

The macro should work on the sheet name as "GSK".

[abraham30]

Dear Expert
Can anyone help me why the macro is not working. I am waiting for your reply or is there any alternative way where the macro can be developed so that email alert will work properly.

My requirement is to alert user 70 days prior to the date available in column J.
In my sheet, few columns were hide but I think macro should work irrespective of hide/unhide.

Thanks in advance