Playing with vba sending email through lotus notes

Question

I have already seen a lot of posts related to that, both on the web overall or on stackoverflow. However, I didn't see people making too much changes and really playing with this macro.

Ok, it's possible to send emails through lotus notes using VBA, but how can I make these emails cooler? Change font format or color for example? Change the styles, choosing to insert picture as a link or embedding it?

Well, this is what I got so far by searching around and making a few changes:

Sub SendEmail(Subject, Text)

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 
Call session.Initialize("Your Password Here")
'Open the mail database in notes
Set Maildb = session.GetDatabase("Mail Server", "mail\    .nsf")
If Not Maildb.IsOpen = True Then
Call Maildb.Open
End If
'Create the mail document
Set mailDoc = Maildb.CreateDocument
Call mailDoc.ReplaceItemValue("Form", "Memo")
'Set the recipient (you can write the name of a list you saved in your Lotus Notes)
Call mailDoc.ReplaceItemValue("SendTo", "email1@email.com.br")
'Set subject
Call mailDoc.ReplaceItemValue("Subject", Subject)
'Create and set the Body content
Set body = mailDoc.CreateRichTextItem("Body")
Call body.AppendText(Text)
'Example to create an attachment (optional)
Call body.AddNewLine(2)
'Insert an pdf attached
Call body.EmbedObject(1453, "", "C:\Desktop\Test.pdf")
Call body.AddNewLine(2) 'add line to separate text
'Message in the end of the email
Call body.AppendText("This is an automatic message.")
'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

Btw, I use the Windows Task Scheduler to call a VBS which will then call a macro that will call the macro to send email with a specific subject and text. As I have several macros that generates emails and each one has its subject and text, I thought this would be better.

This is the vbs (this is probably useless and everyone knows here, but I will share anyway):

'Run VBA Using VBS
Option Explicit

On Error Resume Next

ExcelMacroExample

Sub ExcelMacroExample() 

    Dim xlApp 
    Dim xlBook 

    Set xlApp = CreateObject("Excel.Application") 
    Set xlBook = xlApp.Workbooks.Open("C:\Desktop\Macros.xlsm") 'Excel filename
    xlApp.Run "SendEmail" 'Excel macro name
    xlApp.Quit 

    Set xlBook = Nothing 
    Set xlApp = Nothing 

End Sub 

Answer 1

A couple of things here:

• It's going to be easier if you can first write the code in Domino Designer (i.e. get a machine installed with the Notes Client and the Domino Designer Client). At the moment you are using Notes as a COM Server. The big disadvantage is that you have almost no debugging information if something fails. Write the code first in LotusScript, then port it to VBS (they are very similar dialects of BASIC).

• You can create a Notes RichText E-Mail (this is what you are doing now with CreateRichTextItem). You can manipulate the RichTextItem with different methods, the most important one being NotesRichTextStyle, which you have to think of as 'bits of formatting that will change everything afterwards'. You need to create the NotesRichTextStyle Object, configure it (i.e. font, bold, etc) and insert it into the rich text field. If this sounds klunky, that's because it is.

  Dim db As NotesDatabase
  Dim session As New NotesSession
  Set db = session.CurrentDatabas 
  Dim doc As New NotesDocument(db)
  Call doc.AppendItemValue("From", session.UserName)
  Call doc.AppendItemValue("Subject", _
  "Meeting time changed")
  Dim richStyle As NotesRichTextStyle
  Set richStyle = session.CreateRichTextStyle
  Dim richText As New NotesRichTextItem(doc, "Body")
  Call richText.AppendText("The meeting is at ")
  richStyle.Bold = True
  Call richText.AppendStyle(richStyle)
  Call richText.AppendText("3:00")
  richStyle.Bold = False
  Call richText.AppendStyle(richStyle)
  Call richText.AppendText(" not 2:00")
  Call doc.Save(True, False)
  

• If you want even more control, then you can create an HTML E-Mail wrapped in Mime, but it's fiddly at best and you're looking at several days of painful steps until it works, and you really would need an experienced professional for this. This is a good start: other Stackoverflow question

• The way you're referencing the user's mail reference is horrible. It's hardcoded and will only ever work for that one particular database, even if the person in question changes name, for instance. This is much better:

  Dim db As New NotesDatabase( "", "" )
  Call db.OpenMail