Email reports based on grouping

[kdankwah]

I would like to email a report that I created. The report is grouped by account number. What I want is to email each group to a specific email address (the directors in charge of the account), how can I do that?

The report will be printed in a pdf format. At the moment I print each report to pdf and email it from the pdf which is very cumbersome and time consuming. Can I use a script?

Thanks

Thanks

[kdankwah]

Re: Email reports based on grouping

I did this in Access and here is the code that I used. See below:

Private Sub cmdEmail_Click() 'RTIG DEPT
On Error GoTo Err_cmdEmail_Click
If IsNull(Me.cboMonthDD.Column(1)) Or Me.cboMonthDD.Column(1) = "" Then
Exit Sub
Else
DoCmd.RunMacro "Advanc_Services_200043"
End If
Exit_cmdEmail_Click:
Exit Sub

Err_cmdEmail_Click:
MsgBox Err.Description
Resume Exit_cmdEmail_Click

=======================

The Macro run the email object that has the usual "To", "From" etc Unfortunately I dont know how to do this in Alpha. Maybe this will help in someone giving me the heads up.

Thanks

[kdankwah]

Re: Email reports based on grouping

Disregard this posting, my IT people will not allow POP email configuration, we are using Exhange server and I dont know any way to go around this. I have figured how to do the report by parameter but the email will not go because of this.

Thanks for everything. Does anyone has any ideas. I dont want to go the gmail way because of the sensitivity of the reports.

Thanks again,

[Ed Barley]

Re: Email reports based on grouping

Hello K,

Take a look at the attached JPG. To give you some ideas.

We use Exchange Server at work, and of course Outlook is our default email program.

I Outlook all the time for sending PDF files.

Regards

Ed

[kdankwah]

Re: Email reports based on grouping

Thanks Ed,

With your help I have been able to set the email thru Outlook, ehhh had a slight pdf driver issues but that was resolved right away when the admin installed them for me. Its working at the moment but I dont know if I should have opened a new posting on this. The Xbasic below is what is running the parameter driven based on "costctr" to create the report in pdf format and email it to the right email address, which I manually do now.

Is there anyway I can automate this by inserting the email and associated "costctr" in this code so that it will run with the click of a button?

For instance [email protected] will be connected to costctr 200500 and so forth for all costctrs and emails associated with them, all hardcoded in the Xbasic code. I will love to do this but I dont know where in the code to insert this. Should I use a table with 2 fields, field1 (email address) and field2 (Costctr) and get the system to look at the table fields and go down each record and insert the information in the costctr and email "to" fields in this code.

I am open to any advice.

Thanks



==============================================
'Send a report, label, letter, form or browse layout as an attachment to an e-mail message. You can specify which records to send.

filter = " (Costctr = [varC->prms.Parameter1] )"
get_parameters_string = <<%string%
Parameter1|Costctr|Character|text box||
%string%
ok_label = "&OK"
cancel_label = "&Cancel"
dialog_title = "Parameters"

'Prompt for parameter values. Store results in a 'dot' variable called 'prms'
dim prms as p
prms = ui_get_parameters(dialog_title,get_parameters_string,ok_label,cancel_label)

if prms.last_button_pressed = "OK" then
'Replace parameters in the filter with actual values
query.filter = replace_parameters(filter,local_variables())

query.order = "Phone_num"
else
end
end if

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("telephone_by_month","PDF",query.filter,query.order,"default",to_param,subject_param,message_param,cc_param,bcc_param)

[kdankwah]

Re: Email reports based on grouping

I have been able to get this to work with the code below. I would like someone to show me how to get the system to pick the "Costctr" and "email address" shown in red from a table called email table with 2 fields i.e "Costctr" and "Emailadd". My reasoning is that I dont want to "hardcode" this into the code thus allowing me to make changes at one table anytime I have to.

Any help will be appreciated.

The main table/set name will be telephoneset.

Thanks

==================================================
'Date Created: 22-Apr-2008 03:13:26 PM
'Last Updated: 22-Apr-2008 03:21:02 PM
'Created By : udr
'Updated By : udr
'Send a report, label, letter, form or browse layout as an attachment to an e-mail message. You can specify which records to send.

query.filter = " (Costctr = \"2005AB\" )"
query.order = ""

to_param = a5_eval_expression("ev2armtr.com",local_variables())
cc_param = a5_eval_expression("",local_variables())
bcc_param = a5_eval_expression("",local_variables())
subject_param = a5_eval_expression("Telephone",local_variables())
message_param = a5_eval_expression("Testing",local_variables())


:Report.Send("telephone_by_month","PDF",query.filter,query.order,"default",to_param,subject_param,message_param,cc_param,bcc_param)
;

query.filter = " (Costctr = \"2005AC\" )"
query.order = ""

to_param = a5_eval_expression("[email protected]",local_variables())
cc_param = a5_eval_expression("",local_variables())
bcc_param = a5_eval_expression("",local_variables())
subject_param = a5_eval_expression("Telephone",local_variables())
message_param = a5_eval_expression("Testing",local_variables())


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

[Stan Mathews]

Re: Email reports based on grouping

To process all records in the email table table...

tbl = table.open("email table")
'if running from a form where the email table is a child of the set use
'tbl = table.current(#)
'instead of the first line above
'where # is the position from the top of the set structure counting down, parent table as 1
tbl.fetch_first()

while .not. tbl.fetch_eof()
	
query.filter = "Costctr = "+quote(tbl.costctr))
query.order = ""

to_param = a5_eval_expression(alltrim(tbl.Emailadd),local_variables())
cc_param = a5_eval_expression("",local_variables())
bcc_param = a5_eval_expression("",local_variables()) 
subject_param = a5_eval_expression("Telephone",local_variables()) 
message_param = a5_eval_expression("Testing",local_variables())

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

tbl.fetch_next()
end while
'if using table.current() above, don't close table per next line
tbl.close()

[kdankwah]

Re: Email reports based on grouping

Hi Stan,

Thanks for coming to my aid.
I applied your method but unfortunately I am getting a incorrect datatype on line 12. See below

==================
'Date Created: 22-Apr-2008 03:13:26 PM
'Last Updated: 23-Apr-2008 12:21:48 PM
'Created By : udr
'Updated By : udr
'Send a report, label, letter, form or browse layout as an attachment to an e-mail message. You can specify which records to send.

'tbl = table.open("telephone database")
'if running from a form where the email table is a child of the set use
tbl = tables_in_set("telephone database_set")
'instead of the first line above
'where # is the position from the top of the set structure counting down, parent table as 1
tbl.fetch_first()

while .not. tbl.fetch_eof()

query.filter = ("Costctr = "+quote(tbl.costctr))
query.order = ""

to_param = a5_eval_expression(Email,local_variables())
cc_param = a5_eval_expression("",local_variables())
bcc_param = a5_eval_expression("",local_variables())
subject_param = a5_eval_expression("Telephone",local_variables())
message_param = a5_eval_expression("Testing",local_variables())

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

tbl.fetch_next()
end while
'if using table.current() above, don't close table per next line
tbl.close()

[kdankwah]

Re: Email reports based on grouping

Nerver mind I got it working. Thanks Stan

[Ed Barley]

Re: Email reports based on grouping

Hey K,

Gland Stan was able to get you to the end.

What made the code work for you, so we all can learn.

Regards

Ed

[kdankwah]

Re: Email reports based on grouping

Since I was using the table set I should have used this 'tbl = table.current(#)instead of this tbl = tables_in_set("telephone database_set"). I did the change because it was not working at first. My bad.

Anyway, I have been able to send my emails to all the directors like I want to and it worked seemlessly like a charm the reports are grouped based on the email table fields costctr and email address.

Thanks Stan

[kdankwah]

Re: Email reports based on grouping

Stan,

One quick, quick question, can you put 2 different reports on this code. In other words, the system will run the first one and then run another report right after the first and email the 2 reports on the same email. The filters will be the same.

Thanks

[Stan Mathews]

Re: Email reports based on grouping

In other words, the system will run the first one and then run another report right after the first and email the 2 reports on the same email.

No. the report.send() generates the report and sends it immediately. You could have two report.send() statements consecutively but the reports would be sent in separate emails.

'.............earlier lines
message_param = a5_eval_expression("Testing",local_variables())

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

To include two reports in one email you will have to report.save_as() for each report, capturing the saved as names, and then send them with FILE.SEND().

[kdankwah]

Re: Email reports based on grouping

Thanks Stan.