Sending query results to Lotus Notes from Access

Posted on 2011-04-28
Last Modified: 2012-05-11

I have a query in a Access 2007 database, whose results I wish to send to lotus notes as an attachment.  I've verified that the lotus notes references are set.  I need some help in how to best export the query results to lotus notes.  There is a method called docmd.send object that I think I could use.  I would appreciate any help
Question by:chtullu135

    Author Comment

    I found the solution at  I understand most of the code but am unsure of the following '.CreateRichTextItem ("Attachment" & CStr(intAttach))
    I found I had to comment it out to make it work.
    Public Sub 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)
        '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)
        Dim intAttach As Integer
        '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
        UserName = Session.UserName
        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
        End If
        'Set up the new mail document
        Set MailDoc = Maildb.CreateDocument
        With MailDoc
            .Form = "Memo"
            .SendTo = Split(SendTo, ",")
            .CopyTo = Split(CC, ",")
            .BlindCopyTo = Split(BCC, ",")
            .Subject = Subject
            .Body = BodyText
            .SaveMessageOnSend = SaveIt
        'Set up the embedded object and attachment and attach it
            Dim aryAttachment() As String
            aryAttachment = Split(Attachment, "|")
            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
        'Send the document
            .PostedDate = Now() 'Gets the mail to appear in the sent items folder
            .Send False
        End With
        'Clean Up
        Set Maildb = Nothing
        Set MailDoc = Nothing
        Set AttachME = Nothing
        Set Session = Nothing
        Set EmbedObj = Nothing
    End Sub

    Open in new window

    LVL 46

    Expert Comment

    by:Sjef Bosman
    There are hundreds of pages explaining how to send a mail through Notes, with an attachment. In most cases, the attachment has to be created first, as a file.

    Here's an example:
    LVL 46

    Accepted Solution

    That creates items in the mail called Attachment1, Attachment2, etc., for the Attachment-parameter is a string that can contain multiple file names, separated by a '|' character.
    LVL 46

    Expert Comment

    by:Sjef Bosman

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

    728 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

    16 Experts available now in Live!

    Get 1:1 Help Now