Welcome to the new CogKnowHow.com!


If you have trouble logging in, reset your password via the login window
by clicking the 'Forgot Password' link.

Impromptu macro save the reports in pdf format and send mail


#1
'******************************************************************************
'* Date: 05-01-2003                                                           *
'* Version: 1.0                                                               *
'* Author: Martijn Christenhusz                                               *
'* Description:                                                               *
'* macro to run an Impromptu report                                           *
'* save the reports in pdf format with the date appended to the report name   *
'* create an email in Outlook with the pdf attached                           *                                               *
'*                                                                            *
'******************************************************************************


' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

   Declare Sub SaveThePDF(cat$, imr$, userclass$, pdf$ )
   Declare Sub Outlook(strSubject$, strTo$, strBody$, strAttachment$)

   Dim objImpRep as Object
   Dim objImpApp as Object
   Dim objPDFPub as Object

   Dim objOutlook            as Object
   Dim objOutlookEmail       as Object
   Dim objOutlookAttachments as Object


' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Main()

  Dim strToday        As String
  Dim strReportName1  As String
  Dim strCatalogName1 As String
  Dim strUserClass    As String
  Dim strFileName1    As String
  Dim strTo1          As String

  strToday        = date$ 
  strCatalogName1 = "C:\program files\Cognos\cer4\samples\Impromptu\reports\gosales.cat"
  strReportName1  = "C:\program files\Cognos\cer4\samples\Impromptu\reports\customer_satisfaction.imr" 
  strUserClass    = "Creator"
  strFileName1    = "C:\program files\Cognos\cer4\samples\Impromptu\reports\customer_satisfaction" & strToday & ".pdf"
  strTo1          = "martijn.christenhusz@cognos.com"

  call SaveThePDF ( strCatalogName1, strReportName1, strUserClass, strFileName1 )

  'msgbox "Het pdf rapport is gegenereerd!"
 
  strSubject = "customer_satisfaction Report"
  strBody = "This is a generated email. If there are issues with this email please contact me." & Chr(13) & Chr(13) & "Martijn Christenhusz" & Chr(13) & "BI consultant" & Chr(13) & "0653476357" & Chr(13) & Chr(13)
  
  call Outlook(strSubject, strTo1, strBody, strFileName1)
  
  'msgbox "The email is generated in Outlook!" 

End Sub

' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub SaveThePDF(cat$, imr$, userclass$, pdf$)
  
  Set objImpApp = CreateObject("CognosImpromptu.Application")
  Set objImpRep = objImpApp.OpenReport(imr)
  
  objImpApp.OpenCatalog cat, userclass
  strReportName = objImpRep.FullName 
  objImpRep.RetrieveAll

  Set objPDFPub = objImpRep.PublishPDF
  
  objPDFPub.Publish pdf
  objImpRep.CloseReport
  objImpApp.CloseCatalog
  objImpApp.Quit
  
  Set objImpApp = Nothing
  Set objImpRep = Nothing
  Set objPDFPub = Nothing

End Sub

' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub Outlook(strSubject$, strTo$, strBody$, strAttachment$)

   
   Set objOutlook            = CreateObject("Outlook.Application")
   Set objOutlookEmail       = objOutlook.CreateItem(OlMailItem)
   Set objOutlookAttachments = objOutlookEmail.Attachments
   
   objOutlookEmail.Subject = strSubject
   objOutlookEmail.Body    = strBody
   objOutlookEmail.To      = strTo
   
   objOutlookAttachments.Add strAttachment
   
   objOutlookEmail.Save
   objOutlookEmail.Send
   
   ' the following two options might be useful, but have been commented out for this example
   
   'objOutlookEmail.Display
   
   Set objOutlook            = Nothing
   Set objOutlookEmail       = Nothing
   Set objOutlookAttachments = Nothing 
      
End Sub

'Note: This macro be written so that it sends it to various people by performing the following action.

'Change the strTo1 to include several email addresses:
'strTo1="fredsmith@a.com;fredsmith@b.com;fredsmith@c.com"