Problem sending a report from 2007 via Lotus Notes

[CazB]

I have a function which generates a report as a RTF file, then sends it out to a list of users via Lotus Notes. The list of users is generated within the function using a query.

The code worked fine while I was testing with just me in the recipients... but as soon as I add more people to the list, I get

Runtime error 7294: Unable to send mail, no match found in name and address book(s)

If I copy and paste the generated list into a new mail message in Lotus Notes, it accepts them all....

I THINK this is because when it's sending from Access, it's trying to use my personal address book, rather than the one that's held on the mail server... Does that sound plausible / feasible? If it does, can anyone let me know how to change this?

I tried changing the recipients list to have the 'actual' email addresses in it rather than their names - when I did that I got no errors... but it didn't send the email, either!

HELP?

I can attach the code I'm using, if it would help...

 

[CazB]

Anyone? Please?

 

[JHB]

I've some old description of how to send E-mail via Lotus Notes through MS-Access, maybe you can use some of it, (I haven't test it), look at the "bcc" field in the code.
Else post your code.

Public Sub TestMail

    ' create a new notes mail by setting a variable
    ' which will reference our class file.
    Dim oMemo As NotesMailMemo

    ' instantiate it (this could have been done above equally well
    ' by saying Dim oMemo As New NotesMailMemo)
    Set oMemo = New NotesMailMemo

    ' let's set some properties of our new mail
    With oMemo

        ' SendTo is the only property that is actually required
        ' to send a valid notes mail.  All of the others after
        ' this are entirely discretionary.  Group names and internet
        ' addresses will also work in the SendTo, cc and bcc fields
        ' if your domino server / notes client is setup correctly.
        .SendTo = "Joe Bloggs/ACME"

        ' optionally..
        .cc = "Darren Davison/Accurum"
        .bcc = "dd@home.com"

        .Subject = "Hello from VB/A!"

        ' the body field here is created from a String and not the
        ' rich-text format used by the notes client by default.  If you
        ' need additional functionality here, you will have to amend the
        ' class file
        .Body = "This is a test message"

        ' the signature file allows any text file to be appended to the
        ' body of the mail
        .SigFile = "c:\mysig.txt"

        ' couple of notes options
        .Signed = True
        .Encrypted = True
        .Priority = "H" ' or "L" or "N"

        ' file attachments - you can call this method as often as you like
        ' for multiple files.
        .Attach "c:\test1.txt"
        .Attach "d:\test2.txt"

        ' deliver it..
        .Send

    End With

End Sub

 

[CazB]

Thanks JHB... I just tried using that code but Access didn't like it! It didn't like 'NoteMailMemo' - which wasn't a good start... have tried playing around with a few other alternatives but I'm still snookered...

This is what I've been trying to use:
The procedure that calls the function

Private Sub Form_AfterInsert()
Dim ClientName As String
Dim AdditComments As String
Dim x As Integer
Dim strlist As String
Dim db As DAO.Database, rs As DAO.Recordset
 
Set db = CurrentDb
Set rs = db.OpenRecordset("qryRecipients")
rs.MoveFirst
Do While Not rs.EOF
 strlist = strlist & rs.Fields("recipients") & "; "
 rs.MoveNext
Loop
strlist = Left(strlist, Len(strlist) - 1)
    ClientName = Me.Customer_Name
    If Me.EmailComments Is Not Null Then AdditComments = Me.EmailComments Else AdditComments = ""
    x = SendNotesMail(strlist, "A new tender has just been added to the Tenders database - Please see attached file for details.", AdditComments, "Notification of a new Tender : " & ClientName)
 
Set db = Nothing
Set rs = Nothing
End Sub

The function it calls:

Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String)
'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record
DoCmd.OutputTo acOutputReport, "rep09emailnotification", acFormatPDF, "x:\tenders\group tendering database\TenderUpdate.pdf", False
Dim Subject As String
Dim Attachment As String
Dim Recipient As String
Dim BodyText As String
Dim ExtraText As String
Dim SaveIt As Boolean
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 EmailSend As Object
Dim EmailApp As Object
'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 = 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
MailDoc.Form = "Memo"
MailDoc.sendto = strSendTo
MailDoc.Subject = strSubject
MailDoc.Body = strBody & vbCrLf & vbCrLf & strExtraText
MailDoc.SaveMessageOnSend = SaveIt
'Set up the embedded object and attachment and attach it
'If Attachment <> "" Then
Set AttachME = MailDoc.CreateRichTextItem("x:\tenders\group tendering database\TenderUpdate.pdf")
Set EmbedObj = AttachME.EmbedObject(1454, "", "x:\tenders\group tendering database\TenderUpdate.pdf")
'End If
'Send the document
MailDoc.Send 0, Recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
Kill ("x:\tenders\group tendering database\TenderUpdate.pdf")
'Syntax to use for the function:
'SendNotesMail "[EMAIL="email@email.com"]email@email.com[/EMAIL]", "Body Message", "Additional Text", "Subject"
End Function

 

[CazB]

I should maybe add:

If I use

DoCmd.SendObject acSendReport, "rep09emailnotification", acFormatPDF, strlist, , , "Notification of a new Tender - " & ClientName, "A new tender has just been added to the Tenders database - please see attached file for details. " & AdditComments, 0

Then the email goes... but it doesn't matter which acFormat I use, I get a .tmp file ...

 

[JHB]

Thanks JHB... I just tried using that code but Access didn't like it! It didn't like 'NoteMailMemo' - which wasn't a good start... have tried playing around with a few other alternatives but I'm still snookered...

I think it is only a matter of missing references, did you select the Lotus Notes Object Library?

 

[CazB]

how do I do that?

(sorry, self-taught dabbler here, finding that a little knowledge can be a dangerous thing)

 

[JHB]

In the code window's menu line, Click "Tools" choose "References.." then find in the list something like "Lotus Notes Object Library", (mark it).
I haven't installed Lotus Notes on this computer, so I can't give you the correct name for the Lotus Notes Object Library.

 

[CazB]

hmmm... the only Lotus options I have are Lotus Domino Objects and Lotus Notes Automation Classes and they're both ticked... any other ideas?

 

[JHB]

I just fired up an old computer I had with Lotus Notes installed and I see, it weren't the best advice I gave you, sorry.
The below send an E-mail to two recipients with two attachment.
This time the code is tested and it functions. Remember to replace "C:\Lotus\Notes\Data\names.nsf" till yours. Also "C:\NotNameGiven1.jpg" and "C:\NotNameGiven2.jpg" must be replaced.
And here:
recipients(1) = "FirstEmail@Address.com"
recipients(2) = "SecondEmail@Address.com"

Public Sub TestMail1()
  Dim Maildb As Object
  Dim MailDoc As Object
  Dim Body As Object
  Dim Session As Object
  
  'Start a session to notes
  Set Session = CreateObject("Lotus.NotesSession")
  'This line prompts for password of current ID noted in Notes.INI
  Call Session.Initialize
  'Open the mail database in notes
  Set Maildb = Session.GetDatabase("", "C:\Lotus\Notes\Data\names.nsf")
  If Not Maildb.IsOpen = True Then
    MsgBox ("Not open")
    Exit Sub
  End If
  'Create the mail document
  Set MailDoc = Maildb.CreateDocument
  Call MailDoc.ReplaceItemValue("Form", "Memo")
  
  'Set the recipient
  Dim recipients(1 To 2) As String
  recipients(1) = "FirstEmail@Address"
  recipients(2) = "SecondEmail@Address"
  
  Call MailDoc.ReplaceItemValue("SendTo", recipients)
  Call MailDoc.ReplaceItemValue("Subject", "New Subject Text")
  'Create and set the Body content
  Set Body = MailDoc.CreateRichTextItem("Body")
  Call Body.AppendText("Body text here")
  Call Body.AddNewLine(2)
  'Example to create an attachment (optional)
  'EMBED_ATTACHMENT (1454)
  'EMBED_OBJECT (1453)
  'EMBED_OBJECTLINK (1452)
  Call Body.EmbedObject(1454, "", "C:\NotNameGiven1.jpg", "Attachment")
  Call Body.EmbedObject(1454, "", "C:\NotNameGiven2.jpg", "Attachment")
  'Example to save the message (optional)
  MailDoc.SaveMessageOnSend = True
  'Send the document
  'Gets the mail to appear in the Sent items folder
  Call MailDoc.ReplaceItemValue("PostedDate", Now())
  Call MailDoc.Send(False)
  'Clean Up
  Set Maildb = Nothing
  Set MailDoc = Nothing
  Set Body = Nothing
  Set Session = Nothing
End Sub

 

[CazB]

Eventually solved this.... by creating a Public mail group on the mail server, and adding all the receipients into it!

Never did find a 'proper' solution, but definitely got a workaround now

Thanks to those who tried to help