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


Sending query results to Lotus Notes from Access

Posted on 2011-04-28
Medium Priority
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
  • 3

Author Comment

ID: 35484625
I found the solution at http://www.fabalou.com/VBandVBA/lotusnotesmail.asp  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
ID: 35484636
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:
- http://www.fabalou.com/VBandVBA/lotusnotesmail.asp
LVL 46

Accepted Solution

Sjef Bosman earned 2000 total points
ID: 35484665
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
ID: 35484709

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

834 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