Lotus Emails (VBA/Access/2002)

Topic

New Reply

I’m an Access VBA developer trying Lots emails for the first time. After getting some sample code from the Access loungers, I now have a nice function that sends a Lotus email from VBA code in Access, with or without an attachment. However, when Lotus Notes starts, it prompts the user for a password. There are times when I want my db app to send emails without user intervention. Can anyone tell me how to avoid the password entry box when Lotus starts up from code? Is there a way to access the current user’s profile to pass in his/her email pw to the function? Or do I have to establish an email account for the database and use that?

If anyone has some very basic, commented code for LN I would appreciate it. I’m having trouble doing basic things such as saving the email in the user’s sent items folder and exiting the Lotus Notes session after completing the email send. Also, when I send the email, the first line of the message is “body.” Eliminating it eliminates the user’s message as well.

Any help would be appreciated. Code follows:

Public Function fnSendLotus(Optional Attachment, Optional strMessage As String) As Boolean
‘Sends an email in Lotus Notes with or without an attachment.
‘Attachment must be passed as a variant so it can be tested.
‘If strMessage is not passed in, a default message is used.

Dim S As Object
Dim db As Object
Dim doc As Object
Dim rtItem As Object
Dim Server As String, Database As String
Dim strError As String
Dim strRecipient As String, strSubject As String, blLog As Boolean, strAtchName As String, strSender As String
strSender = GetComputerName
‘Start up Lotus Notes and get object handle
Set S = CreateObject(“Notes.NotesSession”)
Server = S.GetEnvironmentString(“MailServer”, True)
Database = S.GetEnvironmentString(“MailFile”, True)
Set db = S.GetDatabase(Server, Database) ‘This is where the user is prompted for a password
On Error GoTo ErrorLogon

Set doc = db.CreateDocument
On Error GoTo 0
If strMessage = “” Or IsNull(strMessage) Then
strMessage = “Update from PVH Asset Management Database”
End If
doc.Form = “Memo” ‘Create a new “Memo” document
doc.Importance = “1” ‘(WHERE 1=URGENT, 2= NORMAL,3=FYI)
‘This doesn’t work for some reason.

strRecipient = InputBox(“Enter recipient name() and URL address(es).” & vbCrLf & “Separate multiple recipients with a comma.”, vbOKOnly)
doc.SendTo = strRecipient
‘SENDS A RETURN RECIEPT
‘doc.RETURNRECIEPT = “1” This doesn’t work for some reason.
strSubject = InputBox(“Type your subject here”, vbOKOnly)
doc.Subject = strSubject
Set rtItem = doc.CreateRichTextItem(“Body”) ‘This establishes a link to the email body for the attachment
Call rtItem.AppendText(“Body”)
Call rtItem.AppendText(Chr(10) & strMessage & Chr(10)) ‘This builds the email message as a new paragraph
‘Note: you can repeat this call with additional or default messages
If IsMissing(Attachment) = False Then ‘Note: Attachment must be a variant for IsMissing function to work
Call rtItem.AddNewLine(1)
strAtchName = Attachment.Name
‘Embeds the file as an attachment to the body field
Call rtItem.EmbedObject(1454, “”, Attachment)
End If
Call doc.Send(False) ‘Make sure this parameter stays false, or LN will open
‘Delete references to Notes objects to release memory
‘Call doc.Save(True, False) ‘Save the email in sent items – doesn’t work for some reason
‘S.Exit ‘This doesn’t work for some reason
Set doc = Nothing
Set db = Nothing
Set S = Nothing
Set rtItem = Nothing
MsgBox “Mail has been sent!”, vbInformation
fnSendLotus = True

Call WriteLog(strSender, strRecipient, strSubject, strMessage, strAtchName)
‘Nice function for adding a record to an email log table in my database app.

Exit Function

ErrorLogon:
If Err.Number = 7063 Then
MsgBox “Please login to Lotus Notes first!”, vbCritical
Set doc = Nothing
Set db = Nothing
Set S = Nothing
Set rtItem = Nothing
fnSendLotus = False
Exit Function
Else
strError = “An Error has occurred on your system:” & vbCrLf
strError = strError & “Err. Number: ” & Err.Number & vbCrLf
strError = strError & “Description: ” & Err.Description
MsgBox strError, vbCritical
‘S.Exit
Set doc = Nothing
Set db = Nothing
Set S = Nothing
Set rtItem = Nothing
fnSendLotus = False
Exit Function
End If
End Function

Reply | Quote

Replies

Try inserting the following line before the prompt for a password occurs :

Call S.InitializeUsingNotesUserName(“UserName”, “Password”)

replacing UserName and Password with the appropriate values.

I use the following in Excel to send the activeworkbook (could be any file)

Sub SendMail(strSendTo, strSubject, strBody)
Dim Session As Object
Dim Maildoc As Object
Dim rTItem As Object
Dim oDB As Object
Dim mObjAtt As Object
Set Session = CreateObject(“Notes.NOTESSESSION”)
Set oDB = Session.GETDATABASE(“”, “”)
Call oDB.OPENMAIL
Set Maildoc = oDB.CREATEDOCUMENT
Maildoc.Form = “Memo”
Maildoc.Subject = strSubject
Maildoc.SendTo = strSendTo
Maildoc.PostDate = Date
Maildoc.SAVEMESSAGEONSEND = True
Set rTItem = Maildoc.CREATERICHTEXTITEM(“Body”)
Call rTItem.APPENDTEXT(strBody & vbCrLf & vbCrLf)
Set mObjAtt = rTItem.EmbedObject(1454, “”, ActiveWorkbook.FullName)
Call Maildoc.SEND(False)
Set rTItem = Nothing
Set Maildoc = Nothing
Set oDB = Nothing
Set Session = Nothing
End Sub

The line in red records the mail in the users Sent items. This code will prompt for the password if the user is not logged into a notes session. Take a look at the following links.

Lotus Developer Domain

COM Together – with Domino

You can down an acrobat document from the latter with examples in VB and VBA

Andrew C

Reply | Quote

Kathryn,

I get the impression that this will be on more than one users machine. If that is the case, I would do as you suggest and have a seperate email account for your application and install the id file on each users machine. This would mean that you don’t have to ask all your users for their email password, and eliminates maintaining that list of passwords as time goes by. This does not take account of any security implications that may arise, either!

On the subject of saving the email in the sent items folder, I’m using excel 2000 and Notes 4.5.7 and the line you have commented out: “Call doc.Save(True,False)” is the line I use all the time without problems, but as Andrew has offered an alternative, hopefully that will work for you.

Reply | Quote

I recommend that future posts involving Access be posted to the Access forum. While you will probably get an answer you can use here, you might get it a lot faster in Access. Access depends heavily on VBA for any serious development so you’ll find Access VBA questions answered quickly in that board. And Access objects, forms and controls, are very different in many respects from their counterparts in other Office applications, so the code differs as well..

Reply | Quote

Thank you, everyone!

Adding the line doc.SaveMessageOnSend = True just above Call doc.Send(False) worked fine. But the line Call doc.Save(True, False) resulted in error message “object does not support this property or method.” Any idea why?

Thank you, too, for the link to the excellent LND article on using Domino objects. The author does not include Access VBA as supporting COM, but my object browser shows all the NotesSession objects. Anybody know if there are any pitfalls I should watch out for in Access VBA?

I appreciate your help.

Reply | Quote

Are you asking about specifc pitfalls relating to Lotus Notes or just in general? In general, there are a lot. Access uses different forms and controls from the rest of Office, so the VBA to manipulate those objects can be significantly different.

Reply | Quote

Specific to Lotus Notes. I have no problem with the Access VBA object model. But I’m still wondering how to end my LN session – I don’t see a “quit” or “Exit” method for LotusSession in the object browser. So, after my function runs, the user is left with an ugly ,plain grey LN screen that has to be closed manually.

Thanks!

Reply | Quote