VBA Help Needed - Excel to Lotus Notes (email)

[gkisystems]

I have the following macro that works fine except for 1 exception - it does not enter any text as the body of the message. How can I edit this code so it includes a message for the body of the email?


Sub EmailRDReport()
'Working in Excel 2010 with Lotus Notes
Dim wb As Workbook
Dim I As Long


Set wb = ActiveWorkbook


If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will" & vbNewLine & _
"be no VBA code in the file you send. Save the" & vbNewLine & _
"file first as xlsm and then try the macro again.", vbInformation
Exit Sub
End If
End If


On Error Resume Next
For I = 1 To 3
wb.sendmail "emailaddress1@example.com, emailaddress2@example.com, emailaddress3@example.com", _
"My Subject Line Goes Here"
If Err.Number = 0 Then Exit For
Next I
On Error GoTo 0
End Sub

 

[Rosen]

I am not aware of any way to use the workbook functionality to define the body of an email.

Try adding a reference to Lotes Notes in the VB Editor and accessing it directly. The object library should have a Document object which has a Body property I believe that lets you set the body of the email. If you need specific code reply back and I will dig it up for you, I have a number of spreadsheets at work that send emails via Lotes Notes.

 

[gkisystems]

I am not aware of any way to use the workbook functionality to define the body of an email.

Try adding a reference to Lotes Notes in the VB Editor and accessing it directly. The object library should have a Document object which has a Body property I believe that lets you set the body of the email. If you need specific code reply back and I will dig it up for you, I have a number of spreadsheets at work that send emails via Lotes Notes.

I believe there is code that allows you to define the body of the email. I found this website that offers some tips, except the VBA code that is offered that includes a message in the body of an email only works with Outlook, not Lotus Notes. See here:

Example Code for sending mail from Excel

Would you be able to give me the specific code that would work by adding a reference to Lotes Notes in the <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> Editor? I'm no expert on VBA, I'm just learning as I go and so far I'm only skilled in making minor changes to existing code...not creating new code from scratch.

 

[VoG]

I don't use Lotus Notes but this may help you Lotus Notes Mail code examples from Dennis Wallentin

 

[Rosen]

Here is the code I use.

Sub SendEMail(Recipient As Variant, Subject As String, Body As String)
  Dim notesSession As Object, notesDatabase As Object, notesDocument As Object
  Set notesSession = CreateObject("Notes.NotesSession")
  Set notesDatabase = notesSession.GETDATABASE("", "")
  If notesDatabase.IsOpen = False Then notesDatabase.OPENMAIL
  Set notesDocument = notesDatabase.CreateDocument  
  With notesDocument
    .Form = "Memo"
    .SendTo = Recipient
    .Subject = Subject
    .Body = Body
    .SaveMessageOnSend = False
  End With
  With notesDocument
    .PostedDate = Now()
    .Send 0, Recipient
  End With
End Sub

Recipients is aways an array of strings which are the email addresses.