CognosScript Editor: Sending Personalized Email with a PDF Attachement

We needed to send a roster and/or invoice to our customers using email. Using the CognosScript Editor I created a process that

1. reads in customer information from a file
2. runs a roster/invoice for that customer
3. saves the output from Impromptu as a PDF file
4. composes a customized email
5. sends the email with the PDF attachment

First you will need a comma delimited text file with all the fields you will need (put in Excel, save as text file):

00031616,"HomeBanc Foundation, Inc.",Ms. Albertelli,kaigr@cof.org,$400.00
00001385,Calhoun County Community Foundation,Ms. anks,kaigr@cof.org,"$1,410.00"
00003788,"Community Foundation of Broward, Inc.",Ms. Bartram,kaigr@cof.org,"$4,630.00"
00002651,Alabama Civil Justice Foundation,Ms. McInnish,kaigr@cof.org,$400.00

Next you will need an Impromptu report that generates your roster/invoice for each customer. The customer ID will be a prompt that is passed by the script.

Then we can use the CognosScript Editor (Start, Programs, Cognos BI, Tools, CognosScript Editor) to create the script.

Sub main()

Dim impapp As object
Dim imprep As Object
Dim objPDFPub As Object
Dim objImpSP As Object
Dim member as String
Dim member_name as String
Dim contact_name as String
Dim contact_sal as String
Dim email_address as String
Dim message_text as String
Dim order_total as String
Dim objOutlook as Object
Dim objOutlookMsg as Object

Set impapp = CreateObject("Impromptu.Application")
Set objOutlook = CreateObject("Outlook.Application")
impapp.Visible 1
impapp.OpenCatalog "X:\TIMSS_AI\Catalogs\Custom_Membership_Reports.cat","User",,"username","password"

Open "C:\1st_Reminder\1st_Reminder.txt" For Input As #1

message_text= "If you intend to renew, but prefer to pay at a later date, please inform us “
message_text= message_text + “of this by April 1, as this information is vital to our budgeting “
message_text= message_text + “process. Please accept our apologies if your dues have already been
message_text= message_text + “mailed. " +chr(13)+chr(13)

Do While Not Eof(1)
Input #1, member, member_name, contact_sal, email_address, order_total
Set imprep = impapp.OpenReport("C:\1st_Reminder\membership_invoice_customer.imr", member)
Set objPDFPub = imprep.PublishPDF
objPDFPub.Publish "C:\1st_Reminder\"+member+"_Invoice.pdf"
imprep.CloseReport
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
With objOutlookMsg
.To=email_address
.Subject="Council on Foundations - Membership Renewal"
.Body="Dear "+contact_sal+", "+chr(13)+chr(13)+ "We are writing to remind you that your 2004 membership dues of "+order_total+" will be due on April 1, 2004. " + message_text
.Attachments.Add("C:\1st_Reminder\"+member+"_Invoice.pdf")
.Send
End With
Set objOutlookMsg=Nothing

Loop
Close #1

impapp.Quit

End Sub

Things to watch out for:

1. Email is From the user that is logged in on the workstation’s default account
2. Because of Security settings in Outlook 2003, you will need to confirm the sending of each email
3. Style of email (i.e. font) is that user’s default style
4. Attachments can take up a lot of space. If you go over your mailbox limit, process will not send messages (I delete from Sent folder as they go out).
5. Make sure Impromptu report has output for all customers. If not, they will get sent a blank attachment.

Other Tips:

1. Can save as Excel
   ImpRep.ExportExcel “E:\Testcases\EdwinCruize\MyAttempts.xls”
2. Print a report
   ImpRep.Print [START, END, COPIES]
3. See Document #113754 in Cognos Knowledge Base on sending an email attachment and other documents on Macros and the Script editor.
4. .MAC file is the script and will launch CongonsScript Editor, .MCX is the compiled script and will run when you double click on it.

Applies to: TIMSS5 & TIMSS6