Sunday, September 24, 2017

Sparkpost and AplhaFive - Fantastic Part 4 (Final)

September 24th 2017

Sparkpost and AlphaFive - Fantastic

Part 4 ( Final)

So far we have covered 
1> to create an account with Sparkpost and generate an api key for sending the emails.
2> created a separate table to hold the api key, so that we can retrieve that key with a sqlLookup command to save us from exposing the key to the outside world.
3> we created a function sparkpost_email_send function with three arguments and sent one or more emails.  Then we changed and incorporated that function in our code to send email  itself, to make it easy and not worry about publishing user defined functions.

Today is the final discussion, we will cover how to send professional looking email with images and links the body of the email. The prerequisite for the images to be sent along the email is somehow the email should get the data for the images sent.  There are few methods available and I am going to take easy method.

You can send the images as attachment, then it will show somewhere in the bottom.  That is not what we need. We need to format the email with logo and banner at the top then the body then the footer with some other banner and social network images as links. This will not be possible with that method.

You can send images as base64encoded value and the email will convert that into images when received. A cumbersome process for me, at least.

The easy method, which I will describe now and follow in the example, will be to store the image in a content delivery network(CDN), preferably hosted by you and link that image to show in the email.  Works every time except when the CDN goes down.  The advantage of CDN hosted by private provider is that it rarely goes down but they might flag you down for spamming, this is not the case when you host it yourself, but your server may go down when the internet is down or rebooting.  There is advantage and disadvantage in both systems. You decide which is better.  I am using my own hosted server to deliver the content.

Then finally, if you have time, please read documentation in alphafive for html templates and using placeholders to replace variables in that location to customize the email.

So, now that all the bases are covered, I will show you how the email is sent.  In this model, I have an dialog to fill in the from address, to address, cc, subject, item name that will bring up the name, image and description, then I will fill in the price then send that as an email.  This is only an example, you can design any way you want and as long as you incorporate what I have shown in the html template, placeholders and image delivery method, this method will work for you too.

So, I will give you the code that sends the email and show you via a screencast the working of that.
Here is the code:
First onChange event of the item will do a lookup to the back end and bring the necessary data back to the dialog;

function xb2 as c (e as p)
'debug(1)
dim cn as sql::Connection
dim args as sql::Arguments
dim result as l = .f.
dim sqlSelect as c
dim product_id as n
product_id = e.dataSubmitted.product
result = cn.Open("::Name::local_MySql")
if result then
sqlSelect = "SELECT * FROM product WHERE id = :newProduct_id"
args.set("newProduct_id",product_id)
result = cn.Execute(sqlSelect,args)
if result then
rs = cn.ResultSet
description = rs.data("description")
image_name = rs.data("image_name")
end if
cn.close()
end if
e._set.image_name.value = image_name
e._set.description.value = description
'xb2 = "javascript commands to be executed in the Browser."
end function 

Then you will fill in the price ( this can also be automated if you wish), thenwhen you press send email button this will call another function that will send the email and send the message to the browser.
Here is that code:
function xb as c (e as p)
'debug(1)
dim subject as c
dim description as c
dim to as c
dim cc as c = ""
dim list as c = ""
dim flag as l = .f.
dim msg as c
dim email_msg as c
dim image_name as c
dim price as c
dim product_id as n
dim cn as sql::Connection
dim sqlSelect as c
dim args as sql::Arguments
dim rs as sql::ResultSet
dim result as l
dim ps as p
dim pm as p
dim pp as p
cc = e.dataSubmitted.cc
select
case cc = "All"
sqlSelect = "SELECT * FROM customer2"
case cc = "Gandhi"
sqlSelect = "SELECT * FROM customer2 WHERE name = 'gandhi'"
case cc = "Papi"
sqlSelect = "SELECT * FROM customer2 WHERE name = 'papi'"
case cc = "Gandhi&Papi"
sqlSelect = "SELECT * FROM customer2 WHERE name = 'gandhi' OR name = 'papi'"
end select
result = cn.Open("::Name::local_MySql")
if result then
result = cn.Execute(sqlSelect)
if result then
rs = cn.ResultSet
flag = rs.nextRow()
while flag
list = list + rs.data("email")+","
flag = rs.nextRow()
end while
if substr(list,len(list)) = "," then
list = substr(list,1,len(list)-1)
end if
end if
end if
to = e.dataSubmitted.to
subject = e.dataSubmitted.subject
description = e.dataSubmitted.description
image_name = e.dataSubmitted.image_name
price = e.dataSubmitted.price
pp.description = description
pp.image_name = image_name
pp.price = price
email_msg = <<%str%
<!DOCTYPE html>
<html>
<head>
<meta name="generator" content="Alpha Five HTML Editor Version 11 Build 3381-4096">
<!-- must use in order to make XP Themes render -->
<meta HTTP-EQUIV="MSThemeCompatible" content="Yes" />
<meta http-equiv="X-UA-Compatible" content="IE=edge" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 

<title></title>
<style>
<!--
body {
background-color:white;
}
h1 {
font-size:35px;
font-style:italic;
}

-->
</style>
</head>
<body>

<table width="90%" border="0" bordercolor="" bordercolorlight="" bordercolordark="" bgcolor="" cellpadding="0" cellspacing="0">
<tr>
<td style="background-color:#008080; text-align:center; color:#ffffff;"><h1>Nini's Wine Cellar</h1></td>
</tr>
<tr><td>&nbsp;</td></tr>
<tr>
<td><div style="text-align:center;">
<img style="width:320px;height:180px;" src="http://24.90.115.79:82/claretpos/naples_morning.jpg">
</div></td>
</tr>
<tr>
<td style="text-align:center;"><h3>Our weekly Promotions</h3> </td>
</tr>
<tr>
<td style="text-align:center;">
<table width="90%" border="0" bordercolor="" bordercolorlight="" bordercolordark="" bgcolor="" cellpadding="0" cellspacing="0">
<tr>
<td style="width:50%;"><div style="text-align:center;">
<img style="width:110px;height:110px;" src="http://24.90.115.79:82/claretpos/{image_name}">
</div></td>
<td style="width:50%; text-align:left;"> {description}<br/>{price}</td>
</tr>
</table>

</td>
</tr>
   <tr>
<td style="background-color:#008080; text-align:center; color:#ffffff; font-size:20px;" >Fine wines for less</td>
</tr>
<td style="text-align:center;">Stay Connected<br/></td>
</tr>
<tr>
<td style="background-color:#ffffff; text-align:center;">
<a href = "https://www.facebook.com/niniswinecellar" target="_blank">
<img src = "http://24.90.115.79:82/claretpos/facebook.png" style="width:22px;height:22px;border:0">
</a>
<a href = "https://www.twitter.com/niniswinecellar" target="_blank">
<img src = "http://24.90.115.79:82/claretpos/twitter.jpg" style="width:22px;height:22px;border:0">
</a>
</td>
</tr>
</table>

</body></html>
%STR%
result = email_smtp_open(ps,"smtp.sparkpostmail.com",587,"SMTP_Injection",sql_lookup("::Name::local_MySql","sparkpost_api","ID=1","api_key"),"STARTTLS")
if result then
pm.from = "admin@niniswinecellar.com"
pm.from_alias = "Govindan Gandhi"
pm.to = to
pm.bcc = list
pm.subject = subject
'pm.message = message
pm.html_message = evaluate_string(email_msg,pp)
result = email_smtp_send(pm,ps)
if result then
msg = "alert('email successfully sent');"
else
msg = "alert('there was a problem sending email, try again later');"
end if
end if
email_smtp_close(ps)
xb = msg

end function 

Some of the things to remember, always send the email to yourself and bcc to others so that the email address is hidden. Always use hidden api key to avoid people exploiting your or your client key.
While this is shown as an example, you can design your email differently and if you use these methods you can definitely send customized emails not plain simple drag as an email.

Hers is a sample email sent via Sparkpost and using the code.

No comments:

Post a Comment