Sending Personalized Reports in Batch Email

[aprescott]

Wanting to be able to send end-of-month giving report to our donors via email - either in the email text or as an attached report.

Obviously, each report needs to be personalized (so that I'm not sending another donor's information to the wrong person). Would need to be able to specify a given date range and generate report based on those values.

This would be a batch of 500-1000 emails. (We currently send a snail mail version of this from our desktop Alpha database and are looking to go "paperless")

Currently working with lists and UX (panels and the like). Also have MailChimp account and looking into Mandrill. Where do I begin?

[madtowng]

Hi Adrianne,

I've been doing something similar to this for years, using my own email server.
The biggest issue will be making sure you have tables configured with everything you need.
You will also want to consider any logging you may want/require.
Once you have that, design the report in the report designer.
I strongly suggest using SQL because it gives you options to easily manipulate your query.

I had done this using dbfs before a friend introduced me to sql (specifically mySQL).
I have no experience with mailchimp or mandrill, but lots of experience using smtp_send.

In short, and I'm presuming you want to automate as much of this as possible, I would say
Design your report. Include EVERYTHING you think should be in the report.
Once that is done, configure the table(s) with everything you need, and certainly things you might need, but don't think you need.
These would include fields for date entered, payment received, payment applied, donor contact information.

Keep track of variations of the reports, you will have options.
Once you have all this (the hard stuff done) , sending the reports will be the easy part.

[aprescott]

We've actually got a lot of the "hard stuff" done- we've got a fairly robust desktop version (using dbfs) that collects donation information. I've been working on converting things to mysql and moving this whole beast to web.

Assuming I've got a sql table Contact with donor contact info and a sql table Contrib that is donations (amount, date posted, payment type, deposit number, etc) and a view to join the two, what's next? I think I've got the report building under control. I'm just so new at web that it seems potentially more difficult than it actually is.

How do I send unique reports to each email address without having to manually send all?

[Jay Talbott]

The 30,000 foot version is something like this:
Query the Contact table for DonorId for all of those who contributed within a certain period
Loop through those DonorIDs
Create a report for that donor for the donations within the period (set up report with arguments for DonorID, BeginDate and EndDate)
Save the report to the server in a temporary folder
Attach the report to the email. If you are using EMAIL_SMTP_OPEN(), the code may look something like this:

function emailReport as c (e as p)

dim cn as sql::Connection
dim rs as sql::ResultSet
dim flag as L = .F.
dim sqlQueryStatement as c = ""
dim pm as P
dim ps as P
dim MyOrgainizationEmailAddress as c = "[email protected]"
flag = cn.ope("::name::connectionString")
IF flag = .T. THEN
CANCEL()
emailReport = ""
END
END IF

dim globalvars as p
globalvars = global_variables()
dim args as SQL::Arguments
dim layout_name as c
layout_name = a5_removeTrailingBackslash(context.request.applicationRoot) + chr(92) + "DonorLetter.a5rpt"
args.Set("DonorID",rs.data(1))
args.Set("DonorName",rs.data(2))
args.Set("BeginDate",CTOD(e.dataSubmitted.BeginData))
args.Set("EndDate",CTOD(e.dataSubmitted.EndData))


sqlQueryStatement = "SELECT DonorID, DonorName, DonorEmailAddress FROM ContactTable WHERE DonorId IN (SELECT DonorId FROM ContribTable WHERE ContributionDate BETWEEN :BeginDate AND :EndDate"

cn.Execute(sqlQueryStatement,args)
rs = cn.ResultSet
WHILE rs.NextRow() = .T.
DonorReportName = "c:\a5webroot\reports\DonorLetter_"+rs.data(1)+".pdf"
a5w_report_saveAs(layout_name,"PDF","","",DonorReportName,globalvars,"",args)
DonorEmailAddress = rs.data(3)

if (email_smtp_open(ps, "smtp goes here", 587, "user goes here", "password goes here")) then
pm.to = DonorEmailAddress
pm.from = MyOrgainizationEmailAddress
pm.subject = "my subject"
pm.message = "this is the message"
pm.attachments = DonorReportName
pm.html_message = "Message here"

result = email_smtp_send(pm, ps)
end if
email_smtp_close(ps)
END WHILE

emailReport = ""
end function

That is pretty much the gist of the code for looping through records and sending emails directly from your computer. Using Mandrill or Mail Chimp will be a bit different, but this may get you started.
Hope it helps.
Jay

[aprescott]

Jay, this is excellent. You are a lifesaver. One more dummy question - where would I put this code?

[aprescott]

OK. So I have this in my xbasic Functions on the Ux component. I have a button that uses Javascript actions -> Ajax Callback and calls the xbasic function emailReport.

Is that right?

So far the button isn't working - but I'm also trying to adapt the code you provided for mandrill and very well haven't gotten it correct yet.

[madtowng]

The help for mandrill can be found at https://documentation.alphasoftware....ill%20function
My preferred method is to work with what you have, making the code work for you, both in functionality and readability ( there is nothing worse than
revisiting code a year later , having to question what the intent was when it was written.
I am very willing to help, but I don't want to guess at what the code you've written is.
Sharing the xbasic code you have for the emailReport function would be a big step.
Obviously, you will want to keep passwords protected, but this is the minimum of what is needed to determine where the problems are.

Gregg

[aprescott]

Here's what I have currently - so far I keep getting an error when I start referring to the ResultSet rs

function emailReport as c (e as p)

dim cn as SQL::Connection
dim rs as sql::ResultSet
dim flag as L = .F.
dim sqlQueryStatement as c = ""
dim pm as P
dim rs as P
dim MyOrganizationEmailAddress as c = "[email protected]"
flag = cn.open("::name::donor")
IF flag = .T. THEN
CANCEL()

emailReport = ""

END IF


dim globalvars as p
globalvars = global_variables()
dim args as SQL::Arguments
dim layout_name as c
layout_name = a5_removeTrailingBackslash(context.request.applicationRoot) + chr(92) + "EOMDonationLetter.a5rpt"
args.Set("ID_Number",rs.data(1))
args.Set("First_Name",rs.data(2))
args.Set("StartDate",CTOD(e.dataSubmitted.StartDate))
args.Set("EndDate",CTOD(e.dataSubmitted.EndDate))


sqlQueryStatement = "SELECT ID_Number, First_Name, Email FROM monthgift WHERE ID_Number IN (SELECT ID_Number FROM Contrib WHERE DatePosted BETWEEN :STARTDATE AND :ENDDATE"
dim DonorReportName as c = ""
dim Email as c = ""
cn.Execute(sqlQueryStatement,args)
rs = cn.ResultSet
WHILE rs.NextRow() = .T.
DonorReportName = "c:\a5webroot\reports\EOMDonationLetter_"+rs.data(1)+".pdf"
a5w_report_saveAs(layout_name,"PDF","","",DonorReportName,globalvars,"",args)
Email = rs.data(3)


dim MandrillKey as c = "MandrillSecretKey"

pm.send_to = Email
pm.from_email = MyOrganizationEmailAddress
pm.subject = "Test EOM Letter"
pm.message_html = "Here is the message. Hello World!"
dim ms.attachmentsArray[1] as p
pm.attachmentsArray[1].name = DonorReportName
pm.attachmentsArray[1].type = resolve_mime_type("pdf")

dim pResult as p
pResult = email_send_mandrill(MandrillKey,pm)
if pResult.error = .f. then
emailReport = "alert ('Report sent.');"
else
_name = "alert('Report was not sent.');"
end if

END WHILE

emailReport = ""

end function

[madtowng]

Give me a bit to look at.

[madtowng]

Adrianne,
I see a couple problems. I will try to guide you to the problem unless you ask me to fix the code for you. There is one place where I flat-out give you the code, but that's because the code is missing.
The first thing I see is that you dimmed rs twice, first as sql::resultsset, second as p.
This will mess with the loop that you're using to generate the reports.
I think I see a problem in your select statement as well.
You should probably check to see if your query was successful.
Let me know these things are fixed (please post the adjusted code) and I will look further.

Gregg
P.S. If you're feeling extra ambitious, please share the structure of all tables involved. This will allow me to actually test the sql.

if cn.callresult.text <> "Success" then
putMagicCodehere = .t.
end if

[aprescott]

OK, I worked with my husband (who is not familiar with Alpha, but does understand coding better than me) and he came up with this. Email sends successfully, but the a5w_report_saveAs is not saving the report in the designated location. thoughts on why?

function emailReport as c (e as p)
debug(1)


dim donors as p
donors = json_parse(e.__listdata)

dim pm as P

dim MyOrganizationEmailAddress as c = "[email protected]"



dim globalvars as p
globalvars = global_variables()
dim args as SQL::Arguments
dim layout_name as c
layout_name = a5_removeTrailingBackslash(context.request.applicationRoot) + chr(92) + "EOMDonationLetter.a5rpt"


dim DonorReportName as c = ""
dim Email as c = ""

for each donor in donors[1].data

args.Set("ID_Number",donor.id_number)
args.Set("First_Name",donor.First_Name)


DonorReportName = "c:\a5webroot\reports\EOMDonationLetter_"+donor.id_number+".pdf"
a5w_report_saveAs(layout_name,"PDF","","",DonorReportName,globalvars,"",args)
Email = donor.email


dim MandrillKey as c = "A8Req9IQZOIZauMO4sdEZA"

pm.send_to = Email
pm.from_email = MyOrganizationEmailAddress
pm.subject = "Test EOM Letter"
pm.message_html = "Here is the message. Hello World!"
pm.attachments = DonorReportName


dim pResult as p
pResult = email_send_mandrill(MandrillKey,pm)
if pResult.error = .f. then
emailReport = "alert ('Report sent.');"
else
_name = "alert('Report was not sent.');"
end if

next

emailReport = ""

end function

I have on a UX two fields that ask for StartDate and End Date and a button that refreshes a list to reflect records who fit into that date range. Then there is a button that runs this code.

[madtowng]

The donorreportname is not getting the expected value. This should help with that https://documentation.alphasoftware....eas%20function.

[Al Buchholz]

replace

dim DonorReportName as c = ""

dim Email as c = ""

with

dim DonorReportName as c
DonorReportName = ""

dim Email as c
Email = ""

I recommend keeping the dim and value setting as two different statements because combining them can sometimes make the dim/value set not reset the value.

See docs. https://documentation.alphasoftware....c_DIM%20AS.xml

________________________________________

Debug(1) can watch a variable and show you it's value when the value is assigned to the variable.
Right click on the variable name in the code and select watch.

Show us what debug shows that value of DonorReportName is when you assign here:

DonorReportName = "c:\a5webroot\reports\DonorLetter_"+rs.data(1)+".pdf"

[aprescott]

Debug is blank when I step through that line. When I go to next line (using a5w_report_saveas) it shows C:\A5Webroot\reports\EOMDonationLetter_13746.pdf (But that file does not exist in that location)

[Al Buchholz]

Debug is blank when I step through that line. When I go to next line (using a5w_report_saveas) it shows C:\A5Webroot\reports\EOMDonationLetter_13746.pdf (But that file does not exist in that location)

Does it go some where else or nowhere that you can find?

Permissions issue - try another location for the file to divide and conquer the issue.