Chore to EMAIL

[bunchukokoy]

Hi Guys,

I have this requirement from my client in which a chore will have to email an excel file everyday. This excel file is formatted (color, font, data) based on a cube view. But this excel should only contain values at all (not DBRW, not connected to TM1). My problem is, is it possible for a chore to execute whatever it is, a vb script or a service, that will open an excel and will log in to TM1 server, then open a cube view and then slice it. Then after the slice, a macro will auto execute to format the contents of the excel file. Then after the excel has been formatted, it will automatically close and will be attached to an email with default settings for recipients and message body.

Are all of these possible TM1 geniuses?

Thanks.

Bunch

[rmackenzie]

Hi - this is totally possible, I have done it several times, in several variations... but it isn't a small job and there are tons of ways that people want it configured and tweaked. Have you made a start on it at all... perhaps you will get more assistance if you ask us about the specific part that you are stuck on?

Or perhaps you just need some encouragement to have a go... in which case, go for it!

[bunchukokoy]

Hi Sir Mackenzie,

Actually, I don't have an idea on how can I call a service that will open an existing macro-driven excel file (2003 specifically) and how to create that service, I'm thinking that's thru' vb script. I know I can automatically execute my macros once that Excel is opened, provided that the Macro is always enabled. Once it's opened, using TM1 API, I can auto-connect to a particular Tm1 server. Am I right? Then there my macros will do the formatting. Even sending this excel file after it's been closed will be easy. I think it'll all be easy once the excel file has been opened, by a service thru' a chore, I don't exactly have an idea. But what will my chore be needing and how can I integrate that with my chore?

Thanks a lot.

bunch

[rmackenzie]

Actually, I don't have an idea on how can I call a service that will open an existing macro-driven excel file (2003 specifically) and how to create that service, I'm thinking that's thru' vb script

You can have an ExecuteCommand statement in the TI run by the chore that calls a batch file that simply runs Excel and passes in the name of the workbook:

Code: Select all

"C:\Program Files\Microsoft Office\Office11\EXCEL.EXE" "D:\YOUR_DATA\YOUR_TM1_REPORTS\ETC\ETC\YOUR_REPORT.xls"

One thing to watch out for here is that the name of the account running your TM1 service will need enough permissions to do this.

Then you need to write some code in the Workbook Open event of that Workbook that does the following steps:

• Load the Perspectives add-on
• Connect to a server
• do all your other stuff like slicing, colouring and e-mailing, etc

Here's a bit of help for the first two steps... the others you will have to do on your own!

Code: Select all

Private Sub Workbook_Open()
    
    Dim wbTM1AddIn As Workbook
    
    'open perspectives
    Set wbTM1AddIn = Workbooks.Open("C:\Program Files\Cognos\TM1\bin\tm1p.xla")
    
    'login to a server
    Run "N_CONNECT", "tm1server", "admin", "apple"
    
    'do some other stuff here
    '....
    
End Sub

[bunchukokoy]

Thank you Sir Mackenzie! Indeed a big help.

[viswaraju]

hi,
can you please suggest how to send a Email in TM1.
Like a process has finished, user wants to know what are all the process has been finished.
Give me examples how to Email in TM1 or any document regarding this.

Thanks and regards

TM1 9.5
Excel 2007.

[qml]

can you please suggest how to send a Email in TM1.

Have you actually bothered searching the forum for this particular topic? Have you?

You already asked this question before and even before that and got pointed in the right direction.

There's plenty to find if you're willing to make the tiniest bit of effort yourself instead of having everything handed to you on a silver platter.

Take this thread for example where lotsaram and David Usherwood summarise the two most popular approaches very well. And there's plenty more.

[viswaraju]

sorry my friend has used my forums id for this.