Solved

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

Posted on 2002-07-23
7
557 Views
Last Modified: 2010-07-27
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
Comment
Question by:thangaraj
7 Comments
 
LVL 9

Expert Comment

by:perove
ID: 7172193
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 100 total points
ID: 7172222
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
 
LVL 57
ID: 7172231
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:archery
ID: 7173343
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
 
LVL 54

Expert Comment

by:nico5038
ID: 7281566

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
 

Expert Comment

by:ComTech
ID: 7315428
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
 

Expert Comment

by:ComTech
ID: 7315432
Question for archery at : http://www.experts-exchange.com/Databases/MS_Access/Q_20369207.html

Regards,
ComTech
CS Admin @ EE
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now