VBA for Email with Lotus Notes

[Tom59593]

Hi all,

I am writing an application that presents a form (on a sheet called Form) to the user. This form has multiple fields for entry, (some fill-in and some choices via pull-down menus in combo boxes).

When the user clicks submit, the data is all moved, formatted, calculated as needed, and inserted into a seperate sheet, called Database. There is also a 'Clear Form' button that does just that.

Everything as far as functionality with the above features I can get to work just fine. My problem is this:

I have been informed that an email needs to be sent to an administrator whenever the user clicks submit. Normally, this would be simple, but there's more. The user already specifies his/her company division in the cell "C9". This is a pull-down menu that is populated with data from a table that is hidden off to the side of the form. This table includes each of the possible divisions (there are 13), as well as the administrator email address that a message needs to be sent to when the form is submitted.

What I need is some code to do is this:

1) Given the choice for the user's division, determine which email address needs to receive an email. [email address are in Range("R6:R18")]
2) Generate an email containing the following contents:
Subject:
Global Validation Event Approval

Message:
("C6") has submitted an event to the Global Validation Event Database on ("C13"). Please review the submission and approve or decline the event in the master Database. Then, inform ("C6") of the updated status of the event. Thank you.

3) Send the email (using lotus notes) to the address determine in step 1.

I figured that I could use some form of the LOOKUP function, but I cannot seem to get this to work. Also, I am unsure as to how to get excel to send the email through Lotus Notes. I have seen code for Outlook, but not for Lotus...so I am a bit of a beginner as far as that is concerned.

Please let me know if and how this would be possible.

THANK YOU IN ADVANCE!!!!

 

[Kenneth Hobson]

Make a Sub and pass the parameters to this function. You may want to add a few more or reset the options to send and save. This example includes an option to include an attachment but you can set it to "" if you have none to attach.

'Similar to: 'Brian Walters, ozgrid.com, #67089
Public Sub SendNotesMail(Subject As String, Attachment As String, _
    ByVal Recipient As String, _
    BodyText As String, _
    SaveIt As Boolean, _
    ByRef lErr As Double)
    'lErr is used when using the Sub in a batch process,
    'to handle instances where an error appears
    
    'Example of use:
    'SendNotesMail "The Subject", "C:\My Documents\TestFile.txt", _
        "[EMAIL="john@doe.com"]john@doe.com[/EMAIL], [EMAIL="jane@doe.com"]jane@doe.com[/EMAIL]", _
        "This is the body text, can be longer", True, lErr
    
  'Set up the objects required for Automation into lotus notes
  Dim Maildb As Object            'The mail database
  Dim UserName As String          'The current users notes name
  Dim MailDoc As Object           'The mail document itself
  Dim AttachME As Object          'The attachment richtextfile object
  Dim Session As Object           'The notes session
  Dim EmbedObj As Object          'The embedded object (Attachment)
  Dim ArRecipients() As String    'Array of recipients
  Dim i As Long                   'Counter
  
  'Create an array of recipients (Separated by commas)
  Recipient = Recipient & ","
  
  While InStr(1, Recipient, ",", 1) > 0
      i = i + 1
      ReDim Preserve ArRecipients(1 To i) As String
      ArRecipients(i) = _
      Left(Recipient, InStr(1, Recipient, ",", 1) - 1)
      Recipient = _
          Mid(Recipient, InStr(1, Recipient, ",", 1) + 1, Len(Recipient))
  Wend
  
  'Start a session to notes
  Set Session = CreateObject("Notes.NotesSession")
  On Error GoTo err_h
  
  'Open the mail database in notes
  Set Maildb = Session.GETDATABASE("", "mail\username.nsf")
  If Maildb.IsOpen = False Then
      Maildb.OPENMAIL
  End If
  
  'Set up the new mail document
  Set MailDoc = Maildb.CREATEDOCUMENT
  MailDoc.Form = "Memo"
  MailDoc.sendto = ArRecipients
  MailDoc.Subject = UCase(Subject)
  MailDoc.Body = UCase(BodyText)
  
  'This is supposed to be the property, but works
  'on some systems only
  'without an apparent reason of failure
  MailDoc.SAVEMESSAGEONSEND = SaveIt
  
  'Set up the embedded object and attachment and attach it
  If Attachment <> "" Then
      Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
      Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
      MailDoc.CREATERICHTEXTITEM ("Attachment")
  End If
  
  'Send the document
  MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
  MailDoc.Send 0
  MailDoc.Save True, True, False
  
  'Clean Up
err_h:
  lErr = Err.Number
  Set Maildb = Nothing
  Set MailDoc = Nothing
  Set AttachME = Nothing
  Set Session = Nothing
  Set EmbedObj = Nothing
End Sub