0

Send Email no error, but don't work

let today := today();
for i in select INVOICE do
    if i.'BALANCE DUE' > 0 and i.'DUE DATE' - 7 = today and i.Reminder = null then
        let message := "Estimado cliente, su factura #" + i.'INVOICE No' + " con saldo pendiente de " +
            text(i.'BALANCE DUE') +
            " vence el " +
            text(i.'DUE DATE') +
            ". Le agradecemos su pronto pago. Gracias.";
        sendEmail({
            from: userEmail(user()),
            to: i.CUSTOMER2.'CUSTOMER EMAIL',
            subject: "Recordatorio de pago - Factura #" + i.'INVOICE No',
            text: message
        });
        i.(Reminder := true)
    end
end

Some idea.

I don't is thus work  to: i.CUSTOMER2.'CUSTOMER EMAIL', 

but if place my Email to: rafael.sanchis@gmail.com, doesn't send it either.

Thanks

11 replies

null
    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 wk ago
    • Reported - view

    The script I provide is intended to send a reminder email to customers whose invoices are close to due (5 days before the due date) and who still have an outstanding balance.

    • John_Halls
    • 2 wk ago
    • Reported - view

    Hi 

    This has caught a few people out

    You now need to include the html: element, even if you are not using html. Try adding

    html: null,

    to your code.

    Do you mind if I offer a simpler version of your code

    for i in select INVOICE where 'BALANCE DUE' > 0 and 'DUE DATE' - 7 = today() and Reminder = nulldo
       let message := "Estimado cliente, su factura #" + i.'INVOICE No' + " con saldo pendiente de " +
       text(i.'BALANCE DUE') +
       " vence el " +
       text(i.'DUE DATE') +
       ". Le agradecemos su pronto pago. Gracias.";
       sendEmail({
          from: userEmail(user()),
          to: i.CUSTOMER2.'CUSTOMER EMAIL',
          subject: "Recordatorio de pago - Factura #" + i.'INVOICE No',
          text: message,
          html: null
            });
            i.(Reminder := true)
        end
    end
    

    using where in a select is more efficient as it doesn't select every record that then needs qualifying, only those that match in the first place.

    Regards John

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 wk ago
      • Reported - view

       

      This has caught a few people out 😂, don't bellive 

      Thanks John, but the button don't work, does nothing. I wasted time on this.

      I add in the last line   alert("✅ Proceso finalizado.") When click button appear the message but nothing send.

    • Rafael Sanchis
    • Rafael_Sanchis
    • 2 wk ago
    • Reported - view

    This lines works ok

    sendEmail({
        from: userEmail(user()),
        to: "rafael.sanchis@gmail.com",
        subject: "Recordatorio de pago - Factura #" + 'INVOICE No',
        text: "",
        html: "null"
    });
    Reminder := true;
    alert(" ✅ Proceso finalizado.")
    
    • John_Halls
    • 2 wk ago
    • Reported - view

     I noticed a slight error in the code I gave you, corrected to

    for i in select INVOICE where 'BALANCE DUE' > 0 and 'DUE DATE' - 7 = today() and Reminder = null do
       let message := "Estimado cliente, su factura #" + i.'INVOICE No' + " con saldo pendiente de " +
       text(i.'BALANCE DUE') +
       " vence el " +
       text(i.'DUE DATE') +
       ". Le agradecemos su pronto pago. Gracias.";
       sendEmail({
          from: userEmail(user()),
          to: i.CUSTOMER2.'CUSTOMER EMAIL',
          subject: "Recordatorio de pago - Factura #" + i.'INVOICE No',
          text: message,
          html: null
          });
       i.(Reminder := true)
    end

    This won't be the cause of your failure though.

    Put this in the console and see if it returns any records (which will show as a list of record Ids)

    select INVOICE where 'BALANCE DUE' > 0 and 'DUE DATE' - 7 = today() and Reminder = null

    If it doesn't I would look at which of the conditions is incorrect by trying them one at a time, and then two at a time. Hint: check out Reminder = null as if it's a Yes / No field it can have three values, true, false and null

    Regards John

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 wk ago
      • Reported - view

       

      I put this in formula field, and any records returns.

      I send a dummy DB

    • John_Halls
    • 2 wk ago
    • Reported - view

    I tested the select statement in the console and it was as I thought, you need to change your select statement to

    select INVOICE where 'BALANCE DUE' > 0 and 'DUE DATE' - 7 = today() and Reminder != true
    

    Have a go at putting these two select statements in the console and see the difference in the results.

    Regards John

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 wk ago
      • Reported - view

       

      Hi John. Thanks.

      I change something and now is ok, I need to continue testing.

      let today := date(today());
      alert(" Iniciando el proceso de envío de correos...");
      for i in select INVOICE do
          if i.'BALANCE DUE' > 0 and date(i.'DUE DATE') - 5 <= today and
              (i.Reminder = null or i.Reminder = false) then
              let destinatario := i.CUSTOMER2.'CUSTOMER EMAIL';
              if destinatario != null then
                  sendEmail({
                      from: userEmail(user()),
                      to: destinatario,
                      subject: "Recordatorio de pago - Factura #" + i.'INVOICE No',
                      text: "",
                      html: "Estimado cliente, su factura #" + i.'INVOICE No' + " con saldo pendiente de " +
                      text(i.'BALANCE DUE') +
                      " vence el " +
                      text(i.'DUE DATE') +
                      ". Le agradecemos su pronto pago. Gracias."
                  });
                  i.(Reminder := true);
                  alert(" Correo enviado a " + destinatario)
              else
                  alert("⚠ Error: La factura #" + i.'INVOICE No' + " no tiene email registrado.")
              end
          else
              alert("⏭ Factura #" + i.'INVOICE No' + " no cumple con las condiciones.")
          end
      end;
      alert(" ✅ Proceso finalizado.")
      
    • John_Halls
    • 2 wk ago
    • Reported - view

     I looked up the alert() function in the Ninox documentation and, as I thought, this is what it says

    If you call this function more than once within the same script, only the last function call will be executed, so the user has only one box to confirm.

    If I were going to do anything, I would write the results to a sub-table so that you have a record of when the emails were sent.

    Also, you have gone back on my recommendations regarding today() and the select statement. All of this can be put together in one select... where statement

    for i in select INVOICE do
        if i.'BALANCE DUE' > 0 and date(i.'DUE DATE') - 5 <= today and
            (i.Reminder = null or i.Reminder = false) then
            let destinatario := i.CUSTOMER2.'CUSTOMER EMAIL';
            if destinatario != null then
    

     Regards John

      • Rafael Sanchis
      • Rafael_Sanchis
      • 2 wk ago
      • Reported - view

       

      If you call this function more than once within the same script, only the last function call will be executed, so the user has only one box to confirm.

      Yes you are right.

      Thanks John, appreciate your help. 👍

    • Mel_Charles
    • 2 wk ago
    • Reported - view

    John

    although hmtl : null is not needed is you are are calling a thisBody option

    as I always use a template form and a body.

     

    cutdown example

    if Customers.'Google Reviews' = 1 then
        let thisBody := first((select JobBag_Settings).GoogleReview);
        let myEmail := userEmail();
        let log1 := first(select System_Settings).HTMLxGoogle;
        let log1 := replace(log1, "{UL1}", shareFile(first(select System_Settings).xGoogle));
        thisBody := replace(thisBody, "{Ord Company}", text('Ordered By'));
            thisBody := replace(raw(thisBody), "{xGoogle}", log1);
        GoogleSentFlag := 1;
        sendEmail({
            from: myEmail,
            to: CEmail,
            subject: "How did we do? - Follow up regarding your recently delivered job",
            text: "Job Delivered Review",
            html: thisBody
        })
    else
        alert("CUSTOMER OPTED OUT OF GOOGLE REVIEWS")
    end