Who doesn't have lots of monkey jobs (mundane manual work) we have to do every day? I wish I could have my teeth brushed automatically everyday.... That is not possible. But it is possible to automate reporting if we have it canned. I now have many emails with canned reports sitting in my inbox every morning before I wake up. This is improving my productivity like leaps and bounds without some expensive or crazily complex processes. I am quite satisfied with the result given the hacking nature of it.
Attached is the script in blue (saved it as reporter.vsb). It is very easy to schedule it in Windows scheduler to make the machine run a query, format the output, and send the report to folks automatically. A snapshot of how to schedule it in scheduler: Action is to 'Start a program', then 'wscript' is the name of the program.
The arguments would be something like this:
reporter.vbs filename sqlname subject liuyunliu@gmail.com,liuyunliu@yahoo.com
Following script is almost self explanatory. It gets a list of arguments:
1. filename: filename to save output from sql query
2. sqlname: file where the sql query is saved
3. subject: subject for the email
4. a list of emails in to: section of email
Nothing fancy here, but it does the job.....One thing to keep in mind. Since my data is on Greenplum, I have the SQL connection and execution through psql.exe. Make sure you get PSQL.EXE setup with saved password. Maybe I will share how to do that in a different post.
Leave a comment if you have questions or need help setting this up.
'Read me:
'how to use the script:
'wscript reporter.vbs filename sqlname subject emaillist:1;2
'remember to set up the SQL utility. I use psql.exe for greemplum....
filename=Wscript.arguments(0)
sqlname=wscript.arguments(1)
subject=wscript.arguments(2)
emaillist=wscript.arguments(3)
Set WshShell = WScript.CreateObject("WScript.Shell")
intReturn =WshShell.Run("psql.exe -d p1gp1 -h xxx.xxxx.xxxx -p 5432 -U username -H -f " & sqlname & " -o " & filename , 0,TRUE)
'These constants are defined to make the code more readable
Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f
Set fso = CreateObject("Scripting.FileSystemObject")
'Open the file for reading
Set f = fso.OpenTextFile(filename, ForReading)
'The ReadAll method reads the entire file into the variable BodyText
BodyText = f.ReadAll
'Close the file
f.Close
Set f = Nothing
Set fso = Nothing
Set olApp = CreateObject("Outlook.Application")
Set olMsg = olApp.CreateItem(0)
With olMsg
.To = emaillist
.Subject = subject
'.BodyFormat = olFormatHTML
'.HTMLBody = "<HTML><BODY>Enter the message text here. </BODY></HTML>"
.HTMLBody =BodyText
.Attachments.Add filename
'.Display
.send
End With
''''This script is partly based on prior work of following.
''''http://www.paulsadowski.com/wsh/cdo.htm#LoadFromFile
No comments:
Post a Comment