Designing Office Automation Workflows

Every morning when you get to work, the same tasks are waiting. Organize data, create reports, send emails... These are repetitive tasks. You've probably thought, "Can't this be automated?"

Now you really can automate it! Combining AI with Excel lets you automate your workflow from clock-in to clock-out. You can handle the entire process of collecting data, analyzing it, creating reports, and even sending emails with just one button.

Imagine this: At 9 AM, it automatically collects yesterday's sales data, AI analyzes it, creates a beautiful report, and sends it via email to executives. You just check the results.

In this post, we'll learn how to design and build workflows that completely automate real work. From daily reports to customer management and inventory management, we'll create automation systems you can apply right away, step by step.

1. Daily Report Auto-Generation Workflow

Overall Process

1. Data collection (from multiple files/sheets)
2. Data cleaning and validation
3. Aggregation and analysis
4. Generate insights with AI
5. Report formatting
6. Auto-send email

AI Prompt:

"Create a fully automated system that runs every morning at 9 AM,
collects yesterday's sales data, analyzes it, creates a report,
and sends it via email."

AI Generated VBA:

' ============================================
' Daily Report Automation Master System
' ============================================

Sub DailyReport_Master()
    Dim startTime As Double
    startTime = Timer

    On Error GoTo ErrorHandler

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

    ' Step 1: Data Collection
    Call CollectData

    ' Step 2: Data Cleaning
    Call CleanData

    ' Step 3: Aggregation and Analysis
    Call AnalyzeData

    ' Step 4: AI Insights
    Call GenerateAIInsights

    ' Step 5: Generate Report
    Call GenerateReport

    ' Step 6: Send Email
    Call SendEmail

    Application.DisplayAlerts = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

    Dim elapsed As Double
    elapsed = Timer - startTime

    MsgBox "Daily report generated and sent." & vbCrLf & _
           "Time elapsed: " & Format(elapsed, "0.0") & " seconds", _
           vbInformation, "Task Complete"

    Exit Sub

ErrorHandler:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True

    MsgBox "Error occurred: " & Err.Description & vbCrLf & _
           "Error location: " & Erl, vbCritical
End Sub

' ============================================
' Step 1: Data Collection
' ============================================
Sub CollectData()
    Dim targetWs As Worksheet
    Dim sourceFolder As String
    Dim fileName As String
    Dim wb As Workbook
    Dim lastRow As Long

    Set targetWs = ThisWorkbook.Sheets("RawData")
    targetWs.Cells.Clear

    ' Folder path to collect from
    sourceFolder = ThisWorkbook.Path & "\DailyData\"

    ' Write headers
    targetWs.Cells(1, 1).Value = "Date"
    targetWs.Cells(1, 2).Value = "Branch"
    targetWs.Cells(1, 3).Value = "Product"
    targetWs.Cells(1, 4).Value = "Quantity"
    targetWs.Cells(1, 5).Value = "Sales Amount"

    lastRow = 2

    ' Process all Excel files in folder
    fileName = Dir(sourceFolder & "*.xlsx")

    Do While fileName <> ""
        If fileName <> ThisWorkbook.Name Then
            Set wb = Workbooks.Open(sourceFolder & fileName, ReadOnly:=True)

            ' Copy data
            Dim srcLastRow As Long
            srcLastRow = wb.Sheets(1).Cells(wb.Sheets(1).Rows.Count, "A").End(xlUp).Row

            If srcLastRow > 1 Then
                wb.Sheets(1).Range("A2:E" & srcLastRow).Copy
                targetWs.Cells(lastRow, 1).PasteSpecial xlPasteValues
                lastRow = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row + 1
            End If

            wb.Close SaveChanges:=False
        End If

        fileName = Dir()
    Loop

    Application.CutCopyMode = False

    Debug.Print "Data collection complete: " & lastRow - 2 & " records"
End Sub

' ============================================
' Step 2: Data Cleaning
' ============================================
Sub CleanData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim deleteCount As Long

    Set ws = ThisWorkbook.Sheets("RawData")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Remove duplicates
    ws.Range("A1:E" & lastRow).RemoveDuplicates _
        Columns:=Array(1, 2, 3), _
        Header:=xlYes

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Data validation and outlier removal
    For i = lastRow To 2 Step -1
        Dim isInvalid As Boolean
        isInvalid = False

        ' Date validation
        If Not IsDate(ws.Cells(i, 1).Value) Then isInvalid = True

        ' Quantity validation (negative or abnormally large)
        If ws.Cells(i, 4).Value < 0 Or ws.Cells(i, 4).Value > 10000 Then
            isInvalid = True
        End If

        ' Sales amount validation
        If ws.Cells(i, 5).Value < 0 Or ws.Cells(i, 5).Value > 100000000 Then
            isInvalid = True
        End If

        If isInvalid Then
            ws.Rows(i).Delete
            deleteCount = deleteCount + 1
        End If
    Next i

    ' Trim spaces
    For i = 2 To lastRow
        ws.Cells(i, 2).Value = Trim(ws.Cells(i, 2).Value) ' Branch
        ws.Cells(i, 3).Value = Trim(ws.Cells(i, 3).Value) ' Product
    Next i

    Debug.Print "Data cleaning complete: " & deleteCount & " records deleted"
End Sub

' ============================================
' Step 3: Data Analysis
' ============================================
Sub AnalyzeData()
    Dim sourceWs As Worksheet
    Dim reportWs As Worksheet
    Dim lastRow As Long

    Set sourceWs = ThisWorkbook.Sheets("RawData")
    lastRow = sourceWs.Cells(sourceWs.Rows.Count, "A").End(xlUp).Row

    ' Prepare report sheet
    On Error Resume Next
    Set reportWs = Sheets("AnalysisResults")
    If reportWs Is Nothing Then
        Set reportWs = Sheets.Add(After:=sourceWs)
        reportWs.Name = "AnalysisResults"
    Else
        reportWs.Cells.Clear
    End If
    On Error GoTo 0

    ' 1. Overall summary
    With reportWs
        .Cells(1, 1).Value = "Daily Sales Summary"
        .Cells(1, 1).Font.Size = 14
        .Cells(1, 1).Font.Bold = True

        .Cells(3, 1).Value = "Analysis Date:"
        .Cells(3, 2).Value = Date - 1
        .Cells(3, 2).NumberFormat = "yyyy-mm-dd"

        .Cells(4, 1).Value = "Total Transactions:"
        .Cells(4, 2).Value = lastRow - 1
        .Cells(4, 2).NumberFormat = "#,##0"

        .Cells(5, 1).Value = "Total Sales:"
        .Cells(5, 2).Formula = "=SUM(RawData!E:E)"
        .Cells(5, 2).NumberFormat = "#,##0"

        .Cells(6, 1).Value = "Average Transaction:"
        .Cells(6, 2).Formula = "=AVERAGE(RawData!E:E)"
        .Cells(6, 2).NumberFormat = "#,##0"

        .Cells(7, 1).Value = "Maximum Transaction:"
        .Cells(7, 2).Formula = "=MAX(RawData!E:E)"
        .Cells(7, 2).NumberFormat = "#,##0"
    End With

    ' 2. Branch aggregation
    Call AggregateBySalesOffice(reportWs)

    ' 3. Product aggregation
    Call AggregateByProduct(reportWs)

    ' 4. Day-over-day analysis
    Call DayOverDayAnalysis(reportWs)

    Debug.Print "Data analysis complete"
End Sub

' ============================================
' Step 4: Generate AI Insights
' ============================================
Sub GenerateAIInsights()
    Dim reportWs As Worksheet
    Dim summary As String
    Dim insight As String

    Set reportWs = ThisWorkbook.Sheets("AnalysisResults")

    ' Collect summary data
    summary = "Total sales: " & reportWs.Cells(5, 2).Value & " won" & vbCrLf & _
              "Transactions: " & reportWs.Cells(4, 2).Value & " cases" & vbCrLf & _
              "Average transaction: " & reportWs.Cells(6, 2).Value & " won" & vbCrLf & _
              "Day-over-day: " & reportWs.Cells(8, 2).Value

    ' Request insights from AI
    insight = AskGPT( _
        "Analyze this daily sales data and " & _
        "write 3 key insights to report to executives:" & vbCrLf & summary)

    ' Add to report
    reportWs.Cells(20, 1).Value = "AI Insights"
    reportWs.Cells(20, 1).Font.Bold = True
    reportWs.Cells(20, 1).Font.Size = 12

    reportWs.Cells(21, 1).Value = insight
    reportWs.Cells(21, 1).WrapText = True
    reportWs.Range("A21:F25").Merge
    reportWs.Cells(21, 1).Interior.Color = RGB(217, 225, 242)

    Debug.Print "AI insights generation complete"
End Sub

' ============================================
' Step 5: Generate Report
' ============================================
Sub GenerateReport()
    Dim reportWs As Worksheet
    Dim chartObj As ChartObject

    Set reportWs = ThisWorkbook.Sheets("AnalysisResults")

    ' Create charts
    ' 1. Branch sales chart
    Set chartObj = reportWs.ChartObjects.Add( _
        Left:=reportWs.Range("G2").Left, _
        Top:=reportWs.Range("G2").Top, _
        Width:=350, _
        Height:=250)

    With chartObj.Chart
        .SetSourceData Source:=reportWs.Range("A11:B15")
        .ChartType = xlColumnClustered
        .HasTitle = True
        .ChartTitle.Text = "Sales by Branch"
        .HasLegend = False
    End With

    ' 2. Product sales chart
    Set chartObj = reportWs.ChartObjects.Add( _
        Left:=reportWs.Range("G18").Left, _
        Top:=reportWs.Range("G18").Top, _
        Width:=350, _
        Height:=250)

    With chartObj.Chart
        .SetSourceData Source:=reportWs.Range("D11:E15")
        .ChartType = xlPie
        .HasTitle = True
        .ChartTitle.Text = "Product Sales Proportion"
    End With

    ' Apply formatting
    With reportWs
        .Columns("A:F").AutoFit
        .Range("A1:F1").Interior.Color = RGB(68, 114, 196)
        .Range("A1:F1").Font.Color = RGB(255, 255, 255)
    End With

    Debug.Print "Report generation complete"
End Sub

' ============================================
' Step 6: Send Email
' ============================================
Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim reportWs As Worksheet
    Dim emailBody As String

    Set reportWs = ThisWorkbook.Sheets("AnalysisResults")

    ' Create email body
    emailBody = "<html><body>" & _
                "<h2>Daily Sales Report</h2>" & _
                "<p><b>Analysis Date:</b> " & reportWs.Cells(3, 2).Value & "</p>" & _
                "<p><b>Total Sales:</b> " & Format(reportWs.Cells(5, 2).Value, "#,##0") & " won</p>" & _
                "<p><b>Transactions:</b> " & Format(reportWs.Cells(4, 2).Value, "#,##0") & " cases</p>" & _
                "<p><b>Day-over-day:</b> " & reportWs.Cells(8, 2).Value & "</p>" & _
                "<hr>" & _
                "<h3>AI Insights</h3>" & _
                "<p>" & Replace(reportWs.Cells(21, 1).Value, vbCrLf, "<br>") & "</p>" & _
                "<hr>" & _
                "<p>Please see attached report for details.</p>" & _
                "</body></html>"

    ' Save report as file
    Dim reportPath As String
    reportPath = ThisWorkbook.Path & "\DailyReport_" & Format(Date, "yyyymmdd") & ".xlsx"

    ThisWorkbook.SaveCopyAs reportPath

    ' Send Outlook email
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    With OutMail
        .To = "manager@company.com"
        .CC = "team@company.com"
        .Subject = "Daily Sales Report - " & Format(Date - 1, "yyyy-mm-dd")
        .HTMLBody = emailBody
        .Attachments.Add reportPath
        .Send ' Or .Display (review before sending)
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing

    Debug.Print "Email sent successfully"
End Sub

' ============================================
' Auto-run Scheduler Setup
' ============================================
Sub SetupAutoRun()
    ' Run every day at 9 AM
    Application.OnTime TimeValue("09:00:00"), "DailyReport_Master"

    MsgBox "Daily report will be auto-generated every morning at 9 AM.", vbInformation
End Sub

Sub CancelAutoRun()
    On Error Resume Next
    Application.OnTime TimeValue("09:00:00"), "DailyReport_Master", , False
    MsgBox "Auto-run cancelled.", vbInformation
End Sub

2. Customer Data Management Workflow

Scenario: Collect customer information, segment with AI, and generate personalized marketing messages

Sub CustomerManagementWorkflow()
    ' 1. Collect new customer data
    Call CollectCustomerData

    ' 2. Merge duplicate customers
    Call MergeDuplicateCustomers

    ' 3. Segment customers with AI
    Call AI_CustomerSegmentation

    ' 4. Generate personalized messages
    Call GeneratePersonalizedMessages

    ' 5. Update CRM system
    Call UpdateCRM

    MsgBox "Customer management workflow complete.", vbInformation
End Sub

Sub AI_CustomerSegmentation()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("CustomerList")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' AI analyzes each customer profile
    For i = 2 To lastRow
        Dim customerInfo As String
        Dim segment As String

        customerInfo = "Age: " & ws.Cells(i, 3).Value & _
                      ", Purchase frequency: " & ws.Cells(i, 4).Value & _
                      ", Total purchases: " & ws.Cells(i, 5).Value & _
                      ", Last purchase: " & ws.Cells(i, 6).Value

        segment = AskGPT( _
            "Classify this customer as VIP/Regular/New/At-Risk based on:" & _
            vbCrLf & customerInfo)

        ws.Cells(i, 7).Value = segment
    Next i
End Sub

3. Integrated Automation Dashboard

Sub AutomationDashboard()
    Dim dashWs As Worksheet

    ' Create dashboard sheet
    On Error Resume Next
    Set dashWs = Sheets("AutomationDashboard")
    If dashWs Is Nothing Then
        Set dashWs = Sheets.Add
        dashWs.Name = "AutomationDashboard"
    Else
        dashWs.Cells.Clear
    End If
    On Error GoTo 0

    With dashWs
        ' Title
        .Cells(1, 1).Value = "Work Automation Dashboard"
        .Cells(1, 1).Font.Size = 16
        .Cells(1, 1).Font.Bold = True

        ' Workflow buttons
        Call CreateButton(dashWs, "C3", "Generate Daily Report", "DailyReport_Master")
        Call CreateButton(dashWs, "C5", "Customer Management", "CustomerManagementWorkflow")
        Call CreateButton(dashWs, "C7", "Inventory Management", "InventoryManagementWorkflow")
        Call CreateButton(dashWs, "C9", "Project Management", "ProjectManagementWorkflow")

        ' Settings buttons
        Call CreateButton(dashWs, "C12", "Setup Auto-Run", "SetupAutoRun")
        Call CreateButton(dashWs, "C14", "AI Settings", "AIPluginSettings")
        Call CreateButton(dashWs, "C16", "API Statistics", "ShowAPIStatistics")

        ' Status display
        .Cells(3, 6).Value = "Last Run:"
        .Cells(3, 7).Value = Now
        .Cells(3, 7).NumberFormat = "yyyy-mm-dd hh:mm"

        .Columns("A:H").AutoFit
    End With

    MsgBox "Automation dashboard created.", vbInformation
End Sub

Sub CreateButton(ws As Worksheet, cellAddress As String, caption As String, macroName As String)
    Dim btn As Button

    Set btn = ws.Buttons.Add( _
        ws.Range(cellAddress).Left, _
        ws.Range(cellAddress).Top, _
        150, 25)

    btn.OnAction = macroName
    btn.Caption = caption
    btn.Font.Bold = True
End Sub

Conclusion

Building fully automated office workflows:

• ✅ Auto-generate and send daily reports
• ✅ Automate customer/inventory/project management
• ✅ Support decision-making with AI insights
• ✅ Manage at a glance with integrated dashboard
• ✅ Ensure stability with logging and monitoring

Now you can build a fully automated office system using Excel and AI. Break free from repetitive tasks and focus on more creative work!