Automated Reports

Fisherman

A couple of questions for the gurus...

I've noticed that when I generate a report, I can click SEND -> report by email, and generate an HTML Email.

Is there a way to automate this. I need to schedule a report to run every night, and I would like to send it by email if I could.


Also - I'm trying to find information how to programmatically call a report with filters. I've read up on the REPORT.Run command, but I'm not sure I understand how to apply the filters to the report.

Thanks!

Fisherman

OK - I've figured out how to filter the report, so scratch that from my questions.

I'm putting together a quick scheduler that will call codeunits. Nothing fancy. I'm going to use it to call my report and have it print to a pre-defined printer.

I would still like to be able to send the report by email from the codeunit if anyone can help me on that...

themave

you can use the built in job scheduler to run the report, you don't have to make your own scheduler. Sorry about the rest, not a programer.

although if you do a search, you should find a lot of info on it, as this is one of the most asked questions on here.

your basic approach is going to be to get a pdf printer driver that can accept input programitically and then add some code to the report to send the info to pdf printer.
Here are a few examples of pdf printer people have recommended

http://www.altusbusinesssolutions.com/
http://www.pdf995.com/

good luck

Fisherman

hey!! look at that... but I don't have permissions for it. crap.

nunomaia

Other way around, is to save the report as HTML using the SAVEASHTML function, and then using a component to send e-mail of the generated file

Fisherman

Thanks.

Yeah - I had thought about falling back on the old CDO through automation, but I'm not that far along in C\AL yet.

nunomaia

The component that I was referring, it’s an external component. Right now I’m using a custom made .NET 2.0 COM Object, to send the e-mails, because the CDO were missing some features.

Sorry if I’m not have explained correctly about the component.

Fisherman

Didn't think about that.

I guess I could write something quickly in .Net and expose it to COM... of course... System.Web.Mail is just a wrapper for CDO

Waldo

That's something we did. We made a wrapper, and use it in Navision. Mostly for mails in HTML format, mails with multiple attachments ... . You know, what's not possible with the default dll...

Fisherman

Along these lines

I've written a code unit that runs my report with the settings that I would like.

I've REPORT.SAVEASHTML to save the report into a working file in the \client directory, and it's working great.

What I'm trying to do now is to use CDO to send the resulting HTML File, but I'm getting an error. I'm attaching the code below.

Conf is my CDO.Configuration. MSG is my CDO.Message. I've added the appropriate Automation objects to my code unit as these variables. Anything that says 'cdo...' as an item indexer is set up as a Text Constant with the Microsoft CDO Schema definition as the text.

Conf.Fields.Item(cdoSMTPSendUsing).Value := 2;
Conf.Fields.Item(cdoSMTPServer).Value := ServerName;
Conf.Fields.Item(cdoSMTPServerPort).Value := Port;
Conf.Fields.Item(cdoSMTPConnectionTimeout).Value := 10;
Conf.Fields.Item(cdoSMTPAuthenticate).Value := 1; //BASIC
Conf.Fields.Item(cdoSendUserName).Value := 'username';
Conf.Fields.Item(cdoSendPassword).Value := 'password';
Conf.Fields.UPDATE;

Msg.Configuration := Conf;
Msg.To := EmailTo;
Msg.From := 'Report Generator <user@derbyllc.com>';
Msg.Subject := 'Report Output';


filEmail.OPEN(FileLocation);
filEmail.CREATEINSTREAM(strm);

strm.READ(Msg.HTMLBody.Value);
Msg.SEND;

The error I'm getting when I try to compile the code unit is

---

Microsoft Business Solutions-Navision

---

A field from a record variable was expected. For example:

Record.Field
Customer.Name

---

OK

---

When I press 'OK', the cursor is on the line with Msg.To property.

Any ideas?

[EDIT] - nevermind. I changed msg.To to msg."To", and it allowed it to compile.

Fisherman

It's working...

IF (CREATE(Conf) AND CREATE(Msg)) THEN
   BEGIN

      //Schema Definitions for CDO Config from Microsoft. Navision requires Variants for COM Interop
      cdoSMTPSendUsing := 'http://schemas.microsoft.com/cdo/configuration/sendusing';
      cdoSMTPServer := 'http://schemas.microsoft.com/cdo/configuration/smtpserver';
      cdoSMTPServerPort := 'http://schemas.microsoft.com/cdo/configuration/smtpserverport';
      cdoSMTPConnectionTimeout := 'http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout';
      cdoSMTPAuthenticate := 'http://schemas.microsoft.com/cdo/configuration/smtpauthenticate';
      cdoSendUserName := 'http://schemas.microsoft.com/cdo/configuration/sendusername';
      cdoSendPassword := 'http://schemas.microsoft.com/cdo/configuration/sendpassword';

      //setup our mail configuration object
      Conf.Fields.Item(cdoSMTPSendUsing).Value := 2;
      Conf.Fields.Item(cdoSMTPServer).Value := ServerName;
      Conf.Fields.Item(cdoSMTPServerPort).Value := Port;
      Conf.Fields.Item(cdoSMTPConnectionTimeout).Value := 10;
      Conf.Fields.Item(cdoSMTPAuthenticate).Value := 1; //BASIC
      Conf.Fields.Update;

      //Set our options for the mail message
      Msg.Configuration := Conf;
      Msg."To" := EmailTo;
      Msg.From := EmailFrom;
      Msg.Subject := 'Report Output';

      //Attach our HTML report body and send
      //CreateMHTMLBody requires a URL, so we have to add the file:// accessor
      Msg.CreateMHTMLBody('file://' + FileLocation);
      Msg.Send;

      //CLEAR THE CDO OBJECTS
      CLEAR(Conf);
      CLEAR(Msg);
   END
ELSE
   //Error while instantiating CDO objects.  Cannot proceed.
   ERROR('Error while instantiating CDO COM Objects.  Cannot Proceed.');