Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Code to send e-mail using Lotus Notes from MS Access is generating error message

Hi Experts,
a few years ago some experts helped me create the code that would enable me to generate a Lotus Notes e-mail from MS Access. It has worked fine until recently. Now the e-mail still transmits with the attachment but the message text that was meant to accompany the attachment does not get to the receiver, only the attachment. When I look at the message from my sent folder I get the message "Warning: Ignoring portion of document that uses a feature from another version of Notes" and it won't even open the sent message for me to look at it. I am running Lotus Notes 6.5. I think the offending code is somewhere in the following. Can anyone help me figure out what is wrong?

Sub EmailCode()
    Application.ScreenUpdating = False

    On Error Resume Next
    Set Session = GetObject(, "Notes.NotesSession")
    On Error GoTo ErrorHandler
    Dim WasOpen As Boolean
   
    If Session Is Nothing Then
        WasOpen = False
        Set Session = CreateObject("Notes.NotesSession")
    Else
        WasOpen = True
    End If
    If Subject = "Weekly update" Then WasOpen = True ' force Notes to stay open when sending data files
    UserName = Session.UserName
   
    On Error GoTo ErrorHandler
    Set Maildb = Session.GETDATABASE("", "")
    Maildb.OPENMAIL    'This will prompt you for password
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.Subject = Subject
    ActiveWorkbook.Save
    Attachment = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   
    Set AttachME = MailDoc.CREATERICHTEXTITEM("BODY")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "BODY")
    MailDoc.body = BodyText
    If SendCancel = "SendSave" Then
        MailDoc.SAVEMESSAGEONSEND = True
    Else
        MailDoc.SAVEMESSAGEONSEND = False
    End If
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.SEND 0, Recipient
    'Clean Up'
    If WasOpen = False Then
        Session.Close
        KillNotes
    End If

    Set Session = Nothing
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set EmbedObj = Nothing
   
    On Error Resume Next
    If Subject = "Weekly update" Then
        'dont display message
    Else
        MsgBox "Message to " & Recipient & _
        " was successfully sent. ", vbOKOnly, "E-Mail Sent"
    End If
    Exit Sub ' EXIT SUB TO AVOID ERROR HANDLER

ErrorHandler:
    MsgBox "Your E-mail has failed"  _
        & vbCrLf & vbCrLf & "Diagnistics: (EmailCode) Recipient: " & Recipient & " Subject: " & Subject & " Attachment: " & Attachment _
        & vbCrLf & vbCrLf & Err.Description _
        , vbCritical, "Notes Connection Error"
    SendCancel = "Failed"
    On Error Resume Next
    Exit Sub
End Sub
0
Rob4077
Asked:
Rob4077
  • 6
  • 3
  • 2
  • +1
2 Solutions
 
puppydogbuddyCommented:
Hi Rob,
I have not used Lotus Notes, but I have the following link in my files re: sending emails using Lotus Notes that may be helpful to you for comparision purposes against your code, or if it works well, you might want to consider replacing your code with it.

           http://www.fabalou.com/VBandVBA/lotusnotesmail.asp

Hope this helps.

0
 
ZvonkoSystems architectCommented:
Change this lines:
    Attachment = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   
    Set AttachME = MailDoc.CREATERICHTEXTITEM("BODY")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "BODY")
    MailDoc.body = BodyText


To this:
    AttachmentText = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   
    Set AttachME = MailDoc.CREATERICHTEXTITEM("ATTACHMENT")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", AttachmentText, "BODY")
    MailDoc.CREATERICHTEXTITEM ("ATTACHMENT")
    MailDoc.body = BodyText


0
 
ZvonkoSystems architectCommented:
Sorry, typo.
Change this lines:
    Attachment = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   
    Set AttachME = MailDoc.CREATERICHTEXTITEM("BODY")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "BODY")
    MailDoc.body = BodyText


To this:
    AttachmentText = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
   
    Set AttachME = MailDoc.CREATERICHTEXTITEM("ATTACHMENT")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", AttachmentText, "ATTACHMENT")
    MailDoc.CREATERICHTEXTITEM ("ATTACHMENT")
    MailDoc.body = BodyText


And please give the points to puppydogbuddy
The example is derived from his reference.

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.

 
Rob4077Author Commented:
Hi Zvonko and puppydogbuddy and thanks to both of you. I have been out all day I so have only now been able to look at your answers and finish fixing the problem. PDB, your code led Zvonko to the section of offending code but Zvonko's solution was not quite right though very close. As it turns out I had one line of code that was wrong and needed changing. The working code ended up being changed from:
    Set AttachME = MailDoc.CREATERICHTEXTITEM("BODY")
To:
    Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")

Thanks to both of you. PDB, your reference led to the solution and Zvonko has asked that I award the points to you but is seems fairer to me to share them since Zvonko did the analysis, so I hope you are both happy with that outcome.
0
 
puppydogbuddyCommented:
Rob,
No problem whatsoever sharing points.  Just glad Zvonko and I were able to collaborate to help you resolve your problem.

pDog
0
 
qwaleteeCommented:
Just a forthe record note.  Your fix -- createRichTextItem("Attachment") -- does get you the result you want, but it does make Notes work in a way that it really doesn't want to.

Your problem is that you used the "Body" in two different incomptaible way.  Body is a Notes "rich text field," which can hold plain text, colored text, other formatting.... and attachments.  There are some relatively convoluted programming methods that Notes uses to allow that.  Notes also allows you to treat the Body field as a simple plain text field, just like subject.  But you cna;t do both at the same time.

So the following three lines do nolt work correctly TOGETHER:

Set AttachME = MailDoc.CREATERICHTEXTITEM("BODY")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "BODY")
    MailDoc.body = BodyText
   

The first one sets up Body as RICH TEXT, the second takes advanatge of rich text to add an attachment to body, and the third addumes that Body can be a simple plain text field again.  To fix this, all you need to change is the third line:

Set AttachME = MailDoc.CREATERICHTEXTITEM("BODY")
    Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", Attachment, "BODY")
    AttachME.appendText BodyText
   
Instead of just assigning BodyText to the "plain text" Body, we use the rich text method AppendText to add BodyText to the existing RichText field that contains the attachment.
0
 
Rob4077Author Commented:
Hi qwaletee and thanks for the clarification and explanation. I am totally unfamiliar with Notes so your explanation and fix is much appreciated. Unfortunately I have already awarded the points so I have none left on the question to share with you - unless you know how I can re-allocate and share points or perhaps I can raise a new question to award you some extra points which I am happy to do (I am a paying unlimited points member).
Rob
0
 
Rob4077Author Commented:
Hi qwaletee, I just tried the change you suggested and it's gone back to sending the attachment without the text, though I can see what has been sent and I don't get the original warning. Something is therefore still not right and my adjusted code appears to work correctly. Any suggestions?
0
 
Rob4077Author Commented:
Sorry qwaletee, I just re-checked and found the error (my error). Your code appears to work so my original comments about opening a new question stand. Happy to do it if you like!!!
0
 
qwaleteeCommented:
No need, I just didn't want to leave a kldugy asnwer hanging out there when a correct one was available.
0
 
Rob4077Author Commented:
Ok thanks very much for your help and clarification. Rob
0
 
qwaleteeCommented:
No problem.  By the way, the reason why your fix of  Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment") worked is that you essentially moved the "rich text" (formatted text and attachments) out of the field named body into a new field, Attachments.  That allowed you to use the Body field as plain text only (no more conflict).  The reaosn it is a kludge is that Notes does not have a field in email called Attachment.  Theoretically, you attachment would be hidden with no field to display it.  However, because of a fluke in Notes design, any attachments that are in fields that do not display, will get displayed anyway, at the bottom of the message, with a horizontal bar above it.
0
 
Rob4077Author Commented:
Thanks for the clarification. The display you describe is exactly what happened. I really appreciate your help. Thanks again. Rob
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now