Schedule and Email the report. Mail Server being Lotus Notes

system · August 17, 2004, 7:47pm

I created a Business Objects Document using FULL CLIENT. I want to schedule this report every week and then email the refreshed report to the users.
Note:We are using Lotus Notes 4.6.6 Exchange Server and their is no microsoft outlook installed in any of the machines.
How to email the report? Do we need any scripts? It would be great if anyone can send me those scripts and steps to execute?

nmamidi (BOB member since 2004-08-11)

system · August 17, 2004, 8:47pm

First, a warm welcome to BOB! Second, do you remember that bright yellow box at the top of the post screen? Was there something in the other information here on BOB that didn’t work for you? You did search first, right?

A quick search for Lotus Notes turned up several previous discussions on on that topic, including this post containing an actual script. If you’ve tried that post and all the others related to Lotus Notes, and still have a specific question, let us know.

Again, welcome to BOB. We hope you will find it a valuable resource .

Dwayne Hoffpauir (BOB member since 2002-09-19)

system · August 18, 2004, 2:31pm

Thanks for the info and warm welcome. I am working on this. I will get back incase I have any problems.

With Best Wishes
Naveen

nmamidi (BOB member since 2004-08-11)

system · August 18, 2004, 4:14pm

I downloaded Michael’s code obtained from the link. That was very helpful. I have upgraded the Lotus Client from 4.6 to 6.5.
But, I am getting this error when I am trying to run
Run-Time error 13
Type mismatch
at this statement.
Set domNotesDBMailFile = domSession.GetDatabase("", “names.nsf”)
Please let me know what should I do in the code

Thanks in Advance.

With Best Wishes
Naveen

nmamidi (BOB member since 2004-08-11)

system · August 18, 2004, 4:22pm

Moving to the SDK Forum. Thanks. 8)

Dave Rathbun (BOB member since 2002-06-06)

system · August 19, 2004, 6:08am

Hi,

Try putting in the mail database server name in the first parameter, eg :

Set domNotesDBMailFile = domSession.GetDatabase("SERVERNAME", "names.nsf")

You can see the server name by opening your mail file in Lotus Notes and then displaying the database properties. You should see the server name in the window that appears. We are using version 5 of Notes.

Good luck.

Michael Abelha (BOB member since 2002-08-16)

system · August 19, 2004, 2:02pm

Thanks for the info. Solved it.
I am using the code www.forumtopics.com/busobj/viewtopic.php?t=25994&start=0 written by Mayhew M. I am unable to send the same report to multiple users. When I execute it sends the Report only to me.

I require a more complete version of this macro which sends as a pdf instead of .xls, error trapping, creates a log file to monitor sucessful sends, cleans up the attached file, includes Parameters for CC, importance, etc…, I need to schedule a report using BCA which sends this pdf to different users. It would be great if anyone can help me on this.

Thanks in Advance
Regards
nmamidi

nmamidi (BOB member since 2004-08-11)

system · August 20, 2004, 3:08am

Hi,

In the line of code that goes :

Call domNotesDocumentMemo.AppendItemValue("SendTo", domSession.CommonUserName)

replace the domSession.CommonUserName with an email address that you wish to send to.

For PDF you need to use something along the lines of :

doc1.ExportAsPDF (pdfFileName)

For log files you need to use something along the lines of :

Public tsLog As TextStream
Set tsLog = fso.CreateTextFile(Log_File, True)

To send the PDF to multiple users you may need get the CC option working or have a bit of code that loops around changing the send recipient and then sending the email.

Please share the successes that you have back with the forum.

Michael Abelha (BOB member since 2002-08-16)

system · August 20, 2004, 4:09pm

Thanks for the Help.
I am able to send the xls/rep to multiple email users.

Please see the code below provided by Mayhew M, I made minimal changes to suit my requirement.

I would like to know If I need to make any changes in the code when scheduling the Report via BCA.


Sub SendMailAttachment()

'******************************************************************************
' Code Created 01/20/2004, MEMSr.
' This Module was created so the end-user can automatically save as an Excel
' file after report is refreshed.  The Excel document is then attached to Lotus
' Notes and an email is sent to their inbox for review.  After review the Excel
' document can be forward to additional recipents.  Note:  You must have Lotus
' Notes loaded on the local machine in order to run the macro.  Also, you will
' need to have the Lotus Notes Library Reference added to the vb editor before
' the code will compile.
'******************************************************************************

    
    Dim strBOdocument As String
    Dim strBOUserDocsPath As String

    strBOUserDocsPath = busobj.ActiveDocument.Path &amp; "\"
   
    strBOdocument = Application.ActiveDocument.Name
    Application.ActiveDocument.SaveAs (strBOUserDocsPath &amp; strBOdocument &amp; ".xls")
    
    '  The code below is used to send Lotus Notes email attachment for the
    '  newly created Excel spreadsheet(s).  Note:  Lotus Notes does not have to
    '  be open at the time, but must be loaded on the local machine.
    
           
    Dim domSession As New Domino.NOTESSESSION
    'Dim domSession As NOTESSESSION
    Dim domNotesDBMailFile As New Domino.NOTESDATABASE
    Dim domNotesDocumentMemo As New Domino.NotesDocument
    Dim domNotesRichText As New Domino.NotesRichTextItem
    Dim strAttachment As String
    
    domSession.Initialize ("omsairam25$")    'Prompts user for Lotus Notes Password
    Set domNotesDBMailFile = domSession.GetDatabase("", "names.nsf")

    Set domNotesDocumentMemo = domNotesDBMailFile.CreateDocument

    Call domNotesDocumentMemo.AppendItemValue("Form", "Memo")
    
    Call domNotesDocumentMemo.AppendItemValue("Subject", strBOdocument)
    
    Set domNotesRichText = domNotesDocumentMemo.CreateRichTextItem("Body")
    
    strAttachment = strBOUserDocsPath &amp; strBOdocument &amp; ".xls"
    
    Call domNotesRichText.EmbedObject(EMBED_ATTACHMENT, "", strAttachment, "")
    
    'domNotesRichText.AppendText (InputBox("Enter any additional text you want ", _
    "to appear in your email E-mail Body"))
    domNotesRichText.AppendText "Please find the Report as an attachment"
    domNotesDocumentMemo.SAVEMESSAGEONSEND = True
    ' I am able to send to multiple recipients
    domNotesDocumentMemo.Send False, "nmamidi@yahoo.com"
    domNotesDocumentMemo.Send False, "nmamidi@hotmail.com"
End Sub


Private Sub Document_AfterRefresh()
    SendMailAttachment
End Sub

strBOUserDocsPath = busobj.ActiveDocument.Path &amp; "\"

Thanks in Advance
Naveen

nmamidi (BOB member since 2004-08-11)