Case Study- Automation of processing and sending reports

1. Project Introduction:

• Each team from the Accounting department of a SSC had a delegated person who was responsible to manually prepare and send via email every day, for 10-15 days in a row each month, a report regarding the status of the account reconciliations for the month. There was no automation for this process and many users where doing hours of useless manual work each month.
• The process was taking aprox. 20 minutes/day for each person.
• Considering that the number of teams at that moment was 8 and another 3-4 teams were planned to start working in the center, the approximate time spent with these reports was:
  10 teams * 20 minutes/day * 12 days/month= 40 hours per month.

2. Project specifications:

• We download from the account reconciliations application a report with raw data regarding the reconciliations of all teams.

For our automation, we need to process the reports as follows:

Each team (country) must send, to a list of defined users, every day an email containing:

2.1. A table in the body of the email containing:

• Total no of recs
• Total recs sent for review
• Total recs reviewed/Total Rec
• Total recs In Progress and Not Started
• %sent for Review
• %Total Rev / Total Rec
• %Total In Progress and Not Started

2.2. An attachment with an Excel file with all the recs of the team
2.3. A standard text + the due date of the recs, the date of report etc.
2.4. Multiple email languages: there must be the possibility to send the emails in 2 languages.

3. Project Implementation:

Only one user will send the reports for all teams with minimum data processing effort and manual work: the numbers are automatically calculated based on the raw data exported from the system and the emails are automatically processed and sent)

• I created an Excel VBA application in which a user inputs the raw data in one sheet.
• In another sheet there are calculated with formulas the numbers and percentages to be sent for each team in the body of the email.
• For each team, the user can input the email addresses where to send the email (to and cc), the language of the email and has the option to disable sending for one or more records.
• After he ensures all data is correct, the responsible user presses a button and the automation script sends all emails with the HTML table in the body and the desired excel report attached (containing only the correct data for each team).

–> Sample of the raw data:

–> Sample of the Sheet with calculated data:

–> Example of email sent:

4. Benefits:

• After the improvement and automation of this process, one user is spending aprox 20 minutes per day for an average of 12 days each month. This means aprox. 4 hours per month.
• Considering that initialy it took aprox. 40 hours per month, the saved time is aprox. 35 hours per month.
• The planning and development of the “mini-project” took approximately 6 hours. After it was tested a few days and we ensured the data is correct, the script was implemented.

5. Sample of my VBA code that creates and sends Outlook emails with HTML table and filtered attachement:

5.1. Send Emails:

Sub send_html_mails()

Set ws = ThisWorkbook.ActiveSheet
lr = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Dim createdFilePath As String

Dim attachedBook As Workbook

'confirm the email sending
x = MsgBox("Are you sure you want to continue ? Did you check values, emails etc ?" & vbNewLine & vbNewLine & _
"Clickk yes if you want to send or no to Cancel !", vbYesNo, "Ireversible action !")

TempFilePath = Environ$("temp") & "\"

If x = vbNo Then Exit Sub

'how many emails we are sending ?
counter = 0

For i = 3 To lr

Application.StatusBar = "Sending files.. " & Format((i - 2) / (lr - 2), "0%")

comp_code = ws.Range("B" & i)
E_mail = ws.Range("L" & i)
cc_all = ws.Range("M" & i)
report_date = ws.Range("J" & i)
v1 = ws.Range("C" & i)
p1 = Format(ws.Range("G" & i), "Percent")
v2 = ws.Range("D" & i)
v3 = ws.Range("E" & i)
p2 = Format(ws.Range("H" & i), "Percent")
v4 = ws.Range("F" & i)
p3 = Format(ws.Range("I" & i), "Percent")
due_date = ws.Range("K" & i)
limba = ws.Range("N" & i)
isactive = ws.Range("O" & i)

'if we don't want to send, go to next item
If UCase(isactive) = "NO" Then GoTo nextmail

If limba = "EN" Then
 htmlMsg = "<html> <head> <style> table, th, td {border: 1px solid black; border-collapse: collapse;} </style> </head>"
 htmlMsg = htmlMsg & " <body> <p>Hi all,</p> <p>Please find below the reconciliation report for " & comp_code & " at " & report_date & "." & "</p>"
 htmlMsg = htmlMsg & "<table style=" & """" & "width:50%" & """" & ">"
 htmlMsg = htmlMsg & "<tr> <td>" & "Total no of recs:" & "</td> <td>" & v1 & "</td>" & "<td>" & "%" & "</td> </tr> " 'first row (header)
 htmlMsg = htmlMsg & "<tr bgcolor=" & """" & "#808080" & """" & "> <td colspan=" & """" & 3 & """" & "> </td> </tr>" 'second row(empty)
 htmlMsg = htmlMsg & "<tr> <td>" & "Total sent for review" & "</td> <td>" & v2 & "</td>" & "<td>" & p1 & "</td> </tr> " 'third row
 htmlMsg = htmlMsg & "<tr> <td>" & "Total reviewed/Total Rec" & "</td> <td>" & v3 & "</td>" & "<td>" & p2 & "</td> </tr> " '4th row
 htmlMsg = htmlMsg & "<tr> <td>" & "Total In Progress and Not Started" & "</td> <td>" & v4 & "</td>" & "<td>" & p3 & "</td> </tr> </table>" '5th row and close table
 htmlMsg = htmlMsg & " <p>Please be reminded that all the reconciliations are due until " & due_date & "." & "</p>"
 htmlMsg = htmlMsg & " <p>To ensure we meet the deadline we kindly ask both SSC and Local Finance Teams to upload and to review the reconciliations on a daily basis.</p>"
 htmlMsg = htmlMsg & " <p>Best Regards <br></p> <p><i>This e-mail was generated automatically</i></p> </body> </html> "

ElseIf limba = "FR" Then
 htmlMsg = "<html> <head> <style> table, th, td {border: 1px solid black; border-collapse: collapse;} </style> </head>"
 htmlMsg = htmlMsg & " <body> <p>Bonjour a tous,</p> <p>Ci-joint le fichier avec le statut des reconciliations pour " & comp_code & " le " & report_date & "." & "</p>"
 htmlMsg = htmlMsg & "<table style=" & """" & "width:50%" & """" & ">"
 htmlMsg = htmlMsg & "<tr> <td>" & "Total no of recs:" & "</td> <td>" & v1 & "</td>" & "<td>" & "%" & "</td> </tr> " 'first row (header)
 htmlMsg = htmlMsg & "<tr bgcolor=" & """" & "#808080" & """" & "> <td colspan=" & """" & 3 & """" & "> </td> </tr>" 'second row(empty)
 htmlMsg = htmlMsg & "<tr> <td>" & "Sent for review" & "</td> <td>" & v2 & "</td>" & "<td>" & p1 & "</td> </tr> " 'third row
 htmlMsg = htmlMsg & "<tr> <td>" & "Total reviewed/Total Rec" & "</td> <td>" & v3 & "</td>" & "<td>" & p2 & "</td> </tr> " '4th row
 htmlMsg = htmlMsg & "<tr> <td>" & "Total In Progress and Not Started" & "</td> <td>" & v4 & "</td>" & "<td>" & p3 & "</td> </tr> </table>" '5th row and close table
 htmlMsg = htmlMsg & " <p>Pour s’assurer qu’on ne depasse pas le delai " & due_date & " merci de bien vouloir, SSC et local finance, remonter et approuver les reconciliations dans Assurenet chaque jour." & "</p>"
 htmlMsg = htmlMsg & " <p>Pour plus de details concernant leur etat actuel, merci de consulter le fichier ci-joint.</p>"
 htmlMsg = htmlMsg & " <p>Bonne journee. <br></p> <p><i>This e-mail was generated automatically</i></p> </body> </html> "

End If

'MsgBox htmlMsg
'Exit Sub

'create file for attach (call another procedure that creates the file in the temp folder and attach it in the email
Call createwb(comp_code, createdFilePath)

'create and send the email
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
 
 'On Error Resume Next
 With OutMail
 .To = E_mail
 .cc = cc_all
 .BCC = ""
 .Subject = "Reconciliations report" & " " & comp_code & " " & report_date
 .HTMLBody = htmlMsg
 .Attachments.Add createdFilePath
 .send
 End With
 
 'On Error GoTo 0
 SendKeys "%{s}", True

 Set OutMail = Nothing
 Set OutApp = Nothing
 
 With Application
 .EnableEvents = True
 End With
 
 'delete the created and sent as attachment file from the temporary files location
 Call kill_temp_file(createdFilePath)
 
 counter = counter + 1

nextmail:
Next i

Application.StatusBar = False

MsgBox "Mail sending completed.. !" & vbNewLine & vbNewLine & counter & " mails sent.", vbInformation

End Sub

5.2. Create files in temporary folder

Sub createwb(ByVal clusterName As String, createdFile As String)

Set dsh = ThisWorkbook.Worksheets("Data")
lr = dsh.Cells(dsh.Rows.Count, "B").End(xlUp).Row

TempFilePath = Environ$("temp") & "\"

Dim retBook As Workbook

Application.ScreenUpdating = False

On Error Resume Next
dsh.ShowAllData
On Error GoTo 0

'ensure the name of the file will not contain the / or \ characters
cluster_new_name = Replace(clusterName, "/", "")
cluster_new_name = Replace(cluster_new_name, "\", "")

'add a new workbook
Set retBook = Workbooks.Add

'the file name
createdFile = TempFilePath & cluster_new_name & ".xlsx"

'filter the data in data sheet by the cluster and copy the filtered range to the new workbook
dsh.Range("$A$5:$T$" & lr).AutoFilter Field:=18, Criteria1:=clusterName

dsh.AutoFilter.Range.Copy

With retBook.ActiveSheet.Range("A1")
    .PasteSpecial xlPasteFormats
    .PasteSpecial xlPasteValues
    .PasteSpecial xlPasteColumnWidths
End With

retBook.ActiveSheet.Paste

Application.CutCopyMode = False

'Save the file in the temporary folder location
retBook.SaveAs createdFile

'close the file
retBook.Close

'unfilter data sheet
On Error Resume Next
dsh.ShowAllData
On Error GoTo 0

Application.ScreenUpdating = True

End Sub

5.3. Delete created file:

Sub kill_temp_file(ByVal tempfile As String)

Kill tempfile

End Sub