Emailing individual reports to many customers automatically

[[email protected]]

Hi Everybody. I'm absolutely no expert and don't know any action scripting. I've looked at many of the posts on the forum about this subject but I'm confused and need help. It seems that reportmailer would help me, is it still available for V8 and how do I get it if I need it?

I have an account set with an end of month statement report. This produces a report record for each of 35 customers, obviously each one totally different. I want to automatically send each individual report record to each individual's email address. I have a separate table for customer details which contains a field with their email addresses. I could make this a part of the set, or I could create a field within the account table (part of the set) to store the customer's email address.
At the moment I am querying the record by customer number, which is a 3 character character field, this is also the case in the customer details table. So I get the record by customer number and choose to send by email as a pdf attachment, choose the email address by filling in the same 3 character customer code so that the email client (outlook express) finds the address from its own address book and press 'send'. Unfortunately this means doing it 35 times and I could really use a button on each report record that sends the pdf in an email, or even better a button somewhere that sends each record to each recipient itself, while I have a cup of coffee.
Thanks in advance, Trevor.

[Ray in Capetown]

Re: Emailing individual reports to many customers automatically

Trevor, you say you don't know any action scripting. That makes it difficult to suggest practical solutions

An overview, broken down into steps;-

Switch to Alpha's mailer (as opposed to Outlook)
Provide an email address field in the customer table

Then create a sequence (using whatever methodology is within your capabilities) to;-
Loop through the clients, for each call the statement report to email.
Put a timer of a few seconds at least between each.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

Provide a sample database with a few dummy records Just enough to see what fields are involved. Indicate where (form name) you would like to have the email all statements button.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Thanks Ray, I've successfully sent a report in Alpha's client, the customer table already has an email field and I'll try to see if I can find a way to sequence the reports.
Trevor.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Provide a sample database with a few dummy records Just enough to see what fields are involved. Indicate where (form name) you would like to have the email all statements button.

Hi Stan,

Zipped example attached. I have removed sensitive info from customers for data protection and put one of my own email addresses in to the few records left.
The report 'AccMonthly' will produce the statement records. To send an individual record I use the report 'LaprimaSLAccount'. When prompted, use one of the 3 character customer codes that exists in the 'AccMonthly' report.
The ideal form to put the button on would be 'Account', this form is what calculates all the regular charges and is where we add the 'one-offs', you'll probably recognise it, you've helped me before.
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

The customers table is not included in your zip. I would assume that is where one could find the details necessary to send the email?

The script to send your emails is basically

parentform.commit()
tbl = table.current()
tbl.fetch_first()
while .not. tbl.fetch_eof()
	query.filter = "cuscode = "+quote(alltrim(tbl.cuscode))
	query.order = ""
	to_param = a5_eval_expression("",local_variables())
	cc_param = a5_eval_expression("",local_variables())
	bcc_param = a5_eval_expression("",local_variables())
	subject_param = a5_eval_expression("",local_variables())
	message_param = a5_eval_expression("",local_variables())
	
	:Report.Send("AccMonthly","PDF",query.filter,query.order,"default",to_param,subject_param,message_param,cc_param,bcc_param)
	sleep(2)
                tbl.fetch_next()
end while

You would set to_param with a lookup() to the customer table returning the email address. Something like
to_param = Lookup("customer","cuscode = "+quote(alltrim(tbl.cuscode)),"alltrim(email_address_field_name)")



The subject_param line would probably just be

subject_param = "Monthly Statement"
message_param = "Statement covers activity in "+cmonth(addmonths(date(),-1))+"."

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Stan,
Thanks, that looks like the stuff. I'm sorry about the table I missed out, I spent some time adjusting it to just a few records and then forgot to include it ! I'll take a look at this tomorrow because I can spend all day without interruption on it.
Trevor.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Stan,
I modified the code as I thought necessary to this;
'Date Created: 18-Sep-2013 11:46:57 AM
'Last Updated: 18-Sep-2013 12:26:44 PM
'Created By : NEBody
'Updated By : NEBody
parentform.commit()
tbl = table.current()
tbl.fetch_first()
while .not. tbl.fetch_eof()
query.filter = "cuscode = "+quote(alltrim(tbl.cuscode))
query.order = ""
to_param = Lookup("clients","cuscode = "+quote(alltrim(tbl.cuscode)),"alltrim(email)")

cc_param = a5_eval_expression("",local_variables())
bcc_param = a5_eval_expression("",local_variables())
subject_param = "Monthly account from Laprima S.L."
message_param = "Your attached account covers the month of "+cmonth(date())+"."


:Report.Send("AccMonthly","PDF",query.filter,query.order,"default",to_param,subject_param,message_param,cc_param,bcc_param)
sleep(5)
tbl.fetch_next()
end while

oOo

I made a dummy table to get the email addresses from called "Clients" it has only 2 fields, one called "cuscode" (002, 004, 005, 006, 011 and 012) and the other field called "email".
I placed a button on the "accounts" form and placed the above code in the "action scripting" box under the "OnPush" tab and played about with other bits and pieces until it started to work. O.k., it prepares the first MonthlyAcc report for the first code in the clients table (002) but it prepares to send it from my default email address - that's fine by me. It doesn't actually send it, I have to do that manually but it then goes fine. It then moves on to the next code (004) and again I have to press the send button manually. This time it goes but when it prepares the next account, the address and pdf are the same as the previous, second one, i.e. 004. If I send that one again it loops and gives me another the same over and over so I have to close the app with Ctrl-Alt-Delete. I checked that the records in The "Account" table are not filtered (all selected).
I'm sure it must be something I'm missing but it gets me the way it moves on from 002 to 004 but no further.
I increased the sleep command to 5 in case it was moving on before the send had completed properly. Do I need to force it to use A5 as the email client and\or remove the cc_param,bcc_param expressions? If you want to see the "clients" table, can I send it to you as a private message please?
Regards,
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

I had forgotten one had to use the send button with report.send(). That's probably colliding with the sleep command and tying things up.
I left in the ui_msg_box() below just to display the current customer being processed.

Create a directory under your database directory, temp, and let's try it this way.

parentform.commit()
tbl = table.current()
tbl.fetch_first()
while .not. tbl.fetch_eof()
query.filter = "cuscode = "+quote(alltrim(tbl.cuscode))
query.order = ""
to_param = Lookup("clients","cuscode = "+quote(alltrim(tbl.cuscode)),"alltrim(email)")

cc_param = a5_eval_expression("",local_variables())
bcc_param = a5_eval_expression("",local_variables())
subject_param = "Monthly account from Laprima S.L."
message_param = "Your attached account covers the month of "+cmonth(date())+"."

ui_msg_box("",""+tbl.cuscode)
:report.SaveAs("AccMonthly","PDF",query.filter,query.order,a5.get_path()+chr(92)+"temp"+chr(92)+" Acct "+tbl.cuscode+".pdf")
email_send(to_param,subject_param,message_param,a5.get_path()+chr(92)+"temp"+chr(92)+" Acct "+tbl.cuscode+".pdf",)
sleep(1)
tbl.fetch_next()
end while

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Stan, Thanks. Sorry but I don't know what you mean by "Create a directory under your database directory, temp, and let's try it this way."
The database directory within A5 directory is called Laprima, do want me to make another directory called "temp" inside that or inside the A5 directory?
I tried just amending the code inside the account form on the send button, under the OnPush tab and when I try to save it I get the message "error - expected value for the line;
"email_send(to_param,subject_param,message_param,a5.get_path()+chr(92)+"temp"+chr(92)+" Acct "+tbl.cuscode+".pdf",)"
I understand that chr(92) is calling the 92nd character from the character map.
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

The new code is designed to save the reports in the temp directory under the database directory structure. Looks like there is a trailing comma after .pdf" that shouldn't be there. Remove that and you should be ok.


a5.get_path()+chr(92)+"temp"+chr(92)+" Acct "+tbl.cuscode+".pdf"

says

drive_path_of_current_database\temp\Acct current_customer_code.pdf

the parts in red are determined at the time the line is executed.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Again,
This is the latest;
I get the weirdest message in the code window when I try to save it, it says "missing end while command on line 38" the end while command is, or course there but I put another one on line 38 (4 lines further down in blank space) and it saves.
I put at temp folder in the folders list in the A5V8 folder.
When I push the button now, I get a tiny window that says "002" with an ok button. I have to push the ok button and there appears a window that now says "004", I push it and it keeps re-appearing, over and over. So I have to force A5 to stop again.
The 002 account is emailed o.k.
In the temp folder there is one pdf file called "002" but when opened it is actually the 004 pdf.
Sorry about this !
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

The code originally posted is in a message board code window. The last line, not visible unless you drag down, is

end while


Attaching text file of complete code.
code.txt

Probably still has that extra comma.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Sorry, still the same.
I did notice the end while command on the original code, I just had to put another one in 4 lines later. The new code though (without the comma) does now save properly but I still get the little window to press ok etc. as last time.
Attached is the latest.

Updated Laprima.zip

Regards,
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

Works fine here testing sending the reports to myself.

I still get the little window to press ok etc

I left in the ui_msg_box() below just to display the current customer being processed. Delete that line if you don't want to see the customer codes in the message box.

Works fine with your sample database just sent.

received.pngaccount.png