Lotus Notes - Body Text

[Indigo]

I'm still a newbie when it comes to VBA so I was wondering if someone could direct me on how to accomplish this... I have a database where floor management input productivity updates on a quarterly basis and email them to upper management from the database. The email has a .rtf attachment. Upper management would prefer to get this information in the body of the email as opposed to an attachment because they use a blackberry. I'm not sure how to collect this information (stored in 2 different tables) and send it in the body of an email.

I am using the following (found on the net) to create the notes session and prepare the email:

'This public sub will send a mail and attachment if neccessary to the
'recipient including the body text.
'Requires that notes client is installed on the system.
Public Sub SendNotesMail(Subject As String, Attachment As String, Recipient As String, BodyText As String, SaveIt As Boolean)
'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)
    'Start a session to notes
    Set Session = CreateObject("Notes.NotesSession")
    'Next line only works with 5.x and above. Replace password with your password
    'Session.Initialize ("password")
    '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 or using above password you can use other mailboxes.
    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
     Else
         Maildb.openmail
     End If
    'Set up the new mail document
    Set MailDoc = Maildb.createdocument
    MailDoc.Form = "Memo"
    MailDoc.sendto = Recipient
    MailDoc.Subject = Subject
    MailDoc.Body = BodyText
    MailDoc.SaveMessageOnSend = SaveIt
    'Set up the embedded object and attachment and attach it
    If Attachment <> "" Then
        Set AttachME = MailDoc.createrichtextitem("Attachment")
        Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "Attachment")
        MailDoc.ReplaceItemValue "CreateRichTextItem", ("Attachment")
    End If
    'Send the document
    MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
    MailDoc.Send 0, Recipient
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set Session = Nothing
    Set EmbedObj = Nothing
End Sub

I then call the following:

Sub QtrEmail()
DoCmd.OutputTo acOutputReport, "rptAMQtrUpt", _
acFormatRTF, "J:\MFG\Shop\AMQtrUpt.rtf", False
 
Dim stTomb As String
 
stTomb = "[EMAIL="myboss@abc.ca"]myboss@abc.ca[/EMAIL]"
 
Call SendNotesMail("Quarterly Update", "J:\MFG\Shop\AMQtrUpt.rtf", stTomb, "Report attached", True)
 
End Sub

I gather I need to declare my "BodyText" as a variable.... I'm just not certain what all I need to do to get the information emailed without an attachment. Can anyone point me in the right direction? Thanks

 

[Indigo]

I have been experimeting and I hope that someone can give me some direction.

I did the following:

Sub SendEmail()
 
Dim stTomb As String
Dim HoldNotes As String
 
HoldNotes = "Safety Notes:  " & Forms!frmTest!Notes.Value
 
stTomb = [EMAIL="myboss@abc.ca"]myboss[EMAIL="myboss@abc.ca"]@abc.ca[/EMAIL][/EMAIL]
 
    Call SendNotesMail("Test Email", stTomb, HoldNotes, True)
 
End Sub

And that works. But I have multiple lines to send and I cannot get the body of the email to move to a new line with each "Hold" item.

For example:

Sub SendEmail()
 
Dim stTomb As String
Dim HoldShift As String
Dim HoldNotes As String
 
HoldShift = "Shift:  " & Forms!frmTest!Shift.Value
HoldNotes = "Safety Notes:  " & Forms!frmTest!Notes.Value
stTomb = "[EMAIL="twelton@tmmc.ca"]twelton@tmmc.ca[/EMAIL]"
    Call SendNotesMail("Test Email", stTomb, HoldShift, HoldNotes, True)
 
End Sub

I altered the "SendNotesMail" from my original email to:

Public Sub SendNotesMail(Subject As String, Recipient As String, BodyText As String, BodyText2 As String, SaveIt As Boolean)
.
.
.
MailDoc.Body = BodyText & BodyText2

but I get:

"Shift: ASafety Notes: testing, testing …. 1 2 3"

In the body of my email. I would prefer to get:

"Shift: A
Safety Notes: testing, testing …. 1 2 3"

I have experimented by adding a line to the Public Sub so I have:

MailDoc.Body1 = BodyText1
MailDoc.Body2 = BodyText2

But that doesn't work.

As well, some of the information in the email I need to send is numerical so I changed the line to read:

Public Sub SendNotesMail(Subject As String, Recipient As String, BodyText As String, BodyText2 As Integer, SaveIt As Boolean)

and in my Sub SendEmail I declare the variable as an integer but I get a type mismatch error. If I change everything to a string, I get a blank email.

Any pointers or tips would be appreciated. Thank you.

 

[SOS]

You can try

MailDoc.Body = BodyText & vbCrlf & BodyText2

But I really think you will need:

MailDoc.Body = BodyText & "</br>" & BodyText2

 

[Indigo]

hmmm.... let me try.... I also got the number thing sorted out so its just the new line issue.....

 

[SOS]

Oh, and now that I think about it, it may just be

<br>

without the slash.

 

[Indigo]

Thank you! SOS.....

vbCrlf worked beautifully!

 

[Indigo]

</br> or <br> didn't work at all.....

 

[SOS]

Good, glad we could help.

 

[SOS]

</br> or <br> didn't work at all.....

Yeah, it all depends so I'm glad the vbCrLf (VB Carriage Return/Line Feed) worked for you.

 

[ghudson]

</br> or <br> didn't work at all.....

Those are HTML codes. Since you are inserting text into the body with the code you are using vbcrlf is what will work.

 

[SOS]

Those are HTML codes. Since you are inserting text into the body with the code you are using vbcrlf is what will work.

Like I said, it depends. If you are using HTML format mail then the <br> is needed. If Text, the vbCrLf.

 

[tony73]

hi,

I'm newbie to ACCESS 2007 and VBA. I've done FORM with ACCESS 2007 where i Put some dates and create button to send email with this objetcs.
I've used event/action SENDOBJECT to create email, that work very well with OUTLOOK. I need action to send with LOTUS NOTES client.

I've looked code for send with LOTUS NOTES used by INTIGO.
How can use this code? where i put code? I don't know VBA, but i need.

please.....

 

[Indigo]

You need to create a module in vba and insert the Public Sub
you can then call it from the command on your form.