Automated Excel Reporting and Mailing with VBA

Objective:

The objective of this project is to automate the process of creating excel reports, in this case, a “Daily Sales” report generated at the end of every business day. The report includes the total number of sales and total number of orders for that day, based on date prompt. The report should be generated in excel, converted to PDF format, and sent via email using Microsoft Outlook.

Problem Statement:

The manual process of creating daily sales report is time consuming and prone to errors. By automating this process using VBA, we aim to save time, improve accuracy and streamline the reporting and mailing process.

Requirements:

• Excel 2016 and above

• Microsoft Outlook

Deliverables:

1. VBA code that creates a daily sales report based on the provided date prompt

2. A new Excel sheet containing the daily sales report, including the total sales and total number of orders.

3. Conversion of the daily sales report sheet to PDF format.

4. An email containing the PDF attachment sent to the specified recipient using Microsoft Outlook.

Master Data Sheet:

This is our data sheet containing all necessary data

Upon running the VBA macro, the date input prompt pops up …

Prompt for Date Input:

The program asks the user to input a specific date for which the daily sales report needs to be generated.

Search for Date in Master Data:

The VBA code applies a filter in the master sheet for the corresponding column using the date input as criteria

Calculate Total Sales and Total Number of Orders:

Using the filtered data, the VBA calculates the total sales by summing up the sales values, and also counts the total number of orders for the specified date as well.

Creating a New Sheet for Daily Sales Report:

The VBA code cleares the filter in the master sheet and creates a new sheet in the the Excel workbook, to hold the daily sales report.

Populating the Report Sheet:

The VBA populates the newly created sheet with the following information. • Date: the specified date • Total Sales: calculated total sales for the specified date. • Total Number of Orders: The calculated total number of orders for the specified date. It also properly formats all cells, figures and columns.

Convert the Daily Sales Report to PDF:

The VBA code converts the created sheet into PDF format ready to be mailed.

Send Email Via Outlook:

• The VBA code uses Microsoft Outlook Application to send an email to the recipient. • The email includes the PDF file as an attachment • The recipient’s email address is specified in the code.

Further Development:

The current code implementation can be extended to handle more complex requirements and accommodate additional functionalities. Such as:

1. Date Range Input: The code could be further developed to accept date ranges as input, allowing the generation of sales reports for a specific range of dates. This would provide a more comprehensive view of sales trends and performance over a specific period.

2. Sending Emails to Multiple Recipients: To increase the flexibility of the system, the code could be improved to support sending the daily sales report to multiple recipients. This would enable efficient distribution of the report to different stakeholders or departments within the organization.

3. More complicated report types.

VBA Code:

Sub GenerateDailySalesReport()
Dim inputDate As Date
Dim wsData As Worksheet
Dim wsTemp As Worksheet
Dim wsReport As Worksheet
Dim lastRow As Long
Dim filteredRange As Range
Dim totalSales As Double
Dim orderCount As Long

' Ask for the input date
inputDate = InputBox("Enter the order date (dd/mm/yyyy):")

' Set the data sheet
Set wsData = ThisWorkbook.Worksheets("Master Sales Data")

' Set the report sheet or create a new one
On Error Resume Next
Set wsReport = ThisWorkbook.Worksheets("Daily Sales Report")
On Error GoTo 0

If wsReport Is Nothing Then
    Set wsReport = ThisWorkbook.Worksheets.Add
    wsReport.Name = "Daily Sales Report"
Else
    wsReport.Cells.Clear
End If

' Turn off the filter
wsData.AutoFilterMode = False

' Create a temporary sheet
Set wsTemp = ThisWorkbook.Worksheets.Add
wsTemp.Name = "Temp"

' Find the last row in the data sheet
' lastRow = wsData.Cells(wsData.Rows.Count, "A").End(xlUp).Row

' Filter the data for the input date and copy to the temporary sheet
wsData.Range("A1:R9801").AutoFilter Field:=3, Criteria1:=inputDate
wsData.Range("A1:R9801").SpecialCells(xlCellTypeVisible).Copy Destination:=wsTemp.Range("A1")

' Get the filtered range in the temporary sheet
Set filteredRange = wsTemp.UsedRange

' Calculate the total sales and order count from the filtered range
totalSales = Application.WorksheetFunction.SumIf(filteredRange.Columns("R"), ">0")
orderCount = filteredRange.Columns("B").Cells.Count - 1

' Turn off the filter
wsData.AutoFilterMode = False

' Create the report headers
wsReport.Range("A1").Font.Bold = True
wsReport.Range("A2:B2").Font.Bold = True

wsReport.Range("A1") = "Daily Sales Report for " & Format(inputDate, "mm/dd/yyyy")
wsReport.Range("A2") = "Total Sales"
wsReport.Range("B2") = "Number of Orders"

' Insert the calculated values
wsReport.Range("A3").NumberFormat = "$#,##0.00"
wsReport.Range("A3") = totalSales
wsReport.Range("B3") = orderCount

' Autofit columns
wsReport.Columns("A:B").AutoFit

' Save the report as PDF
Dim fileName As String
fileName = "Daily_Sales_Report_" & Format(inputDate, "yyyymmdd") & ".pdf"
Dim filePath As String
filePath = Environ("TEMP") & "" & fileName ' Use the user's temporary folder path

wsReport.ExportAsFixedFormat Type:=xlTypePDF, fileName:=filePath, Quality:=xlQualityStandard

' Create Outlook objects
Dim OutlookApp As Outlook.Application
Dim OutlookMail As Outlook.MailItem

' Initialize Outlook
Set OutlookApp = New Outlook.Application
Set OutlookMail = OutlookApp.CreateItem(olMailItem)

' Compose the email
With OutlookMail
    .BodyFormat = olFormatHTML
    .Display
    .HTMLBody = "Dear Sir" & "<br>" & "<br>" & "Please find the attached file." & "<br>" & "<br>" & .HTMLBody
    .To = "email@gmail.com"
    ' .CC = "xyz@gmail.com"
    .Subject = "Daily Sales Report - " & Format(inputDate, "mm/dd/yyyy")
    .Attachments.Add filePath ' Attach the PDF from the temporary folder
    .Send
End With

' Release the Outlook objects from memory
Set OutlookMail = Nothing
Set OutlookApp = Nothing

' Delete the temporary sheet
Application.DisplayAlerts = False
wsTemp.Delete
Application.DisplayAlerts = True


' Show a message with the file path
MsgBox "Daily sales report created and sent via email.", vbInformation

End Sub

Thank you.