Emailing individual reports to many customers automatically

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

Are you using your default email client or the A5 one? - Default which is Outlook
Am I correct in saying that the code should save the pdfs and then send them from that file? - Yes
Is it important that the customer codes are not consecutive? - No
If that is the case, if you remove the email_send portion should the pdfs be saved in the temp folder which is inside the database folder? - Yes


And if you comment out both the save and send lines but leave the ui_msg_box() you should see that message for each record/customer with their customer code.

I'm using v11 but the code only uses v5 or later commands.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Mmm I wonder if I should try a trial version of V11 if I can get it.
I'm convinced that the code that I'm using is the same as yours and I've tried it with using default email client which is Outlook Express and with the A5 client.
I've tried it without the email line but it doesn't save any pdfs in the temp folder. I've tried it without either and it won't go any further then the second record.
The best I can do is it sending the first record over and over again indefinitely until I do a Ctrl-Alt_Delete. It sends the emails but they are all the first record over and over again and there is just one instance of the first record's pdf left in the temp folder.
Just as a matter of interest, should there be a colon in front of Report.SaveAs ? It doesn't make any difference if I delete it though.
Is there anywhere I can get better help than in the A5 help file? There are a lot of commands in the code that aren't in the help file and the online help is even worse? Also it rederfs to a script genie that I can't find.
Regards,
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

Just as a matter of interest, should there be a colon in front of Report.SaveAs ? It doesn't make any difference if I delete it though.

The colon is optional but proper.

Just for giggles, try this. New button on your form. The save as and send lines have been commented out and will not execute.

fil = file.create(a5.get_path()+chr(92)+"temp"+chr(92)+"Accts.txt",FILE_RW_EXCLUSIVE)
parentform.commit()
tbl = table.current()
tbl.fetch_first()
debug(1)
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())+"."
if filefind.get(a5.Get_Path()+chr(92)+"temp",FILE_FIND_DIRECTORY,"PN") = ""
	dir_create(a5.Get_Path()+chr(92)+"temp")
end if
':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")
fil.write_line("Processed account "+tbl.cuscode)
'sleep(1)
tbl.fetch_next()
end while
fil.flush()
fil.close()

The debugger will appear. Use the F12 key to step through the script (if it gets stuck in a loop you can use the HALT toolbar button.

This modification creates a text file, Accts.txt in the same directory the pdf's should be saved. The text file should contain a line for each account processed like

Processed account 002
Processed account 004
Processed account 005
Processed account 006
Processed account 011
Processed account 012
Processed account 014
Processed account 015
Processed account 016
Processed account 017
Processed account 018
Processed account 019
Processed account 020
...........
...........
Processed account 075

[Ray in Capetown]

Re: Emailing individual reports to many customers automatically

The best I can do is it sending the first record over and over again indefinitely until I do a Ctrl-Alt_Delete. It sends the emails but they are all the first record over and over again and there is just one instance of the first record's pdf left in the temp folder.

This sounds suspiciously like your code problem
1. The creation and send works as it should
2. Something in the table open -

 while .not. tbl.fetch_eof() ...  fetch_next() ... fetch_ ... eof()

loop is corked up, like the table is being reopened each loop and starting again at the first record .. .. endlessly

...or.... fetch_first is inside the loop???

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

I'm convinced that the code that I'm using is the same as yours

Tony,

You do realize you can copy the code posted here and paste it into a code window on a button, etc. No need to retype anything with the resultant possible typographical errors.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Thanks Ray. Yes I agree, something is interrupting the process from returning from the end of the loop to the beginning, but it's not the code, I've checked it space for space and dot for dot, it's the same and I Ctrl C'd and Ctrl V'd it. Also it's not consistent between the 2 machines that I've got, sometimes it goes to the second record, saves it in the temp folder but still sends the 002 records's report. I've also tried changing the sleep command to a high value in case the emails are backing up or whatever but still the same, I'm just going to try the debugger code that Stan sent me.
Regards,
Trevor.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Stan, Yes thanks, that's what I've been doing, I'm just going to try the debugger code you sent.
Trevor.

[[email protected]]

Re: Emailing individual reports to many customers automatically

O.k. Guys, this is interesting I've tried the debugger, it goes through the code multiple times until I stopped it myself after I thought it had gone well past the 6 records in the clients (dummy) table. Sorry Stan, you will have a few emails from me !!

The interesting part is that the code written in the Accts.txt file in the Temp folder goes up to code 020. In my example it gets the email addresses from the 'Clients' table. This is the dummy table with only 6 records in it. Now the accounts report that is being sent is from the account table (real) which is a part of the 'AccountSet' and has 32 records (not consecutive). So the code of course still looks for the remaining accounts past the first 6 addresses but can't find a further email address. In my version, it does however keep sending them to one of the 2 email addresses I have used in the dummy file (Clients). It also only sends the first record's account (002). I stopped the process at 020 so it will be interesting to learn what reports are sent to Stan, both the first 6 and then up to 020.
Does it matter that the clients table is not a part of the AccountSet? It says in the help file 'external' table but I don't wholly trust this help file.
Also, assuming we can solve the rest of the problem now we now the loop is working, would it be better to control which records the code processes by the existence or not of an email address?

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

Does it matter that the clients table is not a part of the AccountSet?

No. I saw the limited number of records in the clients table but assumed I had an abbreviated version. The lookup of the client email address doesn't have any check to see if it is found so it keeps on even if no email address is found.

We can wrap the save report and email if no address found.

fil = file.create(a5.get_path()+chr(92)+"temp"+chr(92)+"Accts.txt",FILE_RW_EXCLUSIVE)
parentform.commit()
tbl = table.current()
tbl.fetch_first()
debug(1)
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)")
if alltrim(to_param) > "" 'only execute this section if an email address is found
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())+"."
if filefind.get(a5.Get_Path()+chr(92)+"temp",FILE_FIND_DIRECTORY,"PN") = ""
	dir_create(a5.Get_Path()+chr(92)+"temp")
end if
':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")
fil.write_line("Processed account "+tbl.cuscode)
'sleep(1)
end if 'end of section to create and send email if address found
tbl.fetch_next()
end while
fil.flush()
fil.close()

[[email protected]]

Re: Emailing individual reports to many customers automatically

[Just for giggles, try this. New button on your form. The save as and send lines have been commented out and will not execute.>

Am I going mad? I copied and pasted the text (via a pen drive to the machine that works best) and the code in the Account form has the to.param = Lookup line was also commented out and a to_param = "I assume, Stan's email address".
Now - looking back to the code on the forum that isn't there, where has it come from?

Anyway, it gave me the idea to do the same, so I changed the code, un-commented the SavAs and the Send_email lines and made the to_param line to my email address. The result is that the debugger goes around as it should but every email received has the first record's pdf file attached. The temp file still just has the 002 and 004 pdfs in it. The 004 pdf file does contain the 004 account and not the 002 one.
To clarify - 9 emails sent before stopping,
All emails contain the 002 pdf
Temp file has Acct 002 and Acct 004 in it plus the debuggers txt file which shows 9 accounts processed, all with 'Processed account 002'.
Does this tell us anything?
Regards,
Trevor.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Stan,
Yes, the one to only continue if an email address is found stops after the first 6 records in the dummy Clients table even though I went through the loop some 9 or 10 times.

[[email protected]]

Re: Emailing individual reports to many customers automatically

[We can wrap the save report and email if no address found.]

I should have added to the previous comment that with the latest code, the Acct.txt file gives the correct customer codes from the Account table (002, 004, 005, 006, 011 and 012).
Regards,
Trevor.

[Stan Mathews]

Re: Emailing individual reports to many customers automatically

even though I went through the loop some 9 or 10 times

The loop will continue until all accounts records are processed but only send reports to those customers who have email addresses.

[[email protected]]

Re: Emailing individual reports to many customers automatically

[The loop will continue until all accounts records are processed but only send reports to those customers who have email addresses.]

I un-commented the :report.SaveAs line and the Accts.txt reads;
Processed account 002
004
005
006
011
012
as it should.
I put back the ' in the :report.SaveAs line and it reverted to 002 about 20 times and then, 004, 005, 006, 011, and 012 at the bottom of the list. I had to stop the p A5. Neither version showed any error in the debug process. The only pdf save was 002 - once. I thought that maybe the code was running too fast for the SaveAs (because only the first record was saved and the txt file reported most of the saves as the first record) so I un-commented the sleep(1) and changed it to sleep(3) it made no difference. So it seems that the problem may be the :report.SaveAs line ?
Trevor.
Trevor.

[[email protected]]

Re: Emailing individual reports to many customers automatically

Hi Stan,
I downloaded a trial version of A5V11, installed the original code (the one that you said works there) and it works - properly - first time. I'm going to test it a few more times, then with the real customers table. I'll let you know in a few days. Amazing !
Regards,
Trevor.