Automating Payroll Workflows: F&F Automation Using Excel VBA + Outlook
Sandeep Bhatt## 🧩 Problem: Manual Pain in Payroll
In many payroll teams, Full & Final (F&F) settlement files arrive daily via email. These contain 3 sheets — Bank File, Payslip, and JV Sheet — all with different formats. The team had to manually download these, store them in month-wise folders, and consolidate them by Lot No. for future reference.
This was:
- Time-consuming and repetitive
- Prone to errors
- Difficult to trace back in case of disputes
🔧 The Automation Solution
To eliminate this manual task, I built an Excel VBA macro with Outlook integration that:
1. Scans Outlook inbox for emails with a specific subject line
2. Identifies and saves the attached Excel file
3. Automatically creates a folder (based on month) and stores it
4. Extracts the 3 sheets, appends them into one workbook
5. Adds 2 standardized headers: Lot No. and Month for traceability
## ⚙️ Tools Used
- Microsoft Outlook
- Excel VBA
- FileSystemObject for folder structure automation
## 🎯 Impact
- Saved ~80% of manual effort
- No more file-naming errors or misplacement
- Faster F&F processing and better historical tracking
## 📌 Learnings
Even small automations can create big wins in operations. What matters is:
- Understanding real pain points
- Using accessible tools like Excel VBA
- Making solutions scalable and reusable
## 🙌 Coming Next
I’m building and sharing end-to-end workflow automations using Excel VBA, Outlook, Power Automate, and AI-driven tools. My goal: help teams future-proof their operations with intelligent automation, RPA, and cloud workflows. Follow me here or on [LinkedIn](https://www.linkedin.com/in/sandeepbhattt/) for future posts!
📜 Complete Excel VBA Code (Blog Version)
Here's the fully sanitized, blog-safe version of the automation macro that powers this consolidation workflow:
Scan specific emails in Outlook:
This Excel VBA macro connects to Outlook and automatically saves attachments from all emails received in the last 7 days that have subjects starting with "F&F Settlement - Lot".
It organizes the attachments into month-based folders (e.g., May_2025) created in the same directory as the macro file. This helps streamline the payroll F&F consolidation process by removing the need to manually check Outlook, download files, and organize folders.
Certainly! Here's the VBA code formatted for easy copying:
Sub SaveFFAttachments_AllRecentEmails()
Dim olApp As Object
Dim olNs As Object
Dim olFolder As Object
Dim olMail As Object
Dim att As Object
Dim savePath As String
Dim monthFolder As String
Dim fileName As String
Dim i As Long
Dim mailDateLimit As Date
' Set cutoff date: mails received in the last 7 days
mailDateLimit = Now - 7
' Initialize Outlook
On Error Resume Next
Set olApp = GetObject(, "Outlook.Application")
If olApp Is Nothing Then
MsgBox "Outlook is not open!", vbExclamation
Exit Sub
End If
On Error GoTo 0
Set olNs = olApp.GetNamespace("MAPI")
Set olFolder = olNs.GetDefaultFolder(6) ' Inbox
' Loop through mails
For Each olMail In olFolder.Items
If olMail.Class = 43 Then ' MailItem
' Subject filter + Date filter (ignore read/unread status)
If LCase(Left(olMail.Subject, 22)) = LCase("F&F Settlement - Lot") Then
If olMail.ReceivedTime >= mailDateLimit Then
If olMail.Attachments.Count > 0 Then
' Create save path dynamically
monthFolder = Format(olMail.ReceivedTime, "MMM_YYYY")
savePath = ThisWorkbook.Path & "\" & monthFolder & "\"
If Dir(savePath, vbDirectory) = "" Then MkDir savePath
' Save each attachment
For i = 1 To olMail.Attachments.Count
fileName = olMail.Attachments(i).FileName
olMail.Attachments(i).SaveAsFile savePath & fileName
Next i
End If
End If
End If
End If
Next olMail
MsgBox "Attachments (from last 7 days) saved successfully!"
End Sub
You can copy this code and use it in your VBA editor.
Sub ConsolidateFiles()
Dim FolderPath As String, FileName As String
Dim SourceWorkbook As Workbook, ws As Worksheet
Dim MasterWorkbook As Workbook, MasterSheet As Worksheet
Dim LastRow As Long, SourceLastRow As Long
Dim SheetNames As Variant, SheetName As Variant
Dim FolderName As String, LotNo As String
Dim LastCol As Long, LotNoCol As Long, MonthCol As Long
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Set MasterWorkbook = ThisWorkbook
FolderPath = ThisWorkbook.Path & "\"
FolderName = Format(Date, "MMM_YYYY")
SheetNames = Array("Bank File", "Payslip", "Recovery Details")
FileName = Dir(FolderPath & "*.xlsx")
Do While FileName <> ""
If FileName <> MasterWorkbook.Name Then
' Extract Lot No using regex
With regex
.Pattern = "Lot\w+"
.IgnoreCase = True
.Global = False
End With
If regex.Test(FileName) Then
LotNo = regex.Execute(FileName)(0)
Else
LotNo = "Unknown"
End If
Set SourceWorkbook = Workbooks.Open(FolderPath & FileName)
For Each SheetName In SheetNames
Set MasterSheet = MasterWorkbook.Sheets(SheetName)
On Error Resume Next
Set ws = SourceWorkbook.Sheets(SheetName)
On Error GoTo 0
If Not ws Is Nothing Then
LastRow = MasterSheet.Cells(MasterSheet.Rows.Count, "A").End(xlUp).Row
' Copy headers if empty
If LastRow = 1 Then
ws.Rows(1).Copy
MasterSheet.Rows(1).PasteSpecial xlPasteValues
MasterSheet.Rows(1).PasteSpecial xlPasteFormats
End If
' Copy data
ws.UsedRange.Offset(1, 0).Copy
MasterSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteValues
MasterSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteFormats
SourceLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
LotNoCol = 0: MonthCol = 0
LastCol = MasterSheet.Cells(1, MasterSheet.Columns.Count).End(xlToLeft).Column
' Add/find "LOT No"
On Error Resume Next
LotNoCol = MasterSheet.Rows(1).Find("LOT No", , xlValues, xlWhole).Column
If LotNoCol = 0 Then
LastCol = LastCol + 1
MasterSheet.Cells(1, LastCol).Value = "LOT No"
LotNoCol = LastCol
End If
' Add/find "Month"
MonthCol = MasterSheet.Rows(1).Find("Month", , xlValues, xlWhole).Column
If MonthCol = 0 Then
LastCol = LastCol + 1
MasterSheet.Cells(1, LastCol).Value = "Month"
MonthCol = LastCol
End If
On Error GoTo 0
' Fill Lot No & Month
MasterSheet.Range(MasterSheet.Cells(LastRow + 1, LotNoCol), _
MasterSheet.Cells(LastRow + SourceLastRow - 1, LotNoCol)).Value = LotNo
MasterSheet.Range(MasterSheet.Cells(LastRow + 1, MonthCol), _
MasterSheet.Cells(LastRow + SourceLastRow - 1, MonthCol)).Value = FolderName
Application.CutCopyMode = False
End If
Next SheetName
SourceWorkbook.Close False
End If
FileName = Dir
Loop
' Add summary row in Recovery Details
Set MasterSheet = MasterWorkbook.Sheets("Recovery Details")
LastRow = MasterSheet.Cells(MasterSheet.Rows.Count, "A").End(xlUp).Row
Dim col As Integer
For col = 6 To 14 ' Columns F to N
MasterSheet.Cells(LastRow + 2, col).Formula = "=SUM(" & Chr(64 + col) & "2:" & Chr(64 + col) & LastRow & ")"
Next col
ConsolidateJVSheet
MsgBox "Consolidation Complete!"
End Sub
Sub ConsolidateJVSheet()
Dim FolderPath As String, FileName As String
Dim SourceWorkbook As Workbook, ws As Worksheet
Dim MasterWorkbook As Workbook, MasterSheet As Worksheet
Dim LastRow As Long, SourceLastRow As Long
Dim FolderName As String, LotNo As String
Dim LastCol As Long, LotNoCol As Long, MonthCol As Long
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
Set MasterWorkbook = ThisWorkbook
FolderPath = ThisWorkbook.Path & "\"
FolderName = Format(Date, "MMM_YYYY")
Set MasterSheet = MasterWorkbook.Sheets("Journal Voucher")
FileName = Dir(FolderPath & "*.xlsx")
Do While FileName <> ""
If FileName <> MasterWorkbook.Name Then
With regex
.Pattern = "Lot\w+"
.IgnoreCase = True
.Global = False
End With
If regex.Test(FileName) Then
LotNo = regex.Execute(FileName)(0)
Else
LotNo = "Unknown"
End If
Set SourceWorkbook = Workbooks.Open(FolderPath & FileName)
On Error Resume Next
Set ws = SourceWorkbook.Sheets("Journal Voucher")
On Error GoTo 0
If Not ws Is Nothing Then
LastRow = MasterSheet.Cells(MasterSheet.Rows.Count, "A").End(xlUp).Row
' Copy header rows 1 & 2 if sheet is new
If LastRow = 1 Then
ws.Rows("1:2").Copy
MasterSheet.Rows("1:2").PasteSpecial xlPasteValues
MasterSheet.Rows("1:2").PasteSpecial xlPasteFormats
LastRow = 2
End If
' Copy data rows from row 3
SourceLastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
ws.Rows("3:" & SourceLastRow).Copy
MasterSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteValues
MasterSheet.Cells(LastRow + 1, 1).PasteSpecial xlPasteFormats
LotNoCol = 0: MonthCol = 0
LastCol = MasterSheet.Cells(2, MasterSheet.Columns.Count).End(xlToLeft).Column
' LOT No header in row 2
On Error Resume Next
LotNoCol = MasterSheet.Rows(2).Find("LOT No", , xlValues, xlWhole).Column
If LotNoCol = 0 Then
LastCol = LastCol + 1
MasterSheet.Cells(2, LastCol).Value = "LOT No"
LotNoCol = LastCol
End If
' Month header in row 2
MonthCol = MasterSheet.Rows(2).Find("Month", , xlValues, xlWhole).Column
If MonthCol = 0 Then
LastCol = LastCol + 1
MasterSheet.Cells(2, LastCol).Value = "Month"
MonthCol = LastCol
End If
On Error GoTo 0
' Fill LOT No & Month
MasterSheet.Range(MasterSheet.Cells(LastRow + 1, LotNoCol), _
MasterSheet.Cells(LastRow + SourceLastRow - 2, LotNoCol)).Value = LotNo
MasterSheet.Range(MasterSheet.Cells(LastRow + 1, MonthCol), _
MasterSheet.Cells(LastRow + SourceLastRow - 2, MonthCol)).Value = FolderName
Application.CutCopyMode = False
End If
SourceWorkbook.Close False
End If
FileName = Dir
Loop
End Sub
Follow me here or on LinkedIn for future posts!
Subscribe to my newsletter
Read articles from Sandeep Bhatt directly inside your inbox. Subscribe to the newsletter, and don't miss out.
Written by
Sandeep Bhatt
Sandeep Bhatt
Process Automation Expert | Excel VBA & Power Automate Specialist | AI + RPA Explorer After 13 years of building automation solutions in Excel and VBA — from payroll systems to complex F&F workflows — I now focus on designing modern, intelligent automations using Microsoft Power Automate, RPA, and AI-powered tools. I’m deeply interested in how prompt engineering, generative AI, and cloud-connected flows can replace repetitive work with smart, self-operating systems. My goal is simple: make operations faster, error-free, and future-proof. When I’m not automating Excel or building flows, I’m testing how AI can enhance decision-making and bring creative automation into everyday business.