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?
Recommended Excel Productivity Tools
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.
Recommended Productivity Tools for Excel
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 9 months agoThis is close to what I need but I am stuck with two problems. 1st I need to run this across my entire workbook that gets new worksheets added daily. 2nd I need it to include the worksheet name in the email so I will know which sheet is due. I create new tickets (worksheets) with a field that is a future date, when that day arrives I need an email. Thank you for your help.
To post as a guest, your comment is unpublished.· 11 months agoHow do I get VBA in Excel to generate an email when a cell changes color? I am able to use formula to have cells' font change, based on the computer clock, when a date is within a range before expiry. I currently have a spreadsheet with 25 employee's names and the dates of expiry for their driver's license, work permits, entry/exit visa dates, and annual start date. Some dates are 30 days, some are 60 days. Whenever a date enters the 30 (or 60) day range before expiry, the value in the cell changes from normal black to bold red font. I want to be able to have an email sent to me warning that the person in A1...A2...A3, etc. is about to have a work document expire. What is the script I need to run to accomplish this?
To post as a guest, your comment is unpublished.· 11 months agoHello, here is an article maybe can help you. https://www.extendoffice.com/documents/excel/4661-excel-send-email-if-cell-is-modified.html