vba - 使用 VBA 通过 gmail 发送电子邮件的 CDO.message 的任何替代方案

Question

当 CDO.message（SMTP 服务器）VBA 代码运行时，它会检查该 gmail ID（我们从中发送电子邮件）是否与当前系统链接。如果它在我们从未使用该 gmail id 登录的新系统上运行，那么它会给出服务器失败错误并且不会发送电子邮件。所以我想用其他方式询问代码（可能是gmail api），它不会检查系统与gmail ID的链接。以下是我正在使用的代码

 Dim iMsg As Object
Dim iConf As Object
Dim strbody As String
Dim Flds As Variant
Dim email As String
Dim pass As String
Dim CN As String
Dim OS As String
Set iMsg = CreateObject("CDO.Message")
Set iConf = CreateObject("CDO.Configuration")

iConf.Load -1
Set Flds = iConf.Fields

With Flds

    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = FF
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpaccountname") = "abcd"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = DD
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    .Update
    
End With

 With iMsg
    Set .Configuration = iConf
    .To = FF
    .CC = ""
    .BCC = ""
    .From = """from"" <[e-mail]>"
    .Subject = UN & " C1 LOGGED IN"
    .TextBody = "COMPUTER NAME IS -" & CPN & ", USERNAME NAME IS -" & UN & ", COMPUTER ID IS -" & sAns
    .Send
    
End With

Set iMsg = Nothing
Set iConf = Nothing
Set Flds = Nothing

Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic

标签： vbagmail-api

Answer 1

解决方案

Apps 脚本允许您从电子表格和其他 Google 文档中获取信息，并能够使用它然后在这些电子表格上满足的某些条件下使用其 Gmail 服务发送电子邮件。

以下示例是您的场景的简化，如果两个数字/值不匹配，则您发送电子邮件通知系统已与另一台设备一起运行。下面是带有不言自明的注释的代码和代表我在此示例中使用的电子表格的图像。

function myFunction() {
  // Get the sheet we will be using
  var ss = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  // get the values of the range that contains the content
  // flat is used to get the 2D array returned by getValues() into a simple
  // 1D array with these values
  var content = ss.getRange('A1:A3').getValues().flat();
  // get the values of the range that contains the condition
  var condition1 = ss.getRange('B1:B3').getValues().flat();
  var condition2 = ss.getRange('C1:C3').getValues().flat();
  // get the values of the range that contains the email address
  var email = ss.getRange('D1:D3').getValues().flat();
  
  // iterate over all the values of the content column
  for(i=0;i<content.length;i++){
  // if the column B and C have different values in the row
    if(condition1[i]!=condition2[i]){
    // send emails with the appropiate properties
      GmailApp.sendEmail(email[i], 'Generated email', content[i]);
    }
  }
}

使用的资源：Gmail App和SpreadsheetApp

我希望这对你有所帮助。让我知道您是否需要其他任何内容，或者您​​是否不理解某些内容。:)