Any alternative to CDO.message for sending emails through gmail using VBA

Question

when CDO.message (SMTP server) VBA code is run it checks if that gmail ID (from which we are sending email) is linked with the current system or not. If it is run on a new system where we never logged in with that gmail id then it gives sever failing error and email is not sent. So I want ask some other way with code (may be gmail api) which does not check for system's link with gmail ID. BELOW IS THE CODE THAT I AM USING

 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"" <Reply@something.nl>"
    .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

Answer 1

Apps Script allows you to get information from Spreadsheets and other Google Documents and be able to use it to then send emails using its Gmail services under certain conditions met on these Spreadsheets for instance.

The following example is a simplification of your scenario where if two numbers / values do not match then you send an email to notify that the system has been run with another device. Below is the code with self-explanatory comments and an image representing the Spreadsheet I am using for this example.

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]);
    }
  }
}

Resources used: Gmail App and SpreadsheetApp