Sending a Text file via Lotus Notes and Excel VBA

[Positive Parrot]

I've lifted some very useful code (attached below) off this forum which allows the sending of Emails with attachments from within Excel using Lotus Notes as an Email client.

The code works fine if the attachement is a spreadsheet but if the attachment is a text file the recepient of the Email receives an annoying message when they open the Email i.e.

Instance member APPENDRTITEM does not exist

They can still click OK to clear the message and open the attachment but is there any way I can tweak the VBA to stop this happening.

Thanks

PP

Sub NotesCoreCode()
            
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With
    
  ' Declare Variables for file and macro setup

    Dim UserName As String
    Dim MailDbName As String
    Dim Maildb As Object
    Dim MailDoc As Object
    Dim AttachME As Object
    Dim Session As Object
    Dim EmbedObj1 As Object

  ' Open and locate current LOTUS NOTES User

    Set Session = CreateObject("Notes.NotesSession")
        UserName = Session.UserName
    MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
    Set Maildb = Session.GETDATABASE("", MailDbName)
    If Maildb.IsOpen = True Then
    Else
        Maildb.OPENMAIL
    End If

  ' Create New Mail and Address Title Handlers

    Set MailDoc = Maildb.CreateDocument

    MailDoc.Form = "Memo"
    '   Select range of e-mail addresses
    Recipient = Sheets("E-Mail Addresses").Range("A2").Value
    MailDoc.SendTo = Recipient
    
    ans = MsgBox("Would you like to Copy (cc) anyone on this message?" _
        , vbQuestion & vbYesNo, "Send Copy")
    
        If ans = vbYes Then
            ccRecipient = InputBox("Please enter the additional recipient's e-mail address" _
                , "Input e-mail address")
            MailDoc.CopyTo = ccRecipient
        End If
            
    MailDoc.Subject = "Pending Report"
        MailDoc.Body = _
    "Attached is a Pending Report.  Please acknowledge receipt."

'   Select Workbook to Attach to E-Mail

    MailDoc.SaveMessageOnSend = True
        attachment1 = "C:\FileToSend.txt" '    Required File Name

    If attachment1 <> "" Then
        On Error Resume Next
            Set AttachME = MailDoc.CREATERICHTEXTITEM("attachment1")
            Set EmbedObj1 = AttachME.embedobject(1454, "attachment1", "C:\FileToSend.txt", "") 'Required File Name
        On Error Resume Next
    End If

    MailDoc.PostedDate = Now()
        On Error GoTo errorhandler1
    MailDoc.Send 0, Recipient

    Set Maildb = Nothing
        Set MailDoc = Nothing
            Set AttachME = Nothing
        Set Session = Nothing
    Set EmbedObj1 = Nothing

errorhandler1:

    Set Maildb = Nothing
        Set MailDoc = Nothing
            Set AttachME = Nothing
        Set Session = Nothing
    Set EmbedObj1 = Nothing

    With Application
        .ScreenUpdating = True
        .DisplayAlerts = True
    End With
    
End Sub

 

[Juan Pablo González]

The syntax that I found here is slightly different, try it to see if it works (They attach a text file too)

http://www.unisyn.com/automate4/user/Learning/BASIC/VBA_Scripts/lotus01.html

 

[Positive Parrot]

Juan Pablo,

Thanks for your suggestion - I tried your suggested code and found it gave the same result as the original i.e. the Email recepient got the same message

Instance member APPENDRTITEM does not exist

Regards,

PP

 

[mettekr]

I realize this post is several years old now, but if anybody looks at it again, I have a question.

I've modified this code to fit my needs and is working great, but there's one small annoyance with how the text is coming into lotus. Below is the code for the MailBody section of my code:

        Dim MailBody 
        MailBody = "Please see the attached booking request form for a " & Model & " to " & POENT & ", ex-" & POEX & ", on the " & TgtVessel & Chr$(13) & _
                    Chr$(13) & Signature
 
        MailDoc.Body = MailBody

Now for some reason, in Lotus, only 69-70 characters will show up per line, then it will go down to the next line.

Meaning, if my variables made the above read:

Please see the attached booking request for a XYZ to Brazil, ex........
[enter/Chr$(13)]
Signature...

It will show up in Lotus like this:

Please see the attached booking request for a XYZ
[enter]
to Brazil, ex..........
[enter/Chr$(13)]
Signature

So basically, it cuts off my lines and goes down a line where there is no reason. there is tons of space left in the email.

Who knows if that made any sense...

Thanks