Sub SendCust_Email()
Dim ObjOutlook As Object
Set ObjOutlook = CreateObject("[Link]")
Dim ObjEmail As Object
Dim intRow As Integer
Dim strAgentID As String
intRow = 2
strAgentID = [Link]("Agent_Data").Range("A" & intRow).Text
While (strAgentID <> "")
Set ObjEmail = [Link](0) ' 0 for olMailItem
Dim strMail_Subject As String
Dim strMail_Body As String
Dim strDay As String
Dim strName As String
Dim strEmail_Id As String
Dim Daily_AHT As Date
Dim Daily_CRM As Double
Dim Daily_ACW As Date
Dim Daily_HoldTime As Date
Dim MTD_AHT As Date
Dim MTD_CRM As Double
Dim MTD_ACW As Date
Dim MTD_HoldTime As Date
Dim MTD_ACD As Integer
strMail_Subject = [Link]("Mail_Details").Range("A2").Text
strDay = [Link]("Mail_Details").Range("C2").Text
strName = [Link]("Agent_Data").Range("B" & intRow).Text
strEmail_Id = [Link]("Agent_Data").Range("C" & intRow).Text
Daily_AHT = [Link]("Agent_Data").Range("D" & intRow).Value
Daily_CRM = [Link]("Agent_Data").Range("E" & intRow).Value
Daily_ACW = [Link]("Agent_Data").Range("F" & intRow).Value
Daily_HoldTime = [Link]("Agent_Data").Range("G" &
intRow).Value
MTD_AHT = [Link]("Agent_Data").Range("H" & intRow).Value
MTD_CRM = [Link]("Agent_Data").Range("I" & intRow).Value
MTD_ACW = [Link]("Agent_Data").Range("J" & intRow).Value
MTD_HoldTime = [Link]("Agent_Data").Range("K" & intRow).Value
MTD_ACD = [Link]("Agent_Data").Range("L" & intRow).Value
' Format time values as h:mm:ss
FormattedDaily_AHT = Format(Daily_AHT, "h:mm:ss")
FormattedDaily_ACW = Format(Daily_ACW, "h:mm:ss")
FormattedDaily_HoldTime = Format(Daily_HoldTime, "h:mm:ss")
FormattedMTD_AHT = Format(MTD_AHT, "h:mm:ss")
FormattedMTD_ACW = Format(MTD_ACW, "h:mm:ss")
FormattedMTD_HoldTime = Format(MTD_HoldTime, "h:mm:ss")
strMail_Subject = Replace(strMail_Subject, "<AgentID>", strAgentID)
strMail_Body = "<html><body>"
strMail_Body = strMail_Body & "<p>Hi " & strName & ",</p>"
strMail_Body = strMail_Body & "<p>Please find below your daily and month-
to-date performance report:</p>"
strMail_Body = strMail_Body & "<table border='1' cellpadding='5'
cellspacing='0'>"
strMail_Body = strMail_Body &
"<tr><th>Metric</th><th>Daily</th><th>MTD</th></tr>"
strMail_Body = strMail_Body & "<tr><td>Hold Time</td><td>" &
FormattedDaily_HoldTime & "</td><td>" & FormattedMTD_HoldTime & "</td></tr>"
strMail_Body = strMail_Body & "<tr><td>ACW</td><td>" & FormattedDaily_ACW &
"</td><td>" & FormattedMTD_ACW & "</td></tr>"
strMail_Body = strMail_Body & "<tr><td>AHT</td><td>" & FormattedDaily_AHT &
"</td><td>" & FormattedMTD_AHT & "</td></tr>"
strMail_Body = strMail_Body & "<tr><td>CRM Log</td><td>" &
Format(Daily_CRM, "0.00%") & "</td><td>" & Format(MTD_CRM, "0.00%") & "</td></tr>"
strMail_Body = strMail_Body & "<tr><td>ACD</td><td>-</td><td>" & MTD_ACD &
"</td></tr>"
strMail_Body = strMail_Body & "</table>"
strMail_Body = strMail_Body & "<p>Thank you.</p>"
strMail_Body = strMail_Body & "<p>Regards,<br>xxx<br>"
strMail_Body = strMail_Body & "</body></html>"
With ObjEmail
.To = CStr(strEmail_Id)
.Subject = strMail_Subject
.HTMLBody = strMail_Body
.Send
End With
intRow = intRow + 1
strAgentID = [Link]("Agent_Data").Range("A" & intRow).Text
Wend
MsgBox "Done"
End Sub
