If column cells certain values, read name and vlookup in another sheet for email

[Tet Htut Naing]

Dear All,

I am troubling with excel macro again.

There is staff attendance workbook in which 12 monthly sheets (one sheet represent one month) take records of all staff attendance time. If they are late on a date, then "pl" is put in that date of column. At the end of the columns, BN Column sums up the total frequency/time of late of staff.

What I want to do is
1) If a staff is late 5 times within a month or the value is 5 at one cell of Column BN, then read the value of the staff name in column C. Then, in Email Addr sheet within the same workbook, I want to do Vlookup to find the staff email address and his/her supervisor email address, adjacent cell to the staff email address.

2) Then I want to email to those email addresses.

What I find difficult for me is "reading the staff name" and VLookup for sending those email addresses in MS Outlook app.

Please help me.
Best Regards,
Ko Htut

 

[frank_AL]

So you want to send an email to the staff employee and their supervisor if the employee is late 5 or more times, correct?

Is the staff name in column C exactly the same as it is in the VLOOKUP Table? (for example if Naing, Tet is in column C of the attendance worksheet is it shown as Naing, Tet in the VLOOKUP Table?
Is the staff email address in column 2 of the VLOOKUP Table?
Is the supervisor email address in column 3 of the VLOOKUP Table?

 

[Tet Htut Naing]

Dear frank AL,

All correct for all of your questions. Thanks for your kind attention on my problem. And very sorry for my late respond.

Best Regards,
Ko Htut

 

[frank_AL]

Ko,

I may be a day or two getting back to you with code to resolve your issue. Code won't be difficult to write just trying to finish a project at my house before my wife disowns me!

Frank_al

 

[Tet Htut Naing]

Dear Frank_al,

Yes ?, it is ok and I really thank for your kind respond.

Best Regards,
Ko Htut

 

[frank_AL]

Ko,

I'm going to start developing code and have thought of a couple more questions.

1. What is the name of the worksheet where the staff members attendance exists?
2. What is the name of the worksheet where the VLOOKUP Table exists?
3. Do you want to send the email TO the staff member and CC their supervisor? Send as TO to both? Doesn't matter to me, just let me know.
4. If possible, can you use a cell on the worksheet where the VLOOKUP table exists to enter what you want for text in the body of the email? If so, give me the Cell ID.

 

[Tet Htut Naing]

Dear Frank_AL,

1. Attendance are recorded in 12 monthly sheets and those sheets are named like Jan_'18, Feb_'18, Mar_'18, etc.. Staff names exist in Column 2 starting from Row 4 consistently in all monthly sheets.

2. Vlookup table existing sheet name is Email Addr.

3. I would prefer Send as TO both.

4. Yes, it is possible to use a cell for email body text, namely E4 of Email Addr worksheet. But again, there is another condition, like if a staff was late 5 days, then use E4 of Email Addr worksheet and if 6 days, use E5. I did not mention this in my original post just because I am afraid of making confused.

Thanks for your questions. I really appreciate you and respect your precious time. Sorry for my late respond cos I live in Myanmar and it is night.

Witg Respect,
Ko Htut

 

[frank_AL]

Ko,

I believe the code below meets your request.

A couple of thing to address:
1. I set the Subject of the Email to "Failure to Comply with Attendance Policy" You can search on that line of code and change as needed.
2. Right now the code creates the email and displays it for you to review. If you want the code to automatically Send the email you will need to change this line of code: ".Display" to ".Send"

Give the code a try and let me know if you experience any issues.

Frank_al

Option Explicit


Sub SendEmail()
Dim currmonth As Worksheet
Dim email As Worksheet
Dim usersel As String
Dim i As Long
Dim lastrow As Long
Dim OutLookApp As Object
Set OutLookApp = CreateObject("OutLook.Application")
Dim MItem As Object
Dim staff As String
Dim staffem As String
Dim svrem As String
Dim emailbody As String
Const olMailItem = 0
Dim lookuptable As Range


usersel = InputBox("Enter Month to be Evaluated")
Set currmonth = Worksheets(usersel)
Set email = Worksheets("Email Addr")
lastrow = email.Cells(email.Rows.Count, "A").End(xlUp).Row
Set lookuptable = email.Range("A1:C" & lastrow)


lastrow = currmonth.Cells(currmonth.Rows.Count, "C").End(xlUp).Row


For i = 4 To lastrow
    If Range("BN" & i).Value >= 5 Then
    staff = currmonth.Range("C" & i)
        staffem = Application.WorksheetFunction.VLookup(staff, lookuptable, 2, False)
        svrem = Application.WorksheetFunction.VLookup(staff, lookuptable, 3, False)
        If Range("BN" & i).Value = 5 Then
            emailbody = email.Range("E4").Value
        Else
            emailbody = email.Range("E5").Value
        End If
        Set MItem = OutLookApp.CreateItem(olMailItem)
    '    On Error GoTo EmailFailed
        With MItem
            .BodyFormat = 3
            .To = staffem & ", " & svrem
            .Subject = "Failure to Comply with Attendance Policy"
            .HTMLBody = emailbody
            .Display
        End With
    End If
'EmailFailed:
Next i


End Sub

 

[Tet Htut Naing]

Dear Frank_AL,

Thank a lot for the code. I will test it very soon and get back here with answer.

Best Regards,
Ko Htut

 

[Tet Htut Naing]

Dear Frank_AL,

It is great to have your code. I really thank for your kind helps.

I tried to reply 6 hours ago and failed just because of internet connection, sorry.
But one error shown debug is the line

staffem = Application.WorksheetFunction.VLookup(staff, lookuptable, 2, False)

Another thing, if possible, I would like to request is that I would like to run the code on cell values changes of BN Column.

However, I really appreciate and deeply thank for your kind help so far.

Best Regards,
Ko Htut