基于单元格日期值提前90天自动发送提醒邮件的技术问询

Got it, let's break this down into actionable, no-fuss solutions—whether you're using Excel or Google Sheets, we've got you covered. The goal is to send an email like Reminder birthday Jon Doe 01/11/2019 exactly 90 days before the target date (so August 1st for that example).

Solution 1: Excel with VBA (Using Outlook) #

If you're on Excel and have Outlook installed, this VBA script will handle the automation smoothly:

Step 1: Prep your spreadsheet #

First, structure your sheet clearly:

• Column A: Full Name (e.g., "Jon Doe")
• Column B: Target Date (e.g., "01/11/2019") — make sure this is formatted as a Date type, not plain text (text dates break calculations!).

Step 2: Add the VBA Script #

1. Press Alt + F11 to open the VBA Editor.
2. Right-click your workbook in the Project Explorer > Insert > Module.
3. Paste this code (tweak placeholders like sheet name/email address to match your setup):

Sub Send90DayReminders()
    Dim olApp As Object
    Dim olMail As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim targetDate As Date
    Dim reminderDate As Date
    
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Update to your sheet name
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Fire up Outlook
    Set olApp = CreateObject("Outlook.Application")
    
    For i = 2 To lastRow ' Start at row 2 if row 1 is headers
        targetDate = ws.Cells(i, "B").Value
        reminderDate = targetDate - 90
        
        ' Check if today is the exact reminder date
        If Date = reminderDate Then
            Set olMail = olApp.CreateItem(0)
            With olMail
                .To = "your-recipient@example.com" ' Update to your email
                .Subject = "Reminder: Upcoming Birthday for " & ws.Cells(i, "A").Value
                .Body = "Reminder birthday " & ws.Cells(i, "A").Value & " " & Format(targetDate, "dd/mm/yyyy")
                .Send ' Swap with .Display if you want to preview before sending
            End With
            Set olMail = Nothing
        End If
    Next i
    
    Set olApp = Nothing
    MsgBox "Reminder check finished!", vbInformation
End Sub

Step 3: Automate the script #

• Manual run: Press F5 in the VBA Editor, or add a button to your spreadsheet to trigger it with one click.
• Daily auto-run: Use Windows Task Scheduler to open the workbook and run the macro (just make sure Excel macros are enabled).

Solution 2: Google Sheets with Apps Script #

For Google Sheets users, Apps Script integrates seamlessly with Gmail for hands-off automation:

Step 1: Prep your spreadsheet #

Same structure as Excel:

• Column A: Full Name
• Column B: Target Date (formatted as Date)

Step 2: Add the Apps Script #

1. Open your sheet > Extensions > Apps Script.
2. Delete the default code and paste this (update placeholders):

function send90DayReminders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1"); // Update sheet name
  const data = sheet.getDataRange().getValues();
  const today = new Date();
  today.setHours(0, 0, 0, 0); // Reset time to midnight for accurate date matching

  for (let i = 1; i < data.length; i++) { // Skip header row (row 1)
    const name = data[i][0];
    const targetDate = new Date(data[i][1]);
    const reminderDate = new Date(targetDate);
    reminderDate.setDate(targetDate.getDate() - 90);
    reminderDate.setHours(0, 0, 0, 0);

    // Check if today's date matches the reminder date
    if (today.getTime() === reminderDate.getTime()) {
      const recipient = "your-recipient@example.com"; // Update email
      const subject = `Reminder: Upcoming Birthday for ${name}`;
      const body = `Reminder birthday ${name} ${Utilities.formatDate(targetDate, Session.getScriptTimeZone(), "dd/mm/yyyy")}`;
      
      GmailApp.sendEmail(recipient, subject, body);
    }
  }
}

Step 3: Set up a daily trigger #

1. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar.
2. Click "Add Trigger" and configure:
   • Function: send90DayReminders
   • Event source: Time-driven
   • Trigger type: Day timer
   • Time of day: Pick a convenient time (e.g., 9:00 AM)
3. Save the trigger—Google will run the script daily automatically.

Quick Pro Tips #

• Double-check date formatting: Always confirm your target date cells are actual dates, not text—this avoids calculation bugs.
• Test first: Use a test date that's exactly 90 days from today to verify the email sends correctly before relying on the automation.
• Adjust the email content: Tweak the .Body (VBA) or body (Apps Script) lines to match your exact reminder wording.

内容的提问来源于stack exchange，提问作者Tod