In this section, I’ve explained an Excel tool that can be utilised by the Accounts Receivable team to track and manage due invoices.
Often, the Accounts Receivable team has to track all invoices that are due, whether they, will be due in the future or are already due and accordingly, payment requests are sent to vendors or liable stakeholders.
Get Rishab Dang’s stories in your inbox
Join Medium for free to get updates from this writer.
In this Excel, we are doing the following steps using the 4 macro buttons:-
First, it works by cleaning & massaging the data downloaded from SAP, in the format that is desired by the regional manager.
Then, it segregates the data into three different sheets as per the due date of the invoice. “Currently Due”, Future Due”, “Past Due”
Then, in the next click, it first, creates folders for each manager’s name.
Then, it creates different Excel workbooks for different regions and pastes the new file into the newly created folder of each regional Manager. One manager can manage more than one region, therefore a folder can have multiple files.
Draft and send an email to all those managers who currently have due or past due invoices with an attachment of complete details for their region.
Press enter or click to view image in full size
Below is the list of VBA codes used to create the above macros :-
errorhandlor: MsgBox "An Unexcepted error has occured while creating folders, please check the user id and the name of the new column you have created on your desktop", vbCritical, "Productivity Team"
End Sub
Creating Folders on the Desktop
Sub creating_folders()
On Error Resume Next
Dim newlastrow As Long Dim newfolder As String Dim FCname As String Dim xrow As Long Dim xcol As Long Dim i As Long Dim myid As String
Set currentwb = ThisWorkbook For i = 1 To newlastrow - 1 CCname = Sheets("CC & FC").Cells(xrow, xcol).Value On Error Resume Next Sheets("Invoicing Due - Working").Select Range("A1").Select
newwb.Close xrow = xrow + 1 Next i MsgBox "Files & Folders have been created on your desktop", vbInformation, "Productivity Team" End Sub
Draft & send emails with attachments
Sub AttachFilesToEmail()
Application.ScreenUpdating = False
'On Error GoTo error_handlor
Dim objOutlook As Object Dim objMail As Object Dim objFileSystem As Object Dim objFolder As Object Dim objFile As Object Dim strFolderPath As String Dim strFileName As String
Dim FCname As String Dim i As Long Dim xrow As Long Dim xcol As Long Dim newlastrow As Long Dim FCEmail As String Dim emailbody As String
strFolderPath = "C:\Users\rdang01\OneDrive - Kearney\Desktop\Invoice Schedule Files\" & FCname & "\" 'check if \ is required in the end
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail .Subject = "Invoice Schedule Report " .Body = "Hi " & FCname & emailbody .To = FCEmail End With
Set objFileSystem = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFileSystem.GetFolder(strFolderPath)
' Loop through each file in the folder For Each objFile In objFolder.Files ' Check if the file is an Excel file If LCase(Right(objFile.Name, 5)) = ".xlsx" Or LCase(Right(objFile.Name, 4)) = ".xls" Then ' Attach the file to the email objMail.Attachments.Add objFile.Path End If Next objFile
objMail.Display
' Clean up objects Set objMail = Nothing Set objOutlook = Nothing Set objFileSystem = Nothing Set objFolder = Nothing Set objFile = Nothing
xrow = xrow + 1
Next i
Application.ScreenUpdating = True
MsgBox "Emails have been sucessfully created", vbInformation, "Productivity Team"
Exit Sub
error_handlor: MsgBox "An Unexpected error has occurred, Kindly check the names in the folders and the names in CC&FC sheet column M& N are correct", vbCritical, "Productivity Team"
