Can anyone help with examples of scripts that automate emails with the *.REP files as attachments? Can anyone help with examples of scripts that interface with Microsoft Word?
Regards,
Grayham Bailey
Listserv Archives (BOB member since 2002-06-25)
Grayham Bailey asked:
Can anyone help with examples of scripts that automate emails with the *.REP files as attachments?
Can anyone help with examples of scripts that interface with Microsoft Word?
Hi, Grayham – I might be able to help with your first inquiry.
The Business Objects Customer Support folks were extremely helpful to us on the question of using a script to refresh a document and convert/export the data to .xls/.htm/.rtf formats and attach any one (or all) of the formats to an e-mail message. I am attaching a copy of the script we use to accomplish these tasks via the Document Agent Server (DAS). We had to
have Microsoft Outlook on the DAS configured to send to our LOTUS NOTES. I’ve seen a couple of e-mails regarding how that is done. In the meantime, I hope this script will help!
Regards,
Kathy Mitchell
COMBINED SCRIPT**************************************** ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ‘This script can be scheduled through Document Agent Server and automates ‘the following tasks:
‘1. Open/Refresh the subject document
‘2. Convert data to Excel format
‘3. Export report in .rtf format
‘4. Export report in .htm format
‘5. Send mail via Outlook (Lotus Notes) with the (any) document attached ’ if the refresh is successful otherwise it sends a mail telling that ’ if it not possible to refresh the document (including the reason for which
’ the update failed).
‘The script is comprised of a number of subroutines that are ‘called into the Main routine at the bottom ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’ This sub-routine allows to send a Mail via OutLook ’ (OutLook must be installed on the machine where ’ this script is running)
’ It is used if the document did not refresh and will ’ send an e-mail to the requesting user. ’ Used in sub routine ‘Sub RefreshNotOk()’ ‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Sub SendMail(strMsg as String)
Dim OlkApp As Object 'Outlook.Application Dim NewMail As Object 'MailItem
Set OlkApp = CreateObject(“Outlook.Application”) Set NewMail = OlkApp.CreateItem(olMailItem)
With NewMail
.To = “kathleen.mitchell@healthnet.com”
’ .To = “enter address”
.Body = “”
.Subject = strMsg
.Importance = 2 'olImportanceHigh not understood by SBL. .Send
End With
End Sub
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’ This sub-routine defines a timer during which the ’ execution will pause.
’
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Sub Pause
PauseTime = 15 ’ Set duration.
Start = Timer ’ Set start time.
Do While Timer < Start + PauseTime
DoEvents ’ Yield to other processes.
Loop
Finish = Timer ’ Set end time.
TotalTime = Finish - Start ’ Calculate total time. ’ Do not print a dialog box, just here to help people to understand… ’ MsgBox “Paused for " & TotalTime & " seconds” End sub
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’ This sub-routine is called whenever the refresh is ok ’ It converts the data to Excel Format and directs the .xls output ’ to a shared file
’ It also converts the data to .rtf format and ’ sends the .rtf document by mail.
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Sub RefreshOk()
Dim OlkApp As Object 'Outlook.Application Dim NewMail As Object 'MailItem
Dim Attachments As Object
Dim NewDocumentName As String
Dim OldDocumentName As String
Dim strMsg As String
Dim UserName As String
Dim doc as BODocument
dim dp as BODataProvider
Dim Rep as BoReport
dim ReportName as string
dim reps as BOReports
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ‘CONVERTS DATA TO EXCEL FORMAT
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
set dp = ActiveDocument.DataProviders.Item(1) call dp.ConvertTo(ExpExcel,O,"\inetdev\dss\excel links\act_spt1")
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ‘CONVERTS DATA TO .RTF FORMAT
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
set doc = Application.Documents.Item(1)
set reps = ActiveDocument.Reports
dim I as Long
for I = 1 to reps.Count
Set Rep = Doc.Reports.Item(I)
ReportName = “act_spt1”
ReportName = ReportName & “.rtf”
'Rep.ExportAsRtf “\wh20141w025\C$\TEMP” , ReportName
Rep.ExportAsRtf “c:\temp” , ReportName
Next
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ‘CONVERTS DATA TO .HTM FORMAT
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
set doc = Application.Documents.Item(1)
set reps = ActiveDocument.Reports
’ dim I as Long
for I = 1 to reps.Count
Set Rep = Doc.Reports.Item(I)
ReportName = “act_spt1”
ReportName = ReportName & “.htm”
’ Rep.ExportAsHtml “\wh20141w025\C$\TEMP” , ReportName,
1,1,1,1,1,1,0,1
Rep.ExportAsHtml “\inetdev\dss\report catalog” , ReportName, 1,1,1,1,1,1,0,1
next
’ Get the document name
set doc = Application.Documents.Item(1)
DocumentName = act_spt1
Dim PathName1 As String
Dim PathName2 As String
Dim PathName3 As String
Dim PathName4 As String
Dim FullPathName1 As String
Dim FullPathName2 As String
Dim FullPathName3 As String
Dim FullPathName4 As String
PathName1 = “c:\temp”
PathName2 = “\inetdev\dss\Report Catalog\act_spt1\act_spt1” PathName3 = “\inetdev\dss\excel links” PathName4 = “\inetdev\dss\business objects source programs” DocumentName = doc.name
’ Create an attachement and associate the refreshed document to it.
FullPathName1 = PathName1 & “” & DocumentName & “.rtf” FullPathName2 = PathName2 & “” & DocumentName & “.htm” FullPathName3 = PathName3 & “” & DocumentName & “.xls” FullPathName4 = PathName4 & “” & DocumentName & “.rep”
’ Save the modifications made by the refresh.
doc.Save
’ close it otherwise Microsoft Outlook will not be able to open it (locked)
doc.Close
’ Get the user name to log it in the mail. UserName = Application.Variables(“BOUSER”)
’ Build the message itself.
’ strMsg = DocumentName & “.rep” & " has been refreshed successfully by " & UserName
strMsg = “This is a test, please disregard”
Set OlkApp = CreateObject(“Outlook.Application”) Set NewMail = OlkApp.CreateItem(olMailItem) Set Attachments = NewMail.Attachments
Attachments.Add FullPathName1, 1, 2, FullPathName1 Attachments.Add FullPathName2, 1, 2, FullPathName2 Attachments.Add FullPathName3, 1, 2, FullPathName3 Attachments.Add FullPathName4, 1, 2, FullPathName4
With NewMail
.To = “kathleen.mitchell@healthnet.com”
’ .CC = “user address”
.Body = " The attached sample document(s) are being sent for your information. You may ‘launch’ any of the documents to view. These are examples of documentation that can be generated in Business Objects and sent to anyone."
.Subject = strMsg
.Importance = 2 'olImportanceHigh is not understood by SBL.
.Send
End With
End sub
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’ This routine is called whenvever the refresh failed. ’ It sends a message, including the reason for which ’ it failed !
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Sub RefreshNotOk()
dim DocumentName as String
dim strMsg As String
dim UserName As String
dim doc as BODocument
set doc = Application.Documents.Item(1)
DocumentName = doc.Name
UserName = Application.Variables(“BOUSER”)
strMsg = DocumentName & " has not been refreshed successfully by " & UserName & " [" & Error (Err) & “]”
SendMail(strMsg)
End sub
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’ ’ This routine is the main part. It declares a ’ handler to be executed if the refresh is failing ’ for some reason.
‘’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’’
Sub Main
Dim PathName2 As String
Dim FullPathName2 As String
Application.Documents.Open("\inetdev\dss\business objects source programs\act_spt1")
dim doc as BODocument
set doc = Application.Documents.Item(1)
Pause
On Error Goto Err_Refresh
doc.Refresh
RefreshOk
Exit_main:
Exit sub
Err_Refresh:
RefreshNotOk
Error 2013
resume Exit_main
End Sub
Listserv Archives (BOB member since 2002-06-25)
Firstly i’d like to say hi,
I’ve been visiting this site alot recently and it has been very useful answering my queries about BO and reports…
Anyhow this is something i can’t seem to find…
So instead of creating a new thread i’ll re-invent this 
I’m very new to the VB side of things so if anyone has any info that would be fantastic. Even point me in the right direction 
(PS, as i said i’m a newbie to this side of things so go easy on me 
)
Dublin_Kopite (BOB member since 2008-03-04)