Old Chestnut e-mail multi page report to different people

[seat172]

Hi complete numpty when it comes to any sort of code so please be gentle. I have a report that picks up orders for our deep sea suppliers asking them to confirm shipment items and dates. Each page of the report is for a different supplier, the page header contains the supplier details. I would like to e-mail automatically each page to the respective supplier. This report could be 1 page/1 supplier or 20 pages/20 suppliers.

Many Thanks

 

[shudini]

in what format, and using what client, were you hoping to use to email the reports? Also, where do you keep a list of email addresses and supplier id's

 

[seat172]

E-mailing reports

All the data, including the e-mail address is in the various tables on our system. I can query out the required data and have got the report as i need it. As the data is dynamic I want to run the report/query and e-mail it out the results using the address on the report page header. The report needs to be editable as i want a reply from our suppliers so RTF format would be good. We are running a SMTP/Exchange e-mail setup using outlook on the clients.

I could do this one report at a time but it so "not up to date"

Many Thanks

 

[shudini]

since you consider yourself 'numpty' when it comes to programming, i would offer you this option with less programming.

I would presume you would create some kind of form that would allow you to create a command button on it that would say 'email reports'. The idea would be to put all your programming behind the OnClick event of the button. The next step is to create a recordset that would allow you to use your data as variables within your programming.

dim rst as dao.recordset 'initialize recordset
set rst=currentDB.openrecordset "SELECT..." 'populate recordset with data,
                                                 'you will need all the supplier id's
                                                 'and email addresses
rst.moveFirst
do until rst.EOF 'do the following for each record until the end

DoCmd.OpenReport "MyReport", acViewDesign, , , acHidden 'open report in design view
With Reports!MyReport 'apply a filter so only one report is generated at a time
    .Filter = "SupplierID=" & rst!SupplierID
    .FilterOn = True
End With
DoCmd.Close acReport, "MyReport", acSaveYes 'close and save report

dim subj as string
dim msg as string

subj="EmailSubject"
msg="Text of Email Message"

DoCmd.SendObject acReport, "Sample_Report", "RichTextFormat(*.rtf)", rst!EmailAddress, [CC or Blank], [BCC or Blank],subj, msg,False

loop
set rst = nothing

The only downside of doing it this way is that you will get prompted initially by Outlooks security message, since it will detect an outside program trying to access it. There are ways around it, but this gets more complicated already.

Good luck!

 

[seat172]

E-mail from reports

Thanks for your help, i will give it a go i can get most things done but am afraid code is my undoing. I suffer from dyslexia which makes things difficult enough at times but i have learned to get by. Learning code puts me off completely, i am getting too old! Although i have just bought a SQL book???