Hello Master,
I confuse why my function sendRemiderEmail cannot running,
this is my code
when i marking "If count > 0 " email will send, but if i using function IF code doesn't running.
anyone can help my problem?
Thanks,
I confuse why my function sendRemiderEmail cannot running,
this is my code
Code:
Private Sub Workbook_Open()
'fungsi untuk menjalankan sheet saat workbook open
Worksheets("Partlist").Select 'tambahin worksheet kalo mau jalanin sheet yang berbeda
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
Dim stDate As Long: stDate = Date + 2
Dim stDate1 As Long: stDate1 = Date + 6
Dim count As Integer
Dim sh As Worksheet
Dim wbNew As Workbook
count = 0
'filter data yang sesuai kondisi Today + 3
With Sheet1
.AutoFilterMode = False
With .Range("A3:AE5000")
.AutoFilter
.AutoFilter Field:=1, Criteria1:=">1", _
Operator:=xlAnd, Criteria2:="<7"
.AutoFilter Field:=2, Criteria1:=">" & stDate, _
Operator:=xlAnd, Criteria2:="<" & stDate1
End With
End With
Range("C3:AP5000").Copy 'copy data yang sudah di filter
Workbooks.Add 'create new workbook
Set wbNew = ActiveWorkbook 'setting new workbook sebagai active workbook
[A4].PasteSpecial xlPasteAll 'paste data yang di copy ke new workbook
[A4].PasteSpecial xlPasteValues
'Application.CutCopyMode = False
For Each cell In Range("B4:B5000") 'range cell
If cell.Value = Date + 3 Then
Cells(3, 1).Interior.ColorIndex = 3
Cells(3, 1).Font.ColorIndex = 1
Range("A3").Value = cell.Value 'menampilkan tanggal yang sesuai dengan tanggal hari ini +3
count = count + 1
Else ' jika tidak ada value maka range B3 dibuat blank
End If
Next
wbNew.Sheets(1).Name = "Monitoring List Local Wagon" ' setting untuk mengubah nama sheet di workbook baru
wbNew.SaveAs "Z:\SAP\Monitoring List CKD & Local\Reminder\Local\Reminder Monitoring Lot Local Wagon " & Format(Now, "dd-mmm-yy") & ".xlsx"
wbNew.Close 'close new workbook
' kondisi sending email hanya 1 kali
If count > 0 Then
SendReminderMail
ElseIf count = 0 Then
End If
'Worksheets("Monitoring List CKD F-Series").Select 'tambahin worksheet kalo mau jalanin sheet yang berbeda
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub
when i marking "If count > 0 " email will send, but if i using function IF code doesn't running.
anyone can help my problem?
Thanks,