Send auto mail to suppliers using foxpro9 1

[Niki_S]

I have a code and I need to modify my code. What I have to do is I need to send auto mail to suppliers and as well as officer.
I did my code as below. And I have a cursor named PaymtPo

sFrom = "AutoMail@star.lk"
    IF ALLTRIM(thisform.cboFact.DisplayValue)='SGL'
      sTo="priyantham@star.lk"
      
    ENDIF
   
    IF ALLTRIM(thisform.cboFact.DisplayValue)='SFC' OR ALLTRIM(thisform.cboFact.DisplayValue)='VTM' OR ALLTRIM(thisform.cboFact.DisplayValue)='GML' OR ALLTRIM(thisform.cboFact.DisplayValue)='SJR'
      sTo="poornimae@star.lk"
    ENDIF
   
    IF ALLTRIM(thisform.cboFact.DisplayValue)='SGG' OR ALLTRIM(thisform.cboFact.DisplayValue)='VTE'
      sTo="dinushaf@star.lk"
    ENDIF

    sCC="shehanig@star.lk"

[b]Here I created body[/b]

SqlExec(OpsHandle,"Exec SGLSQL.msdb.dbo.sp_send_dbmail 'AutoMail',@recipients=?sTo,@copy_recipients=?sCC, @subject=?sSubject,@Body=?clbody,@file_Attachments =''")

Inside this code I need to do my modifications. Can anyone please help me to do this?
Thank you

 

[Chris Miller]

The mail body is sent to MSSQL Server via the [tt]@Body=?clbody[/tt] portion of the SQLExeec line. And the body sent therefore is in clbody.
Create the body all in all within clbody and everything is fine.

Chriss

 

[Niki_S]

 SELECT PaymtPo 
    GO TOP
    sSubject="Paymaster Confirmation-"+ALLTRIM(PaymtPo.cPayeeName)
    clbody="Dear Supplier,"+Chr(13)+Chr(13)
(PaymtPo.cFtyName)+" for below invoices"+Chr(13)+Chr(13)
    clbody=clbody+"This is to confirm that we have paid "+ALLTRIM(Paym_Dtl.Curr)+" "+ALLTRIM(TRANSFORM(Paym_Dtl.Payment_Value,'999,999,999.99'))+Chr(13)
    clbody=clbody+"Payment Reference :"+ALLTRIM(cPaymentNumber)+"    "+"Payment Date:"+DTOC(dValue_Date)+Chr(13)
    clbody=clbody+"To   :"+ALLTRIM(PaymtPo.cPayeeName)+Chr(13)
    clbody=clbody+"From :"+ALLTRIM(PaymtPo.cFtyName)+Chr(13)+Chr(13)
     
    
    clbody=clbody+"Batch#        Invoice#                        Invoice Date       PO#              Invoice Value              "+Chr(13)
    clbody=clbody+"---------------------------------------------------------------------------------------------------------------------"+Chr(13) 

    SELECT PaymtPo
    _Cnt=1 
    SCAN
      IF _Cnt=1
        clbody=clbody+ALLTRIM(cBatchNo)+"  "+PADR(ALLTRIM(cInvNo),17,' ')+"        "+DTOC(dInvDate)+"     "+cPoNo+" "+ALLTRIM(Paym_Dtl.Curr)+" "+TRANSFORM(Po_Val,'9,999,999.99')+Chr(13)
      ELSE      
        clbody=clbody+ALLTRIM(cBatchNo)+"  "+PADR(ALLTRIM(cInvNo),17,' ')+"        "+DTOC(dInvDate)+"     "+cPoNo+" "+ALLTRIM(Paym_Dtl.Curr)+" "+TRANSFORM(Po_Val,'9,999,999.99')+Chr(13)       
      ENDIF  
      _Cnt=_Cnt+1
    ENDSCAN  
    clbody=clbody+" "+Chr(13)  
    clbody=clbody+"#Management Information System# "+Chr(13)
    clbody=clbody+"This is an Auto Generated E-Mail and no signature is required."

This is my body part. They told me to modify this but I actually I have no idea about this. Can you please tell me step by step to do this?

 

[Chris Miller]

No, I don't know what's not working. If you need to change the content of the body, whatever the wanted change is, you have to get all necessary informations and in the simplest case add new wording.

Before you do the SQLEXEC you could store clbody to a file and check whether that is what you want to send and if not change it, just do
STRTOFILE(clBody,'testmailbody.txt')

Chriss

 

[Niki_S]

What about this? If I have details about suppliers and I need to send auto mail to them with cc officers that I had mentioned in above sections. If that happened what is the way to so that?

 

[Chris Miller]

What does that have to do with the body.

You have mail parameters here:

SqlExec(OpsHandle,"Exec SGLSQL.msdb.dbo.sp_send_dbmail 'AutoMail',@recipients=?sTo,@copy_recipients=?sCC, @subject=?sSubject,@Body=?clbody,@file_Attachments =''")

Copy recipients need to be in sCC. You set sCC="shehanig@star.lk", so that's where a carbon copy of the mail goes to.

You should also look into the T-SQL documentation here, it's SQL Server sending mails. All you provide is the details starting with recipients and mail text (body).

sp_send_dbmail and all its options are explained here:

https://docs.microsoft.com/en-US/sq...end-dbmail-transact-sql?view=sql-server-ver15

And the only other VFP specific thing you need to know is how you send over parameters to SQL: Using ?variablename within the SQLEXEC query string.

Chriss

 

[Niki_S]

I got some details into cursor as below.

SELECT PaymtPo.cPayeeName,PaymtPo.cFtyName,Acp_Supplier_Payee.cPayeeEmail FROM PaymtPo inner join PaymtPo on PaymtPo.cPayeeName = Acp_Supplier_Payee.cPayeeName INTO CURSOR Email

In this cursor cFtyName=SGL,VTM,SFC,GML etc. Now I want to send emails by selecting cFtyName. If I select SGL I need to sent emails to all SGL records.
I did some code but I can't understand how to select SGL factories and send mails to suppliers.

sFrom = "AutoMail@star.lk"
	    SELECT Email
	    IF ALLTRIM(thisform.cboFact.DisplayValue)='SGL'
	     
              sTo="**[b] Here I need to select all SGL records and send auto mails to suppliers[/b]**"

	    ENDIF

Can anyone please tell me how to select Factory and send mails.
Thank you

 

[Chris Miller]

How should we know your database structure?

Chriss

 

[Niki_S]

Really sorry Chriss..
This is my database structure.

CURSOR Email

cPayeeName       cFtyName        cPayeeEmail
ABC Trading      SGL             abcTrading@abc.lk
Honest Lanka     VTM             honst@hLanka.lk
GFHY Trading     SGL             garm@DFHY.lk

I did something like this. Is this ok or not?

IF ALLTRIM(thisform.cboFact.DisplayValue)='SGL'
  SELECT Email.cPemail FROM Email WHERE Email.cFtyName='SGL' INTO CURSOR SGLemail
  sTo =  ALLTRIM(SGLemail.cPemail)      
ENDIF

Thank You

 

[Chris Miller]

Isn't this a table in SQL Server?

If it's a DBF and you can query it directly without going through SQLExec, you just failed at the field name you just posted yourself: cPayeeEmail is having the mail address.

Besides that you want to build up a list of CC recipients, don't you? So you have to go through your SGLemail result and process every record and add it into a cumulative sTo string.

Chriss

 

[Niki_S]

Yes this is a DBF. And I added another field into my dbf.

cPayeeName       cFtyName        cPayeeEmail             nPayeeID  
ABC Trading      SGL             abcTrading@abc.lk       123
Honest Lanka     VTM             honst@hLanka.lk         456
GFHY Trading     SGL             garm@DFHY.lk            87

I got above bdf like this.

SELECT PaymtPo.cPayeeName,PaymtPo.cFtyName,Acp_Supplier_Payee.cPayeeEmail as cPemail,PaymtPo.nPayeeID  FROM PaymtPo inner join Acp_Supplier_Payee on PaymtPo.nPayeeID = Acp_Supplier_Payee.nPayeeID INTO CURSOR Email

After this I did my code like this to send email.

	    sFrom = "AutoMail@star.lk"
	  
	    IF ALLTRIM(thisform.cboFact.DisplayValue)='SGL'
	   	SELECT Email.cPemail FROM Email WHERE Email.cFtyName='SGL' INTO CURSOR SGLemail  [b]*** By doing this I hope to get list of emails***[/b] 
	   	sTo =  ALLTRIM(SGLemail.cPemail)    
	    ENDIF

But I have an issue. When I select emails as above how can they select mails one by one and send mails to all email addresses?
How can query it directly without going through SQLExec when having BDF?
I don't need to CC. I need to just send emails for all SGL fractories when I 'm selecting SGL.

Thank you

 

[Chris Miller]

Well, you have a cursor SGemail.

To go through all records you use a SCN ENDSCAN

SCAN
   sTo = ALLTRIM(SGLemail.cPemail)
   * do something here to send a mail. Call a function SendEmailTo(sTo), for example.
ENDSCAN

Chriss

 

[Niki_S]

To send a mail I did something like this at the end.

  SqlExec(OpsHandle,"Exec SGLSQL.msdb.dbo.sp_send_dbmail 'AutoMail',@recipients=?sTo,@copy_recipients=?sCC, @subject=?sSubject,@Body=?clbody,@file_Attachments =''")

Is this correct?
Thank you

 

[Chris Miller]

Yes, but note this line expects a few parameters more than sTo.

If you need to send different texts you better define a function you call and let that determine the mail body per recipient.

If everybody should get the same mail, you can also create a comma or semicolon separated list of mail recipients in sTo and after the ENDSCAN just call the AutoMail function once.

Chriss

 

[Niki_S]

I need to send same mail. As you said I inserted a comma like this.

SCAN
   sTo = ALLTRIM(SGLemail.cPemail + ',')
   
ENDSCAN

Is this correct or if this is wrong how can I insert comma to separate mails.

This is my body part.

    sSubject="Paymaster Confirmation-"+ALLTRIM(PaymtPo.cPayeeName)
    clbody="Dear Supplier,"+Chr(13)+Chr(13)
    clbody=clbody+"This is to confirm that we have paid "+ALLTRIM(Paym_Dtl.Curr)+" "+ALLTRIM(TRANSFORM(Paym_Dtl.Payment_Value,'999,999,999.99'))+Chr(13)
    clbody=clbody+"Payment Reference :"+ALLTRIM(cPaymentNumber)+"    "+"Payment Date:"+DTOC(dValue_Date)+Chr(13)
    clbody=clbody+"To   :"+ALLTRIM(PaymtPo.cPayeeName)+Chr(13)
    clbody=clbody+"From :"+ALLTRIM(PaymtPo.cFtyName)+Chr(13)+Chr(13)
     
    
    clbody=clbody+"Batch#        Invoice#                        Invoice Date       PO#              Invoice Value              "+Chr(13)
    clbody=clbody+"---------------------------------------------------------------------------------------------------------------------"+Chr(13) 

    SELECT PaymtPo
   
    _Cnt=1 
    SCAN
      IF _Cnt=1
        clbody=clbody+ALLTRIM(cBatchNo)+"  "+PADR(ALLTRIM(cInvNo),17,' ')+"        "+DTOC(dInvDate)+"     "+cPoNo+" "+ALLTRIM(Paym_Dtl.Curr)+" "+TRANSFORM(Po_Val,'9,999,999.99')+Chr(13)
      ELSE 
        clbody=clbody+ALLTRIM(cBatchNo)+"  "+PADR(ALLTRIM(cInvNo),17,' ')+"        "+DTOC(dInvDate)+"     "+cPoNo+" "+ALLTRIM(Paym_Dtl.Curr)+" "+TRANSFORM(Po_Val,'9,999,999.99')+Chr(13)       
      ENDIF  
      _Cnt=_Cnt+1
    ENDSCAN  
   
    clbody=clbody+" "+Chr(13)  
    clbody=clbody+"#Management Information System# "+Chr(13)
    clbody=clbody+"This is an Auto Generated E-Mail and no signature is required."

    SqlExec(OpsHandle,"Exec SGLSQL.msdb.dbo.sp_send_dbmail 'AutoMail',@recipients=?sTo,@copy_recipients=?sCC, @subject=?sSubject,@Body=?clbody,@file_Attachments =''")

 

[Chris Miller]

Well, what do you see in sTo after your loop?

You're forgetting that you want to add more recipients to the list you already have.

sTo = ''
SCAN
   sTo = sTo + ALLTRIM(SGLemail.cPemail + ','
ENDSCAN

See? You expect to create a list, but all you do is overwrite the variable with just one mail address. This isn't finished, because when this list is created with a comma after each recipient, you have an extra comma at the end not needed. It might not be necessary to remove it, when Automail doesn't mind the list to end with a comma and no further recipient.

Chriss

 

[Niki_S]

Thank you chriss, Can you please explain me the purpose of inserting sTo = '' before scan?
Thank you

 

[Chris Miller]

Well, try without it, you'll see.

Chriss

 

[Chris Miller]

I think you did and maybe are still riddled by the error message "Variable STO is not found".

Maybe you also have initialized sTo earlier, but if sTo = sTo + ALLTRIM(SGLemail.cPemail + ',' is the first line declaring sTo, then this can't work. VFP can create a variable on the fly just by an assignment, ie you can have sTO = '' or any other value. Even expressions, but one thing is not allowed when a variable is declared by such an initial an assignment: It can't be in the expression for the initial value. The variable does not yet exist, so it can't be used in the expression that creates it.

What VFP does first is evaluating sTo + ALLTRIM(SGLemail.cPemail + ',' and since sTo does not yet exist you get this error.

On the other side each mail addresss needs to be concatinated to sTo within the loop, Just setting sTo = eMail will not build up a list. It's current value is extended by one further mail address for each record. You could also declare the variable sTo with LOCAL sTo, but then you get another error message 'Operator/operand type mismatch.'

LOCAL declares and initializes variables with the value .F,, that's a very VFP specific thing. VFP has no strong typing, therefore local variables all can start as a boolean (.F. is a boolean value) but then can be changed to whatever type, even multiple times in their lifetime:

CLEAR
ON ERROR ? MESSAGE()
? lAnything, VARTYPE(lAnything)

LOCAL lAnything
? lAnything, VARTYPE(lAnything)
lAnything = 'foo bar'
? lAnything, VARTYPE(lAnything)
lAnything = 0
? lAnything, VARTYPE(lAnything)
lAnything = DATE()
? lAnything, VARTYPE(lAnything)
lAnything = DATETIME()
? lAnything, VARTYPE(lAnything)
lAnything = $1.00
? lAnything, VARTYPE(lAnything)
* ...more types

As far as this freedom goes, you still can't add a string to a boolean or use a variable in the expression which is assigned to it initially. It's good practice to look where variable names first appear in code and perhaps even put a declaration ensuring the right type to the start of code. Just like the first step of creating a table is to define the field names and, most important, their data types. When you initially set a variable to some literal value you ensure the first operation on it is correct.

Chriss