Newsletter Mailing System

About: New API Procedure to mail a Newsletter - fastblogit


  1. newsletters
  2. tome
  3. shucks
  4. unsubscribes
  5. SparkPost
  6. SuppressionLists
  7. backup


Seth says

Seth says


of course

the system


Seth says
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.


Seth says
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.

Seth says
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

Seth says
got this part working ..

Seth says

← what are those 18 unknowns ?

Seth says

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

Seth says

hopefully the final conversion run
is stable

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

Seth says
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.

Seth says 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?

Seth says creates a list of old #unsubscribes to put in  #SparkPost’s #SuppressionLists

Seth says
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


See Also

  1. Thought #EmailListVerify with 1162 viewings related by tag "newsletters".
  2. Thought Copy of - New Email List Manager System with 396 viewings related by tag "newsletters".
  3. Thought Project: Sparkpost for Speak To Me Catalog with 371 viewings related by tag "newsletters".
  4. Thought Diagram of Speak.txt system with 310 viewings related by tag "newsletters".
  5. Thought Requirements for emailing with 249 viewings related by tag "newsletters".
  6. Thought (4) After mailing is finished - Sparkpost transaction to retrieve the bounces with 201 viewings related by tag "newsletters".
  7. Thought (1) Sparkpost transaction to Update a template of the newsletter composed by the Reporter with 196 viewings related by tag "SparkPost".
  8. Thought #SparkPost with 175 viewings related by tag "SparkPost".
  9. Thought about: The #RWG - comment 67967 with 159 viewings related by tag "shucks".
  10. Thought Belief Types with 139 viewings related by tag "shucks".
  11. Thought API Procedure to mail a Newsletter with 132 viewings related by tag "SparkPost".
  12. Thought Unsubscribe to Speak To Me Catalog newsletter with 108 viewings related by tag "newsletters".
  13. Thought Shout Out: Stay Hydrated but keep it in your pocket with 90 viewings related by tag "newsletters".
  14. Thought It Speaks For Its Self with 80 viewings related by tag "shucks".
  15. Thought (3a & 3b) SparkPost transaction that acctually dispatches a mailing with 77 viewings related by tag "shucks".
  16. Thought REPORTER with 77 viewings related by tag "newsletters".
  17. Thought Eggs Florentine with 61 viewings related by tag "shucks".
  18. Thought BlackListt ... get speaktomecatalog off the list with 57 viewings related by tag "newsletters".
  19. Thought 1705a with 52 viewings related by tag "newsletters".
  20. Thought (2) Post the mailing list to SparkPost with 52 viewings related by tag "SparkPost".
  21. Thought Speak.txt - mailing Procedure with 50 viewings related by tag "newsletters".
  22. Thought Shout Out: Trump is back by popular demand ! (2017-01-17) with 50 viewings related by tag "newsletters".
  23. Thought Speak To Me Catalog will donate 10% of every sale on these products with 45 viewings related by tag "newsletters".
  24. Thought Setting up a bounce domain for Sparkpost with 44 viewings related by tag "newsletters".
  25. Thought #shucks with 41 viewings related by tag "shucks".
  26. Thought Luther's Table with 40 viewings related by tag "shucks".
  27. Thought about: GitHub - sait/vfpjson: Json encoder and decoder for VFP Visual Fox Pro with 36 viewings related by tag "shucks".
  28. Thought Email Chatbots with SparkPost with 25 viewings related by tag "SparkPost".
  29. Thought hey what happened to my thought? with 20 viewings related by tag "shucks".
  30. Thought Marketing Stratagies for Speak To Me Catalog with 20 viewings related by tag "SparkPost".
  31. Thought A mailer in the Cloud with 16 viewings related by tag "newsletters".
  32. Thought Your Role in the State of Email - Webinar with 15 viewings related by tag "newsletters".
  33. Thought 1711b with 11 viewings related by tag "newsletters".
  34. Thought 1711a with 8 viewings related by tag "newsletters".
  35. Thought sparkpost API documentation with 7 viewings related by tag "SparkPost".
  36. Thought Coupon 1604a - Talk about those special times coming ... with 3 viewings related by tag "newsletters".
  37. Thought about: antivirus software and internet security for your pc with 3 viewings related by tag "backup".
  38. Thought [title (18273)] with 2 viewings related by tag "backup".
  39. Thought Coupon 1603c ... 10% off your highest valued item with 1 viewings related by tag "newsletters".
  40. Thought A quick question ? with 0 viewings related by tag "newsletters".
  41. Thought Shout out newsletter 2016-03-08 with 0 viewings related by tag "newsletters".
  42. Thought Tome problem on DOS PC with 0 viewings related by tag "tome".