Automation using VBA (Visual Basic for Applications) in Excel involves using VBA programming language to create macros, which are sets of instructions that automate repetitive tasks in Excel. Some common examples of tasks that can be automated using VBA in Excel include data entry, report generation, and formatting.

The benefits of automation using VBA in Excel include increased efficiency, improved accuracy, and the ability to complete complex tasks in a shorter amount of time. Additionally, by automating repetitive tasks, it frees up time for employees to focus on higher-level tasks that require critical thinking and decision-making skills.

In this data digest, I will walk you through the following highlighted below:

1. Activate the developer tab in Excel
2. Automating report sending with VBA
3. Creating a send button for the operation

Note: The popular tableau superstore dataset will be leveraged for the purpose of this tutorial.

Let’s get started..

Activate the developer tab in Excel

To activate the Developer tab in Excel, follow these steps:

• First, right-click on any of the existing tabs on the ribbon
• This opens a menu of options, and we want to select Customize the Ribbon

• Then, select the Developer checkbox and click OK

• The Developer tab is now visible

Note: Once the Developer tab is activated, you can access Visual Basic Editor (VBE) by clicking on the "Developer" tab and then clicking on the "Visual Basic" button in the "Code" section.

Automating report sending with VBA

This is a report that has been generated for different agent from the tableau dataset, I will be automating the mailing of this report to concerned stakeholders via mail in out look.

• First, call out or declare the required variable required to automate the report sending.

'This section converts the report into a picture
Sub send_email_with_table_as_pic()

'Outlook mail application
Dim OutApp As Object
Dim OutMail As Object

'Report table as a range
Dim table As Range

Dim pic As Picture
Dim pic1 As Picture

'The worksheet
Dim ws As Worksheet

'Other useful variables declaration
Dim wordDoc
Dim c As Range
Dim range1 As Range
Dim range2 As Range
Dim range3 As Range
Dim irow As Long
Dim Ldate As String
Dim sheet As Worksheet
Dim ccmail As String
Dim dCurrent_Monday As Date
Dim Tdate As Date

• The report above will be sent to the different sales manager in different regions as shown below. This worksheet in the workbook is titled “people”

• After declaration of the variables, I will write a macro that iterates over each sales manager by region within the range “c2: c4” in the worksheet titled “people”. Also, the code will also contain the condition that takes into account null cells in a bid to avoid error generation when email is not found in a cell.

For Each c In ThisWorkbook.Worksheets("People").Range("c2:c4").Cells

If (c.Value) <> "" Then

• The next operation is to open the outlook app/mail and setting the workbook in order to avoid running into error, since each sales manager has to get the report automatically.

Note: This means using a macro to automatically change the text (names in the dropdown of the report) in the report in alignment to respective sales manager

'Setting/opening outlook application
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

'Setting worksheet to automatically select each sales manager
Set ws = ThisWorkbook.Sheets("Report")
ws.Range("d2").Value = c.Offset(0, -2).Value

• Due to excel application delay, I will be adding application wait code block in order to cater for the backlog in loading the different report.

Application.Wait (Now + TimeValue("0:00:7"))

• Next, set the table to the width of the report shown in the worksheet above

Set table = ws.Range("A1:i20")
table.Font.Size = 12

• Next, setting the date format. Since the report will be sent for the previous days.

Ldate = Format(Now() - 1, "mmmm dd, yyyy")

• Formatting the email message and outline of the mail with HTML

'used in the body of the mail
'create email message
On Error Resume Next

With OutMail
.to = c.Value '& ";" & ccmail
.CC = ""
.BCC = ""
.Subject = "RM Report -" & " " & Format(Date, "DD-MM-yy")

Set wordDoc = OutMail.GetInspector.WordEditor
With wordDoc.Range

			.PasteandFormat wdChartPicture
			.insertParagraphAfter
			
			table.Copy
			
			wordDoc.Paragraphs(1).Range.PasteSpecial xlPasteFormats, , False, False
			wordDoc.Content.insertParagraphAfter
			.insertParagraphAfter
			.insertParagraphAfter
			
			End With
			.display
			.HTMLBody = "" & _
			"Hi " & "" & c.Offset(0, -2).Value & "," & "<p>" & "Please see below your Sales performance as of " & Ldate & " and the recommended actions for your DSRs;" & "<p>" & " <p> " & .HTMLBody
			
			'Adding time for buffer
			Application.Wait (Now + TimeValue("0:00:3"))
			.send
End With

On Error GoTo 0
	End If

• Next, after creating the body of the email and formatting it properly, then I will write a code that allows the same operation to be performed for the next email or sales manager

Application.CutCopyMode = False
		Next c

• Finally, the code above iterates over all available mail of the sales managers and sends them the report and message in alignment with their region. Then, I will be closing the outlook application after the operation is completed.

		MsgBox "Completed, please proceed"
Set OutApp = Nothing
Set OutMail = Nothing

End

Creating a send button for the operation

• First, go to the developer tab and select “insert”. Click on the option to create button

Note: This kind of button is important in case the macro is to be used by someone who doesn’t understand how to run or manage the VBA. Basically for usability for non-technical users

• Drag the button to where you want it to be placed and the visual basic studio opens up for your code.

Private Sub CommandButton1_Click()

			Dim msgValue As VbMsggBoxResult
			msgValue = MsgBox("Are you ready to send mail?" vbYesNo + vbQuestions, "Save")
			
				if msgValue = vbYes Then
						call send_email_with_table_as_pic()

• The button can the customized for aesthetics purpose

• Lastly, click on the button to activate the operation and the report with message body will be sent to the respective sales managers.

Conclusion

VBA can be an effective tool for automating report sending in various contexts, particularly if you are working with Microsoft Excel or other Microsoft Office applications. With VBA, you can create macros that automate repetitive tasks, such as generating reports, formatting data, and sending emails.

However, using VBA for automation does require some technical knowledge and programming skills, so it may not be suitable for everyone. It's also important to be aware of potential security risks, such as malicious code or unauthorized access to sensitive data.

If you want to explore more on building your Microsoft Excel skill, visit our learning path HERE

Also, if you want to get started with data analytics and looking to improving your skills, you can check out our Learning Track