Newsletter Mailing System



of course

the system


The email field in the Tome database will be converted to this grammar.

<empty> | <email list> | <status> <email list>

where <email list> is a space separated list of legitimate email addresses
<empty> is a field containing no characters
<status> := “ok” | “bounce” | “unsubscribe” | …

Only records with no status (fields starting directly with an email)  or “ok” will get bulk mailed to.

Limitation:  all emails at one physical address record must be in the same status.

Challenge: deduping in this situation.
or much simpler and solve deduping problem, but give up multiple emails for same address

<empty> | <email>  [ <status> ]

<empty> is a field containing no characters
<status> := “ok” | “bounce” | “unsubscribe” | …

Note:  after conversion program, new addresses introduced via direct input, or online store, will have their status empty.


Convert this regex to Foxpro and use it in emailConvert.prg & maillist.prg …
function validateEMAIL($EMAIL) {
    $v = "/[a-zA-Z0-9_-.+]+@[a-zA-Z0-9-]+.[a-zA-Z]+/";

    return (bool)preg_match($v, $EMAIL);
stack overfl
and maybe even do an error trap for Denies’s fingers in #tome …

#shucks FoxPro has no regex expressions without integrating it with a lot of other software.  not going to happen.  i will need to rely on the emailVerify to do all the email validation.

restoring the test environment from the live environment …

  1. run dos procedure above
  2. rebuild email index as per code, click decending
  3. locate the 2 badly coded “unsubscribed” and manually fix them
  4. count should be around 55721 + the ones we added
  5. run test again and hope for success null

apparent success null

got this part working ..

← what are those 18 unknowns ?

looks good
but if i run it again i wipe out the unsubscribes
that should not happen null

hopefully the final conversion run
is stable

moving on … next phase is writing the files and seeing how they are used. 

re programming old #unsubscribes to get into #SparkPost #SuppressionLists 

i wonder if this is a reasonable URL for my “inmotion emails table”  ← yes it is null
obviously you need to be logged in to their sql server to get it. can export the entire table with a the Export wizzard at top of the php myadmin … but i just want to export the email address and names of those which have status = unsubscribe.  

i wonder how  …
try google mysql export may or may not help… nope this just seems to be for the entire db … no place to put the select.

try google mysql write csv … nope null … dudes just want to hire me null

try google mysql csv…. wow maybe “6.4 The CSV Storage Engine” looks like will work null
but now i will need to find this allegid “Database Directory” over on inmotion …
or find a way to send it to a download.
then use the intert into select from the email table, i suppose ...
    [INTO] tbl_name
    [PARTITION (partition_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE col_name=expr, ... ]
sounds like something that i need to put into a program. 

shucks … how could i cause a download of a file from the allegid “Database Directory” ?

most of the elements are in this program … guck did i just unsubscribe myself? creates a list of old #unsubscribes to put in  #SparkPost’s #SuppressionLists

things to do ….
  • read old unsubscribes into #sparkPost suppression list see Newsletter Mailing System (comment 76681)
  • perhaps write subroutine in newsletter.prg to read old unsubscribes
  • generate test list based upon estatus= “test”
    • code records ← #done 
    • write program in newsletter.prg ← #done 
    • test it into SparkPost ← #done 
  • understand and program the unsubscribe #done 
    • format for the hyperlink in the actual newsletter
    • <a data-msys-unsubscribe="1"href=""title="USEFUL_NAME">Click here to unsubscribe from our newsletter</a>
    • yielding→ Click here to unsubscribe from our newsletter
  • Generate an actual mailing list #Done
  • Compose an actual newsletter ← in process
  • Send one in test
  • Find out how to use (probably postman) to send it out
  • Send the to the test list
  • Send out the newsletter
  • Retireve the bounces
  • Program their change in status in our database