• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Send Simple Notification email to User to take Leave

Gunasekaran

Member
Hi all,
Need support to send an email "On Current Date" to the respective user reminding him\her to take a break for work/life balance. There is a Report Download From HR Portal. All users have applied for their leave for the next three months well in advance.

The object of this scenario - In case they forget, we need to notify them "On the same" day. To take the Leave. ( If criteria are met (Only Earn Leave & Current Date).

Sample file attached, I Don't have a idea, how to start and where to start here.

Only the rows and columns have been increased, the format is standard. If there is a solution code, I will schedule that macro in the task schedule for the daily morning.

Kindly help me.
 

Attachments

  • ApprovedLeave.xls
    26.5 KB · Views: 7
Hello, finally i did few code work, based on data, but i am not able to create emails for List of Users one by one Instead Single email for all Users?
If No User data, then skip the code. Kindly suggest me.

Also, kindly make me short Macro code, instead of my code is lengthy code.




Code:
Sub Macro1()
Dim sToday As String

    Set Sh = Sheet1
    Set Ws = Sheet2
    
Ws.Cells.Clear
    
lr = Sh.Range("A" & Rows.Count).End(xlUp).Row
    
Trgv = "Earn Leave"
sToday = Format(Date + 1, "MMM-DD")
Sh.Activate
Sh.Cells.Find(What:=sToday, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

Sh.Range("A1:A" & lr, "C1:C" & lr).Copy Ws.Range("A1")

ActiveCell.EntireColumn.Copy Sheets("Sheet2").Cells(1, 4)
Application.CutCopyMode = False

lr = Ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 2 Step -1
 If Ws.Range("D" & i) <> "Earn Leave" Then
 Ws.Range("D" & i).EntireRow.Delete
 End If
 Next i
End Sub
 

Attachments

  • Leave_Request - V1.xlsm
    23.3 KB · Views: 3
Back
Top