[go: up one dir, main page]
Scribd
Upload
31 views2 pages

Code

This document contains code for a VBA macro that sends customized daily performance emails to contact center agents. It retrieves agent data from an Excel workbook, inserts it into an email template, and sends individual emails to each agent.

Uploaded by

nehap2994
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
31 views2 pages

Code

This document contains code for a VBA macro that sends customized daily performance emails to contact center agents. It retrieves agent data from an Excel workbook, inserts it into an email template, and sends individual emails to each agent.

Uploaded by

nehap2994
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

Sub SendCust_Email()

Dim ObjOutlook As Object


Set ObjOutlook = CreateObject("Outlook.Application")
Dim ObjEmail As Object
Dim intRow As Integer
Dim strAgentID As String

intRow = 2
strAgentID = ThisWorkbook.Sheets("Agent_Data").Range("A" & intRow).Text

While (strAgentID <> "")

Set ObjEmail = ObjOutlook.CreateItem(0) ' 0 for olMailItem

Dim strMail_Subject As String


Dim strMail_Body As String
Dim strDay As String
Dim strFolder 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 = ThisWorkbook.Sheets("Mail_Details").Range("A2").Text
strMail_Body = ThisWorkbook.Sheets("Mail_Details").Range("B2").Text
strDay = ThisWorkbook.Sheets("Mail_Details").Range("C2").Text

strFolder = "C:\Users\neha.c\Desktop"
strAgentID = ThisWorkbook.Sheets("Agent_Data").Range("A" & intRow).Text
strName = ThisWorkbook.Sheets("Agent_Data").Range("B" & intRow).Text
strEmail_Id = ThisWorkbook.Sheets("Agent_Data").Range("C" & intRow).Text
Daily_AHT = ThisWorkbook.Sheets("Agent_Data").Range("D" & intRow).Value
Daily_CRM = ThisWorkbook.Sheets("Agent_Data").Range("E" & intRow).Value
Daily_ACW = ThisWorkbook.Sheets("Agent_Data").Range("F" & intRow).Value
Daily_HoldTime = ThisWorkbook.Sheets("Agent_Data").Range("G" &
intRow).Value
MTD_AHT = ThisWorkbook.Sheets("Agent_Data").Range("H" & intRow).Value
MTD_CRM = ThisWorkbook.Sheets("Agent_Data").Range("I" & intRow).Value
MTD_ACW = ThisWorkbook.Sheets("Agent_Data").Range("J" & intRow).Value
MTD_HoldTime = ThisWorkbook.Sheets("Agent_Data").Range("K" & intRow).Value
MTD_ACD = ThisWorkbook.Sheets("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 = Replace(strMail_Body, "<Name>", strName)
strMail_Body = Replace(strMail_Body, "<Daily_AHT>", FormattedDaily_AHT)
strMail_Body = Replace(strMail_Body, "<Daily_CRM>", Format(Daily_CRM,
"0.00%"))
strMail_Body = Replace(strMail_Body, "<Daily_ACW>", FormattedDaily_ACW)
strMail_Body = Replace(strMail_Body, "<Daily_HoldTime>",
FormattedDaily_HoldTime)
strMail_Body = Replace(strMail_Body, "<MTD_AHT>", FormattedMTD_AHT)
strMail_Body = Replace(strMail_Body, "<MTD_CRM>", Format(MTD_CRM, "0.00%"))
strMail_Body = Replace(strMail_Body, "<MTD_ACW>", FormattedMTD_ACW)
strMail_Body = Replace(strMail_Body, "<MTD_HoldTime>",
FormattedMTD_HoldTime)
strMail_Body = Replace(strMail_Body, "<MTD_ACD>", MTD_ACD)
strMail_Body = Replace(strMail_Body, "<Day>", strDay)

With ObjEmail
.To = CStr(strEmail_Id)
.Subject = strMail_Subject
.Body = strMail_Body
.Send
End With

intRow = intRow + 1
strAgentID = ThisWorkbook.Sheets("Agent_Data").Range("A" & intRow).Text

Wend

MsgBox "Done"

End Sub

You might also like