Automatically Sending Email Completed Task Table in Excel using VBA
In this article, we will discuss how to automatically send an email with a completed task table in Excel using VBA. This is a powerful tool that can help you save time and increase productivity by automating the process of sending task updates to your team or clients.
1. Enable Developer Tab
To get started, you need to enable the Developer tab in Excel. Here's how:
- Go to File > Options.
- Click on Customize Ribbon.
- Check the box next to Developer in the right column.
- Click OK.
2. Create a Sub to Send Email
Next, you need to create a subroutine that will send the email. Here's an example code:
Sub SendEmail()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Completed Task Table"
.Body = "Please find the completed task table attached."
.Attachments.Add ActiveWorkbook.FullName
.Send
End With
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
This code creates a new email with the specified recipients, subject, and body. It also attaches the active workbook and sends the email.
3. Create a Sub to Generate the Task Table
Now, you need to create a subroutine that will generate the task table. Here's an example code:
Sub GenerateTaskTable()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Set rng = ws.Range("A1:D" & lastRow)
With rng
.Sort Key1:=.Columns(1), Order1:=xlAscending, Header:=xlYes
.AutoFilter Field:=1, Criteria1:="Completed"
End With
ws.Range("A1:D1").Copy
ws.Range("G1").PasteSpecial xlPasteValues
ws.Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Copy
End Sub
This code sorts the table by the first column and filters it to show only the completed tasks. It then copies the header and visible cells to a new range.
4. Combine the Subs
Finally, you need to combine the two subroutines to automatically send the completed task table in an email. Here's an example code:
Sub SendCompletedTaskTable()
Call GenerateTaskTable
Call SendEmail
End Sub
This code first calls the GenerateTaskTable subroutine to generate the task table. It then calls the SendEmail subroutine to send the email with the attached task table.
References
Type: Online Resource
Title: "How to Send Email from Excel Using VBA"
Author: Chandoo
URL: https://chandoo.org/wp/2011/11/16/send-email-from-excel-vba/Type: Book
Title: "Excel VBA Programming for Dummies"
Author: John Walkenbach
Publisher: Wiley
Year: 2013Type: Online Resource
Title: "How to Create a Task List in Excel"
Author: Vertex42
URL: https://www.vertex42.com/ExcelTemplates/task-list.html
By using the above steps, you can easily automate the process of sending completed task tables in Excel using VBA. This will save you time and increase productivity, allowing you to focus on more important tasks.