Help with send email loop

[twwtc]

I need help with some simple xbasic. I have a button on a form that was created using action scripting to send a report via email. It works as it should, but for only one record. I need it to loop thru the entire table (or all records that are within the query.filter)

I would also like to put in the �pm.from_alias� if possible.

Below is the code generated by action scripting:


query.filter = "Current=\"Y\""
query.order = "recno()"

to_param = a5_eval_expression("=if(G1notify=\"Y\".and.G2notify=\"Y\",alltrim (G1email)+\",\"+alltrim(G2email),if(G1notify=\"N\".and.G2email=\"Y\",alltrim(G2email),if(G1notify=\"Y\".and.G2notify=\"N\",alltrim(G1email),\"\")))",local_variables())

cc_param = a5_eval_expression("",local_variables())

bcc_param = a5_eval_expression("",local_variables())

subject_param = a5_eval_expression("Anson Golf Tournament Results",local_variables())

message_param = a5_eval_expression("Open PDF to view tournament results",local_variables())


:Report.Send("Results2-ColTwo","PDF",query.filter,query.order,"default",to_param,subject_param,message_param,cc_param,bcc_param)

Thanks
Tony

[Stan Mathews]

Re: Help with send email loop

I would also like to put in the �pm.from_alias� if possible.

The from alias you mention is part of sending an email, not a report.

I need it to loop thru the entire table (or all records that are within the query.filter

What table? (name)

[twwtc]

Re: Help with send email loop

Stan
The table name is "tourney"
Thanks for the quick response!
Tony

[Charles Hoens]

Re: Help with send email loop

HI!

There are many ways to do this and I'm sure Stan will give your several, but what I found good to use was Netmailer. It is an Alpah5 addd-on and helps you to automate mailings like this. You can select the records you want to use, save it all in a config file and then call it up within xbasic.

Just a thought...

Charlie

[Stan Mathews]

Re: Help with send email loop

Sorry, got distracted. Assuming the G1notify, G2notify, G1email, G2email are fields in the tourney table.......

If you want to have the button on a form based on the tourney table use option 1. If the button is on a "menu" dummy table, option 2.

tbl = table.current() ' [COLOR="Blue"]option 1[/COLOR]
'tbl = table.open("tourney") ' [COLOR="blue"]option 2, comment out first line and uncomment this (and last line) for option 2[/COLOR]
query.filter = "Current=\"Y\""
query.order = "recno()"
query.description = "Temporary Query"
query.options = "I"
ix = tbl.query_create()
recs = ix.records_get()
if recs = 0
	end
end if
tbl.fetch_first()
while .not. tbl.fetch_eof()

to_param = if(tbl.G1notify="Y" .and. tbl.G2notify="Y",alltrim (tbl.G1email)+","+alltrim(tbl.G2email),if(tbl.G1notify="N".and.tbl.G2email="Y",alltrim(tbl.G2email),if(tbl.G1notify="Y".and.tbl.G2notify="N",alltrim(tbl.G1email),"")))
cc_param = ""
bcc_param = ""
subject_param = "Anson Golf Tournament Results"
message_param = "Open PDF to view tournament results"

:Report.Send("Results2-ColTwo","PDF",query.filter,query.order,"default",to_param,subject_param,message_param,cc_param,bcc_param)
tbl.fetch_next()
end while
'tbl.close [COLOR="Blue"]'only with option 2[/COLOR]

[twwtc]

Re: Help with send email loop

Stan
Thanks, I will have time tonight to give this a try and post the results.
Tony

[twwtc]

Re: Help with send email loop

Stan

I used "option 1" and it worked, however, a separate email was brought up for each record (one or two email addresses) that was within the query (could be up to 50 separate emails).

I had hoped that one email could be sent for 1 or 50 golfers that wanted to be notified.

Is that possible?

Thanks for your help,
Tony

[Stan Mathews]

Re: Help with send email loop

It is possible. I'll get back to you tomorrow.

[Tom Henkel]

Re: Help with send email loop

I used a "report.save_as", then attached the report to the e-mails using the e-mail_send command. This sends them in "batch" without you having to click on each one to end it. see attached.

tblC = table.open("clients")
text1 = get_from_file("C:\FORECAST\timing.txt")
a1.initialize(text1)
N = val(a1[1]) 'N is the number of e-mails to do in a batch
L = val(a1[2]) 'L is the number of seconds to wait after each e-mail
M = val(a1[3]) 'M is the number of seconds to wait between batches
Skip = val(a1[4]) 'SKIP is the number of e-mails that had been sent in a previous attempt.

tblC.index_primary_put("Location")
tblC.fetch_first()
dim I as N
dim K as N
J=0
K=0
I=0
h = 0
while .not.tblC.fetch_eof()
	if tblC.Email_addr = "" .or. marked("clients")
		tblC.fetch_next()
	  else
		if Skip >0
			while h < Skip
				tblC.fetch_next()
				h = h+1
			end while
		end if
		if I < N 
			J=J+1
			I=I+1
		if tblC.Report_Hdr <>""
			loc1 = tblC.Report_Hdr
		  else
	 	 	loc1 = tblC.location
		end if	

[COLOR="Red"]			pdf_filename = "C:\Forecast\reports"+chr(92)+trim(loc1)+"_Pavement_forecast.pdf"
[/COLOR]			msg_text = "Attached is the Pavement Condition Forecast for "+strtran(loc1,"_"," ")
			param_to = trim(tblC.Email_addr)
			param_subject = alltrim(strtran(loc1,"_"," "))+" Pavement Condition Forecast"
			param_cc = ""
			param_bcc = ""
			param_msg = msg_text
			[COLOR="Red"]param_attachments = pdf_filename[/COLOR]
                        email_Send(param_to,param_subject,param_msg,param_attachments)
			tblC.fetch_next()
			sleep(L)
		  else
	  		sleep(M)
			I=0
			K=K+N
			'tbl1.fetch_next()
		end if
	end if
end while

Just remember, when doing batch e-mails, your ISP may think you're spamming, so they will shut down the outgoing port. That is why I put the timing delays in.

Tom

[twwtc]

Re: Help with send email loop

Tom

Thanks for the response. I may give it a try later today or tomorrow, but I am so limited in my knowledge of XBasic that I'm not sure I can substitute my information into your example and make it work.

I am holding out hope that Stan can advise later today on how to make his example work.

Thanks to each of you and to all the many people that take the time to give advice and help to those of us that are a little "XBasic Challenged"

Thanks
Tony

[Stan Mathews]

Re: Help with send email loop

Was waiting to see if you wanted to give that a go. This should work if the email client supports as many send to's as you have.

tbl = table.current() ' option 1
query.filter = "Current=\"Y\""
query.order = "recno()"
query.description = "Temporary Query"
query.options = "I"
ix = tbl.query_create()
recs = ix.records_get()
if recs = 0
	end
end if
tbl.fetch_first()
'initialize variable for accumulation each to_param value
send_to_list = ""
while .not. tbl.fetch_eof()
to_param = if(tbl.G1notify="Y" .and. tbl.G2notify="Y",alltrim (tbl.G1email)+","+alltrim(tbl.G2email),if(tbl.G1notify="N".and.tbl.G2email="Y",alltrim(tbl.G2email),if(tbl.G1notify="Y".and.tbl.G2notify="N",alltrim(tbl.G1email),"")))
send_to_list = send_to_list+to_param+","
tbl.fetch_next()
end while
cc_param = ""
bcc_param = ""
subject_param = "Anson Golf Tournament Results"
message_param = "Open PDF to view tournament results"
'remove initial comma
send_to_list = substr(send_to_list,2)
:Report.Send("Results2-ColTwo","PDF",query.filter,query.order,"default",send_to_list,subject_param,message_param,cc_param,bcc_param)

[Tom Henkel]

Re: Help with send email loop

try this:

tblC = table.open("tourney")

N = 20 'N is the number of e-mails to do in a batch
L = 5 'L is the number of seconds to wait after each e-mail
M = 60 'M is the number of seconds to wait between batches
Skip = 0 'SKIP is the number of e-mails that had been sent in a previous attempt.
param_to = ""
query.filter = "current = \"Y\""
query.order = ""
ix = tblc.query_create()
I = 1
tblC.fetch_first()
while .not.tblC.fetch_eof()
	if I < N
		param_to = ""
		if G1notify = "Y"
			param_to = alltrim(g1email)
		end if
		if g2notify = "Y"
			if param_to <> ""
				param_to = param_to +","+alltrim(g2email)
	  		  else
				param_to = alltrim(g2email)
			end if
		end if			
		pdf_filename = "YOUR SAVED PDF FILE"   <<<<<=================  
		msg_text = "Open the attached PDF to view tournament results"
		param_subject = "Anson Golf Tournament Results"
		param_msg = msg_text
		param_attachments = pdf_filename
	        email_Send(param_to,param_subject,param_msg,param_attachments)
		tblC.fetch_next()
		I = I+1
		sleep(L)
	  else
	  	sleep(M)
		I=0
		'tbl1.fetch_next()
		end if
	end if
end while

[twwtc]

Re: Help with send email loop

Stan
I tried your example and could not send any emails.
A error message was returned:

"MAPI ERROR - One or more recipients were invalid or did not resolve to any address. (MAPI_E_INVALID_RECIPS)

Just so you'll know, I have things set for the purpose of simplicity, that two golfers from one record and one golfer, from another record are set to receive emails. (so three total at this time)

Thanks
Tony

[twwtc]

Re: Help with send email loop

Tom

Thanks for inserting my information. I will give it a try!

Thanks
Tony

[Tom Henkel]

Re: Help with send email loop

Tony,
Just remember, you need to create the PDF file with a report.saveas(). check the help file for all the formatting parameters. this is what I'm using in a loop to create specific reports for specific clients.

		pdf_filename = "C:\Forecast\reports"+chr(92)+trim(loc1)+"_Pavement_forecast.pdf"
		if file_exists(pdf_filename)
			file_remove(pdf_filename)
		end if
		filt1 = "location="+quote(trim(tbl2.Location))
		query.filter = filt1
		query.order = ""
		[COLOR="Red"]report.SaveAs("Conditions Letters",[COLOR="Navy"]"PDF"[/COLOR],query.filter,query.order,pdf_filename)[/COLOR]

as far as the mapi errors, check the e-mail addresses for accuracy.

Tom