A Step-By-Step Guide For Beginners
By Founder of Backbone: Ng Xian Hui
Originally posted on Chartered Accountants Lab (ISCA)
TAKEAWAYS
- VBA (Visual Basic for Applications) can automate more than just tasks in a workbook; it can integrate with other applications, such as Outlook, to streamline processes like sending emails.
- Automating email processes can save time, improve accuracy, and ensure consistent communication.
- Structuring the data properly is essential to reap maximum benefits. Structuring the data in a named table with clear column headers will ensure flexibility and scalability, enabling the macro to adapt to changes seamlessly.
VBA (Visual Basic for Applications) is a programming language and environment embedded within most Microsoft Office products, including Excel and Outlook. This means VBA can automate more than just tasks within a workbook; it can also integrate with other applications, such as Outlook, to streamline processes like sending emails. For accountants who need to share reports regularly, automating email processes can save time, improve accuracy, and ensure consistent communication.
In this article, we will explore how VBA can be used to automate the sending of emails directly from Excel. This approach helps maintain a seamless workflow and eliminates the repetitive task of manually composing emails and attaching files.
PREREQUISITES
To send emails using VBA, you will need to ensure the following:
1. Microsoft Outlook (the classic Outlook) installed
VBA interacts with Outlook to send emails, so Outlook must be installed and properly configured on your computer. Microsoft has released a new Outlook for Windows, which does not support VBA. To ensure that VBA works, make sure the “Try the new Outlook” toggle button in your Outlook is set to Off.
2. Developer tab enabled
If the Developer tab is not enabled, you can follow the steps outlined in “Automating the Final Mile of Report Generation with VBA for Beginners”, to activate it.
Note: Macro recording will not work for this task. While Excel’s macro recorder can capture most actions performed within Excel, it cannot record inter-application actions, such as those involving Outlook or other programmes.
PREPARE DATA IN EXCEL
Before diving into VBA coding, it is crucial to prepare your data in Excel so that your macro can dynamically use it to send emails. Here’s how to structure your data effectively:
1. Create a table
Set up a worksheet in Excel to include all the necessary details for your email automation. Here’s an example of how your table might look like this:
- Recipient Email: Enter the email addresses of the recipients.
- Subject: Include the subject line for the email.
- Body: Write the content of the email.
- Attachment Path: Provide the file path for any attachments.
- Status: Leave the column blank for now. It will be used later to track the status of the process.
2. Format the data as table
To make your VBA code easier to manage, format the data as table and give it a meaningful name.
- Highlight the table
- Format as table
Go to the Insert tab on the ribbon and click the Table icon (see screenshot).
In the dialog box, ensure the range is correct and check the box for My table has headers.
Click OK to create table.
- Name the table
With the table selected, go to the Table Design tab (appears when the table is selected).
Locate the Table Name box in the Properties group (top-left corner of the ribbon).
Enter a meaningful name (EmailData) and press Enter.
Why use a named table?
Using a named table like EmailData instead of fixed cell ranges ensures flexibility and scalability:
- Dynamic range: A named table automatically adjusts to include new rows of data when you add them. This eliminates the need to manually update your VBA code for changes in the data size.
- Ease of reference: Named tables and columns make your VBA code more readable and maintainable. Instead of hardcoded cell references, you can reference data by column names, reducing errors and making the code self-explanatory.
- Future-proof: Even if the structure of your data changes (for example, column order), the code remains intact because it uses column headers, not fixed locations, to retrieve data.
INSERT VBA CODE
Before you can write or edit VBA code, you need to access the VBA editor. Follow these steps:
1. Open the VBA editor
You can press the shortcut Alt + F11 to open the VBA editor directly.
2. Insert a new module
To start writing your VBA code:
- In the VBA editor, go to the Insert menu.
- Select Module.
- A blank code window will appear where you can write or paste your VBA script.
3. Paste the below VBA script
Sub SendEmails()
Dim OutlookApp As Object
Dim OutlookMail As Object
Dim tbl As ListObject
Dim row As ListRow
Dim attachmentPath As String
‘ Initialise Outlook application
Set OutlookApp = CreateObject(“Outlook.Application”)
‘ Reference the named table
Set tbl = ThisWorkbook.Sheets(“Sheet1”).ListObjects(“EmailData”)
‘ Replace “Sheet1” with your sheet name and “EmailData” with the table name
‘ Loop through each row in the table
For Each row In tbl.ListRows
‘ Create a new email
Set OutlookMail = OutlookApp.CreateItem(0)
‘ Compose the email
With OutlookMail
.To = row.Range(tbl.ListColumns(“Recipient Email”).Index).Value
.Subject = row.Range(tbl.ListColumns(“Subject”).Index).Value
.Body = row.Range(tbl.ListColumns(“Body”).Index).Value
‘ Attach the file if the file path is valid
attachmentPath = row.Range(tbl.ListColumns(“Attachment Path”).Index).Value
If Dir(attachmentPath) <> “” Then
.Attachments.Add attachmentPath
End If
.Send ‘ Use .Display instead of .Send to preview the email before sending
End With
‘ Update the status column
row.Range(tbl.ListColumns(“Status”).Index).Value = “Sent”
‘ Clean up the mail object
Set OutlookMail = Nothing
Next
‘ Clean up the Outlook application
Set OutlookApp = Nothing
MsgBox “Emails sent successfully!”
End Sub
RUN THE MACRO
1. Insert a button
In the Developer tab, click on Insert, then select the Button (Form Control) icon from the dropdown menu.
2. Place the button
Click anywhere on your spreadsheet to place the button. A dialog box will appear asking you to assign a macro.
3. Assign the macro to the button
Select “SendEmails” to assign the macro to the button. Click OK to link the macro to the button.
4. Edit the button text
By default, the button will have generic text like “Button 1”.
To change it, right-click on the button and select “Edit Text”.
Enter a descriptive name for the button, such as “Send Emails”.
5. Run the macro
Click the button to execute the assigned macro.
CONCLUSION
Automating email sending with VBA streamlines workflow, saves time, and minimises manual errors. By structuring your data in a named table with clear column headers, you ensure flexibility and scalability, enabling the macro to adapt to changes seamlessly. Whether you’re sending financial reports, invoices, or updates, this approach simplifies the process, making repetitive tasks effortless.
