How to quickly send email based on date in Excel cell?
Have you ever tried to send an Email based on date in Excel cell? For example, here is a list of dates in column A, and then you want to send an Email with the subject, message body to a recipient (also can CC and BCC to others), how can you handle it?
To send email based on the date in Excel, you only can apply a macro code.
1. Enable the sheet that contains data and date you use, and press Alt + F11 keys to enable the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste below code to the blank script.
VBA: Send by date
Sub email() 'UpdatebyExtendoffice20170831 Dim xRg As Range Dim xRgEach As Range Dim xAddress As String Dim xEmail_Subject, xEmail_Send_From, xEmail_Send_To, xEmail_Cc, xEmail_Bcc, xEmail_Body As String Dim xMail_Object, xMail_Single As Object On Error Resume Next xAddress = ActiveWindow.RangeSelection.Address Set xRg = Application.InputBox("Select a range:", "KuTools For Excel", xAddress, , , , , 8) If xRg Is Nothing Then Exit Sub For Each xRgEach In xRg If xRgEach.Value = Date Then xEmail_Subject = Application.InputBox("Subject: ", "Kutools", , , , , , 2) xEmail_Send_From = Application.InputBox("Send from: ", "KuTools For Excel", , , , , , 2) xEmail_Send_To = Application.InputBox("Send to: ", "KuTools For Excel", , , , , , 2) If xEmail_Send_To = "" Then Exit Sub xEmail_Cc = Application.InputBox("CC: ", "KuTools For Excel", , , , , , 2) xEmail_Bcc = Application.InputBox("BCC: ", "KuTools For Excel", , , , , , 2) xEmail_Body = Application.InputBox("Message Body: ", "KuTools For Excel", , , , , , 2) Set xMail_Object = CreateObject("Outlook.Application") Set xMail_Single = xMail_Object.CreateItem(0) With xMail_Single .Subject = xEmail_Subject .To = xEmail_Send_To .cc = xEmail_Cc .BCC = xEmail_Bcc .Body = xEmail_Body .Send End With End If Next End Sub
3. Click Run button or press F5 key to execute the code, a dialog pops out to remind you to select a list of date. See screenshot:
4. Click OK, and continue to specify the email subject, sender’s email address, receiver’s email address, the CC, BCC, and message body. See screenshot:
5. Click OK > OK until last one, and then the email has been sent.
Note: the sender’s email address must be the default account in your Outlook.
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)