[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

How to send mail thro LotusNotes by using Access VBA ?.

Hi,

I need to have code for sending mail thro LotusNotes by using Access VBA ?.

I am using NT Client, Lotus 4.6 and Access 97.

Thanks in Advance
0
thangaraj
Asked:
thangaraj
1 Solution
 
peroveCommented:
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Here's a function I wrote to send a Notes e-mail.  Note that you need to set the path to the mailbox and replace "12345678" with the correct password for the users mailbox.

Let me know how it works out.
Jim.

Public Function SendNotesMail(Subject As String, Attachment As String, BodyText As String, SendTo As String, Optional CC As String = "", Optional BCC As String = "", Optional SaveIt As Boolean = False) As Integer
   
    Const Routine = "SendNotesMail"
    Const Version = "1.0.0"

    ' Set up the objects required for Automation into lotus notes
    Dim oSession As New Domino.NotesSession
    Dim oMailDB As Domino.NotesDatabase
    Dim oMailDoc As Domino.NotesDocument
    Dim oAttachME As Domino.NotesRichTextItem
    Dim oEmbedObj As Domino.NotesEmbeddedObject
   
    Const EMBED_ATTACHMENT = 1454
   
    ' Misc variables.
    Dim intAttach As Integer
    Dim intAttachments As Integer
    Dim strAttachmentName As String
    Dim UserName As String 'The current users notes name
   
    Dim strError As String
    Dim intFatalError As Integer

    SendNotesMail = False
   
    On Error GoTo Error_SendNotesMail
   
    'Start a session to notes
    oSession.Initialize ("12345678")
   
    'Open the mail database in notes
    Set oMailDB = oSession.GetDbDirectory("<mail path here>").OpenMailDatabase
   
    If oMailDB.IsOpen = True Then
      'Already open for mail
    Else
      oMailDB.Open
    End If
   
    'Set up the new mail document
    Set oMailDoc = oMailDB.CREATEDOCUMENT
    Call oMailDoc.AppendItemValue("Form", "Memo")
    Call oMailDoc.AppendItemValue("SendTo", SendTo)
    Call oMailDoc.AppendItemValue("CoptTo", CC)
    Call oMailDoc.AppendItemValue("BlindCopyTo", BCC)
    Call oMailDoc.AppendItemValue("Subject", Subject)
    Call oMailDoc.AppendItemValue("Body", BodyText)
    oMailDoc.SaveMessageOnSend = SaveIt
   
    'Set up the embedded object and attachment and attach it
    intAttachments = dhCountTokens(Attachment, ",")
    For intAttach = 1 To intAttachments
      strAttachmentName = dhExtractString(Attachment, intAttach, ",")
      Set oAttachME = oMailDoc.CreateRichTextItem("Attachment" & CStr(intAttach))
      Set oEmbedObj = oAttachME.EmbedObject(EMBED_ATTACHMENT, "", strAttachmentName, "Attachment" & CStr(intAttach))
      oMailDoc.CreateRichTextItem ("Attachment" & CStr(intAttach))
    Next intAttach
   
    'Send the document
    Call oMailDoc.AppendItemValue("PostedDate", Now())
    oMailDoc.send False
   
    SendNotesMail = True
   
Exit_SendNotesMail:
    On Error Resume Next
   
    'Clean Up
    Set oEmbedObj = Nothing
    Set oAttachME = Nothing
    Set oMailDoc = Nothing
    Set oMailDB = Nothing
    Set oSession = Nothing
   
Error_SendNotesMail:
    UnexpectedError ModuleName, Routine, Version, Err, Error$
    Resume Exit_SendNotesMail

End Function

0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
One last thing, you need to set a reference to "Lotus Domino Objects" in tools/references for the code to compile.

Also, the Lotus site has other sample code and a DCO library that you may want to explore as there are other ways to tackle this.

The function posted depends on the Notes client being installed.  It is possible to talk directly to a Notes Server as well.

Jim.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
archeryCommented:
I use the following function for emailing via Lotus Notes from Access.
You do NOT require any additional references.

The code is taken from a current Access 97 version, BUT also has the required code for use with Access 2000+ (with the "Split" function commented out, as it does not exist for A97). I actually use the same function in either A97 or A2000 applications, so it can be universally used.

In either version it allows for multiple recipients AND multiple attachments.

HTH

Public Function SendNotesMail(Subject As String, _
                              BodyText As String, _
                              SendTo As String, _
                              Optional CC As String = "", _
                              Optional BCC As String = "", _
                              Optional Attachment As String = "", _
                              Optional AttachmentDelimiter As String = "|", _
                              Optional SaveIt As Boolean = False) _
                              As Variant
                             
   ' Example: SendNotesMail Subject:="This is the Subject Line", _
                            BodyText:="This is the BODY of the text, ", _
                            SendTo:="somebody@hotmail.com,somebodyelse@hotmail.com", _
                            CC:="thirdperson@hotmail.com,fourthperson@hotmail.com", _
                            Attachment:="C:\Boot.ini,C:\PUBLIC_HOLIDAY_2003.doc,C:\DCO\Readme.txt", _
                            AttachmentDelimiter:=","
                             
   On Error GoTo Notes_Error
   
   Dim lngFindJetVersion As Long
                             
   '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 MailDbName As String         ' The current users notes mail database 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)
   
   'Start a session to notes
   Set Session = CreateObject("Notes.NotesSession")
   'Get the sessions username and then calculate the mail file name
   'You may or may not need this as for MailDBname with some systems you
   'can pass an empty string
   
   MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   'Open the mail database in notes
   Set Maildb = Session.GETDATABASE("", MailDbName)
   If Maildb.IsOpen = True Then
       ' Already open for mail
   Else
       Maildb.OPENMAIL
   End If
   
   'Set up the new mail document
   Set MailDoc = Maildb.CREATEDOCUMENT
   With MailDoc
       .Form = "Memo"
       .Subject = Subject
       .Body = BodyText
       .SAVEMESSAGEONSEND = SaveIt
       
        ' Get current Access version using SysCmd (fix international
        ' issues by using val instead of (implicit) Clng)
       
        lngFindJetVersion = Val(SysCmd(acSysCmdAccessVer))
       
        Select Case lngFindJetVersion
       
            Case Is = 8
                               
                .SendTo = SendTo
                .CopyTo = CC
                .BlindCopyTo = BCC
               
                If Attachment <> "" Then
                    Dim TotalAttachmentLength As Long
                    Dim AttachmentLength As Long
                    Dim AttachmentStartpoint As Long
                    Dim DelimiterFound As Long
                    Dim AttachmentNumber As Integer
                   
                    TotalAttachmentLength = Len(Attachment)
                    AttachmentStartpoint = 1
                    AttachmentNumber = 0
                    Do '    Until AttachmentStartpoint > TotalAttachmentLength
                        AttachmentNumber = AttachmentNumber + 1
                        DelimiterFound = InStr(AttachmentStartpoint, Attachment, AttachmentDelimiter)
                        If DelimiterFound = 0 Then
                            AttachmentLength = (TotalAttachmentLength - AttachmentStartpoint) + 1
                            Exit Do
                        Else
                            AttachmentLength = DelimiterFound - AttachmentStartpoint
                            Set AttachME = .CREATERICHTEXTITEM("Attachment" & CStr(AttachmentNumber))
                            Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Mid(Attachment, AttachmentStartpoint, AttachmentLength), "Attachment" & CStr(AttachmentNumber))
                            .CREATERICHTEXTITEM ("Attachment" & CStr(AttachmentNumber))
                            AttachmentStartpoint = DelimiterFound + 1
                        End If
                    Loop
                   
                    If AttachmentNumber = 1 Then    ' There was only a SINGLE attachment
                        Set AttachME = .CREATERICHTEXTITEM("Attachment")
                        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "Attachment")
                        .CREATERICHTEXTITEM ("Attachment")
                    Else
                        Set AttachME = .CREATERICHTEXTITEM("Attachment" & CStr(AttachmentNumber))
                        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Mid(Attachment, AttachmentStartpoint, AttachmentLength), "Attachment" & CStr(AttachmentNumber))
                        .CREATERICHTEXTITEM ("Attachment" & CStr(AttachmentNumber))
                    End If
                End If
   
            Case Is = 9, 10
                'Access 2000, Access XP
                '
                ' Allows for multiple :
                '                       SendTo(s)
                '                       CopyTo(s)
                '                       BlindCopyTo(s)
                '                       Attachment(s)
                '
                ' .SendTo = Split(SendTo, ",")
                ' .CopyTo = Split(CC, ",")
                ' .BlindCopyTo = Split(BCC, ",")
                'Set up the embedded object and attachment and attach it
                    Dim aryAttachment() As String
                    Dim intAttach As Integer
                    ' aryAttachment = Split(Attachment, AttachmentDelimiter)
                    For intAttach = LBound(aryAttachment) To UBound(aryAttachment)
                        Set AttachME = .CREATERICHTEXTITEM("Attachment" & CStr(intAttach))
                        Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", aryAttachment(intAttach), "Attachment" & CStr(intAttach))
                        .CREATERICHTEXTITEM ("Attachment" & CStr(intAttach))
                    Next intAttach
                       
            Case Else
                Err.Raise 8010, , _
                "Access ???: can only test strFindJetFilename files up to Access XP"
                Application.Quit
        End Select

Send_EMail:

   'Send the document
       .PostedDate = Now() 'Gets the mail to appear in the sent items folder
       .Send False
   End With
   
Clean_Up:
   'Clean Up
   Set Maildb = Nothing
   Set MailDoc = Nothing
   Set AttachME = Nothing
   Set Session = Nothing
   Set EmbedObj = Nothing
   
   Exit Function
   
Notes_Error:

    MsgBox "Error constructing Lotus Notes message"
    Resume Clean_Up
   
End Function
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - Answered by: JDettman (100pts) archery (100pts)  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
ComTechCommented:
I have reduced the 200 points for a 50/50 split.

JDettman
archery

I will accpet JDettman here at the question and create a new question in this TA for archery.

Regards,
ComTech
CS Admin @ EE
0
 
ComTechCommented:
Question for archery at : http://www.experts-exchange.com/Databases/MS_Access/Q_20369207.html

Regards,
ComTech
CS Admin @ EE
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now