时间戳

首页 > 解决方案 > 如果每个收件人都满足某个单元格值，则向收件人发送电子邮件

google-sheets - 如果每个收件人都满足某个单元格值，则向收件人发送电子邮件

问题描述

基本上，我使用 Google 表格创建了一个发票跟踪器，并且我想在他们的发票到期时向我的每个客户发送一封提醒电子邮件。我已经设置了日期和倒计时，现在我想在单元格值达到“2”时向他们发送提醒电子邮件，这意味着自我为他们开具发票以来已经过去了 32 天。

我已经从网上收集了不同来源的代码，并且我设置了一个 24 小时触发器以每天运行一次代码。电子邮件模板也已到位。每个客户的数据（日期、姓名、地址等）都列在单独的行中。

我的问题是，邮件应用程序不会向正确的客户发送一封电子邮件，而是在其中任何一个有到期发票时向所有客户发送电子邮件！

我不确定我应该使用哪个函数或代码。我尝试了“Email_Sent”的东西，但对它没有任何好处！

function CheckMaturity() {
  // Fetch invoice maturity
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('InvoiceTracker').activate();   
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    for (var i = 5;i<=10;i++){
      var invoiceMaturityRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('InvoiceTracker').getRange(i, 13); 
      var invoiceMaturity = invoiceMaturityRange.getValue();
      // Check invoice maturity
        if (invoiceMaturity = 2){
        // Fetch the email address
          SpreadsheetApp.getActiveSpreadsheet().getSheetByName('InvoiceTracker').activate();

          var templateText = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('EmailTemplate').getRange(1,1).getValue();

          var currentAddress = ss.getRange(i, 15).getValue(); 
          var currentInvoiceNo = ss.getRange(i, 3).getValue(); 
          var currentInvoiceDate = ss.getRange(i, 4).getValue(); 
          var currentClient = ss.getRange(i, 14).getValue();
          var messageBody = templateText.replace('{client}',currentClient).replace('{invoiceNo}',currentInvoiceNo).replace('{invoiceDate}', currentInvoiceDate);
          var subjectLine = 'Kind reminder - Invoice status';

          MailApp.sendEmail(currentAddress, subjectLine, messageBody);{
          SpreadsheetApp.getActiveSpreadsheet().toast('Invoice reminder sent to' +currentClient, 'Reminder sent', -1);

          }

       }
    } 
}

我希望该应用程序仅向正确的（相关）客户端发送一封电子邮件。

标签： google-sheetsgoogle-sheets-apigoogle-sheets-macros

解决方案

我认为您需要以下内容。请检查变量和引用。应调整以下代码。应将“A”列替换为您拥有最后一条记录的列，以防止您错过任何客户。此外，请检查下面代码中的注释。

.Range("A1047854").End(xlUp).Row

特此完整代码：

Sub SendEmails()
    Dim myOlApp As Outlook.Application, MailItem As Outlook.MailItem
    Dim attachmentPath1 As String, attachmentPath2 As String
    Set myOlApp = CreateObject("Outlook.Application")

    'loop through a sheet (change index)
    For i = 1 To ThisWorkbook.Sheets("index").Range("A1047854").End(xlUp).Row
        'set key for check (or just do it directly in the if)
        invoiceMaturity = ThisWorkbook.Sheets("index").Range("A" & i).Value
        If invoiceMaturity = "2" Then
            'you can load the variables first, before adding them to the email, or add them directly.
            Name = ""
            MailAddress = ""
            Address = ""
            currentInvoiceNo = ""
            currentInvoiceDate = ""
            currentClient = ""

            'make item for each iteration (again)
            Set MailItem = myOlApp.CreateItem(olMailItem)
            'attachments
            attachmentPath1 = "path/to/file.something" 'or set to ""(nothing)
            'body
            MailItem.HTMLBody = "<B>" & "<h3>" & "DRAFT:" & "</h3>" & "</B>" & "<br>" & _
                "Dear, " & "<br>" & "<br>" & _
                "Please find enclosed a kind reminder.." & "<br>" & "<br>" & _
                "Please note, that.." & "</b>" & "<br>" & "<br>" & _
                "Should you have any questions or comments on the above, please do let us know." & "<br>" & "<br>" & _
                "Kind regards," & "<br>" & "<br>" & _
                "Signature"

            MailItem.to = MailAddress  'adjust email
            MailItem.Subject = "[subject of email" & "a variable?" 'adjust subject
            MailItem.Show 'or mailitem.send

            'just to make sure
            Set MailItem = ""

        End If
    Next i
End Sub

推荐阅读