CJSmith dot me

I dump stuff I find useful here

Category: V1 DBForms

DBForms: Check and format email addresses from Sage Line 500/1000

Published / by Chris Smith / Leave a Comment

As the email field can sometimes not be used for the correct purpose I use a bit of code to look for an @ symbol and then a dot. If more than one email address is in I replace the comma with -t which is required by dbmailsend.
If using Sage Line 500/1000 the CustomerTable will be scheme.slcustm
I expect the DBForms def below to pickup the customer code already so there is only a lookup to the customers email address.

In [INPUT]
 
VARIABLE=EMAIL_ADDRESS
VARIABLE=ISQL_CMD , , "<PATHTOOSQL.exe>"
 
In [EXTRACT]
LET EMAIL_ADDRESS= EXECUTE(((ISQL_CMD + " -h-1 -S <SQLSERVER> -U <USER> -P <PASSWORD> -d <DATABASE> -Q\"set nocount on;select email from <CustomerTable> where customer='") + <CUSTOMERVARIABLE>) + "'")
DEBUG ("Email >" + EMAIL_ADDRESS) + "<"
 
 
In [DELIVERY]
LET EMAIL_ADDRESS= TRIM(EMAIL_RESULT)
                NOTE Check for valid email address; is there an @?
                IF  INSTR(EMAIL_ADDRESS,"@",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid"
                }
                NOTE Check for valid email address; are there spaces?
                NOTE Check for valid email address; is there at least one full stop?
                IF  INSTR(EMAIL_ADDRESS,".",1) = 0 THEN
                {
                        LET EMAIL_ADDRESS=""
                        DEBUG "Email address invalid2"
                }
                DEBUG ("Email B >" + EMAIL_ADDRESS) + "<"
                NOTE Format EMAIL_ADDRESS Variable if more than one email address entered.
                IF  INSTR(EMAIL_ADDRESS,",",1) <> 0 THEN
                {
                        LET EMAIL_ADDRESS= REPLACE_STRING(EMAIL_ADDRESS,",","\" -t \"")
                        DEBUG ("Email C >" + EMAIL_ADDRESS) + "<"
                }
 
 
 
In [OUTPUT]
 
OUTPUT=<EMAILOUTPUTNAME>
INVOCATION="<pathtodbmailsend>"
PARAMETERS=
{
        "-f"
        QUOTED <EmailAddress>
        "-r"
        QUOTED <EmailAddress>
        "-c"
        QUOTED <EmailAddress>
        "-s"
        QUOTED <EMAILSUBJECT>
        "-a"
        <ATTACHMENT>
        "-t"
        QUOTED EMAIL_ADDRESS
        OUTPUT_FILE
}