Lotus Notes Automation

[manic]

Hello all:



I need help with a code to automate the sending of a report with lotus notes. I have found this code:

Option Compare Database

Option Explicit

Public mobjDB As Object

Public Sub SEND_EMAILS()

'open the session with the lotus notes server
'this sub will output a report as a file
'attach the file and add the predetermined subject and body
'delete the file that was output
'close the session with the server

If OPEN_SESSION Then

'output report to text file on C:\
'DoCmd.OutputTo acOutputReport, "My Report", acFormatRTF, "O:\Report", False

'put your do loop here

'reference the email report sub to mail the file
If EMAIL_REPORT("MyWonderfulEmailAddress@gmail.com", "My Email Body", "My Subject Line", "O:\Report.pdf") = True Then
MsgBox "Message Sent"
Else
'error in email module
End If

'end your loop here

'delete the file
Kill ("o:\Report.pdf")

'call the close session sub to destroy the objects
CLOSE_SESSION

Else
'session not opened properly
End If
End Sub

Public Function OPEN_SESSION() As Boolean

Dim objSession As Object
Dim strServer As String
Dim strMailFile As String

'lotus notes must be open for module to work correctly
If MsgBox("Do you have lotus notes running?", vbCritical + vbYesNo, "Warning!") = vbYes Then
'this code must be left out of the loop so that only one session is started
Set objSession = CreateObject("Notes.NOTESSESSION")

strServer = objSession.GETENVIRONMENTSTRING("mailserver", True)
strMailFile = objSession.GETENVIRONMENTSTRING("mailfile", True)

Set mobjDB = objSession.GETDATABASE(strServer, strMailFile)

OPEN_SESSION = True
Else
MsgBox "Please start Lotus Notes and try again.", vbOKOnly, "Emails"
OPEN_SESSION = False
End If

End Function
Public Function EMAIL_REPORT(strSendTo As String, strBody As String, strSubject As String, Optional strFile As String) As Boolean
On Error GoTo EmailReport_Err

Dim objDoc As Object
Dim objRichTextAttach As Object
Dim objRichTextItem As Object
Dim objAttachment As Object

Const NOTES_RECIPIENTS = ""
Const NOTES_REPORTS_ADMIN_USER = ""
Const NOTES_MAIL_FILE = "O:\Report.pdf"

Set objDoc = mobjDB.CREATEDOCUMENT
Set objRichTextAttach = objDoc.CREATERICHTEXTITEM("File")
Set objRichTextItem = objDoc.CREATERICHTEXTITEM(objDoc, "Body")

If strFile <> "" Then
Set objAttachment = objRichTextAttach.EMBEDOBJECT(1454, "", strFile)
End If

'set up the email to be sent
objRichTextItem.AppendText strBody
objDoc.REPLACEITEMVALUE "SendTo", strSendTo
objDoc.REPLACEITEMVALUE "Subject", strSubject

objDoc.SaveMessageOnSend = True 'send E-mail
objDoc.SEND False 'false for do not attach a form

EMAIL_REPORT = True

Exit_Here:
Set objAttachment = Nothing
Set objDoc = Nothing
Set objRichTextAttach = Nothing
Set objRichTextItem = Nothing
Exit Function

EmailReport_Err:
EMAIL_REPORT = False
Resume Exit_Here

End Function

Public Sub CLOSE_SESSION()

Set mobjDB = Nothing

End Sub

The problem I have with this code is that it sends an email behind the scenes and i can't see it on my sent files.
How can I adjust this code to just open lotus notes with the attachment in it already so I can manually choose to who I send the report to instead of having it hardcoded.
any help with this will be appreciated.

[June7]

Instead of objDoc.SaveMessageOnSend or objDoc.SEND or both try objDoc.Display. Not sure because I use Outlook for my VBA email. My procedure is a Sub not a function.

[manic]

Instead of objDoc.SaveMessageOnSend or objDoc.SEND or both try objDoc.Display. Not sure because I use Outlook for my VBA email. My procedure is a Sub not a function.

I tried it, no Luck.

[June7]

Guess can't help. It works for me with Outlook.

[manic]

maybe you can help me with this, I got this working so far:

Private Sub CmdLotus2_Click()
Dim stReport As String
Dim stSubject As String
Dim stTo As String
Dim stCc As String
Dim stMessage As String

stTo = "QTN"
stCc = "MY"
stMessage = "Please review the Attachement"
stSubject = Me.lstReports.Column(4)
stReport = Me.lstReports.Column(2)

DoCmd.SendObject acSendReport, stReport, acFormatPDF, stTo, stCc, , stSubject, stMessage, True, ""
End Sub

this opens Lotus notes since is my primary email client and attaches the report but the problem is that the report name is not even close to what it supposed to be, it attaches the correct report selected from a list but the name and the file format is not right. I hve to choose a program from my program list to open the attachment (adobe) because the file attached is unknown.here is a screen of it.

[June7]

I don't think you can specify name to give the file sent with email by using the SendObject method. It defaults to the object Name or Caption property. Think I have seen developers get around this by using VBA to open the object in design view, change the object name/caption, then switch the object to open, and send email and close the object without saving with the change. Now if I can find thread I saw that in... Ah hah review this https://www.accessforums.net/showthr...ght=sendobject

[manic]

I don't think you can specify name to give the file sent with email by using the SendObject method. It defaults to the object Name or Caption property. Think I have seen developers get around this by using VBA to open the object in design view, change the object name/caption, then switch the object to open, and send email and close the object without saving with the change. Now if I can find thread I saw that in... Ah hah review this https://www.accessforums.net/showthr...ght=sendobject

that is a very good post, thanks for that one.
Evrything works up to

Reports!Sparepart_Return_Order.Caption = "Output filename:" & [Ordre_Hoved_Id] 'Renames the Report
maybe because Im using a listbox instead of a normal report.

[June7]

What do you mean by 'listbox instead of a normal report'?

[manic]

What do you mean by 'listbox instead of a normal report'?

Im using this to get the report name on my form

Me.lstReports.Column(2)

and when I add these lines:

Private Sub CmdLotus2_Click()
Dim stReport As String
Dim stSubject As String
Dim stTo As String
Dim stCc As String
Dim stMessage As String

stTo = "QTN"
stCc = "MY"
stMessage = "Please review the Attachement"
stSubject = Me.lstReports.Column(4)
stReport = Me.lstReports.Column(2)
 DoCmd.OpenReport stReport, acViewPreview, acWindowNormal, "" 'Opens the report
 Reports!Me.lstReports.Column(2) = "Output filename:" & Me.lstReports.Column(4)    'Renames the Report
 DoCmd.SendObject acSendReport, stReport, acFormatPDF = "PDF Format(*.pdf)", stTo, stCc, , stSubject, stMessage, True, ""
End Sub

I get the following error:
Run-Time Error '2451
the report name'Me' you eneter is mispelled...

I feel more lost than the show Lost itself...

[June7]

I bet the issue is with:
Reports!Me.lstReports.Column(2) = "Output filename:" & Me.lstReports.Column(4) 'Renames the Report

Reference to the listbox is same as using a variable. Variables cannot be used with Reports!. Since you set declared variables use them. Same issue but cleaner code. Also, you left off the Caption property.
Try:
Reports(stReport).Caption = "Output filename:" & stSubject 'Renames the Report

[manic]

I bet the issue is with:
Reports!Me.lstReports.Column(2) = "Output filename:" & Me.lstReports.Column(4) 'Renames the Report

Reference to the listbox is same as using a variable. Variables cannot be used with Reports!. Since you set declared variables use them. Same issue but cleaner code. Also, you left off the Caption property.
Try:
Reports(stReport).Caption = "Output filename:" & stSubject 'Renames the Report

Done!! Thanks a bunch June7.