Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lotus Emails (VBA/Access/2002)

    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

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Lotus Emails (VBA/Access/2002)

    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
    <font color=red>Maildoc.SAVEMESSAGEONSEND = True</font color=red>
    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

  3. #3
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lotus Emails (VBA/Access/2002)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lotus Emails (VBA/Access/2002)

    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..
    Charlotte

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lotus Emails (VBA/Access/2002)

    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.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Lotus Emails (VBA/Access/2002)

    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.
    Charlotte

  7. #7
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lotus Emails (VBA/Access/2002)

    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •