VBA code for opening an email in Lotus Notes and copying/pasting the To:, Subject: Email Body: which is stored in a sheet.

CarlStephens

Board Regular
Joined
Sep 25, 2020
Messages
128
Office Version
  1. 2016
Platform
  1. Windows
Hello All,

I am pretty new to coding, and I was wondering whether there is a VBA code that will will open an email (in Lotus Notes that will already be open) and copy an email address and subject line stored in the spreadsheet, and paste the information in the respective email fields, along with the body of the email, which is in the spreadsheet too. An example of the spreadsheet is below:

Thank you for your time, and I shall look forward to hearing from you soon.

1601049343897.png
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Try this macro.
VBA Code:
Public Sub Create_And_Display_Notes_Email()

    Dim NSession As Object          'NotesSession
    Dim NUIWorkspace As Object      'NotesUIWorkspace
    Dim NMailDb As Object           'NotesDatabase
    Dim NDocument As Object         'NotesDocument - the email document
    Dim NItem As Object             'NotesItem
    Dim NRichTextItem As Object     'NotesRichTextItem
    Dim NEmbeddedObject As Object   'NotesEmbeddedObject
    Dim ToEmail As String, CCEmail As String, BCCEmail As String, Subject As String, BodyText As String
   
    With ActiveSheet
        ToEmail = .Range("C2").Value
        CCEmail = .Range("C3").Value
        BCCEmail = .Range("C4").Value
        Subject = .Range("C5").Value
        BodyText = Join(Application.Transpose(.Range("C7", .Cells(.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
    End With
   
    'Start a session to Lotus Notes
   
    Set NSession = CreateObject("Notes.NotesSession")           'OLE - late binding only
    Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
   
    Set NMailDb = NSession.GetDatabase("", "")                  'uses the default database (in Notes.ini)   
    If Not NMailDb.IsOpen Then NMailDb.OpenMail
   
    'Create new email document
   
    Set NDocument = NMailDb.CreateDocument
   
    With NDocument
        .ReplaceItemValue "Form", "Memo"
        .ReplaceItemValue "Subject", Subject
        .ReplaceItemValue "SendTo", ToEmail
        .ReplaceItemValue "CopyTo", CCEmail
        .ReplaceItemValue "BlindCopyTo", BCCEmail
   
        'Create a rich text item for the email body text
       
        Set NRichTextItem = .CreateRichTextItem("Body")
        With NRichTextItem
            .AppendText BodyText
            .AddNewLine 2
        End With
       
        'Save the email (in Drafts)
       
        .Save True, True, False
    End With
       
    'Open the newly created email via the Lotus front-end UI objects, so that user can review it
   
    Set NDocument = NUIWorkspace.EditDocument(True, NDocument)
   
    'Put cursor inside email body text
   
    NDocument.GoToField "Body"
       
End Sub
If you want to use multiple email addresses for To, CC and BCC then separate them with a comma in the cells.
 
Last edited:
Upvote 0
Thank you John, I really appreciate your reply. Quick question, if the mail server is already open will the code need to start a new session. Also, if the spreadsheet is going to be shared with different users that are using different mail servers, this code will only work with Notes users, correct?
 
Upvote 0
The code requires the Lotus Notes client on the PC and I think it works best if the Notes application and Notes Mail is already open and it will use that session. If the Notes application isn't open the code will open it. I don't know about the mail server. The macro only works with Notes email clients.
 
Upvote 0
Thank you so much John.....last two questions, and I promise I will leave it there. Where in the code would I need to add the font Calibri and size 11 (to ensure that I am following company guidelines and normally the email has a signature which is not appearing on the current code, is this something that can be added to the code?
 
Upvote 0
A different approach is needed if you want to create an email with the default font and signature. This macro creates the email using the Notes UI, so it should use the default font and signature. This time the Notes application (and Mail, I think) must be already open before running the macro.

VBA Code:
Public Sub Create_and_Display_Notes_Email2()

    Dim NUIWorkspace As Object
    Dim NUIDocument As Object
    Dim ToEmail As String, CCEmail As String, BCCEmail As String, Subject As String, BodyText As String
    
    With ActiveSheet
        ToEmail = .Range("C2").Value
        CCEmail = .Range("C3").Value
        BCCEmail = .Range("C4").Value
        Subject =  .Range("C5").Value
        BodyText = Join(Application.Transpose(.Range("C7", .Cells(.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
    End With
    
    Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
    
    'Create an email using the Notes UI
    
    NUIWorkspace.ComposeDocument , , "Memo"
    
    Do
        Set NUIDocument = NUIWorkspace.CurrentDocument
        DoEvents
    Loop While NUIDocument Is Nothing
    
    With NUIDocument
        .FieldSetText "EnterSendTo", ToEmail
        .FieldSetText "EnterCopyTo", CCEmail
        .FieldSetText "EnterBlindCopyTo", BCCEmail
        .FieldSetText "Subject", Subject
    
        .GoToField "Body"
        .InsertText BodyText        
    End With
    
End Sub
 
Upvote 0
Thank you John, you have been amazing and helped me get a project off of the ground, which is greatly appreciated?
 
Upvote 0
Hey John.....is there a code to add attachments based on the file path that will be on the sheet? Per the below for example. Thank you.

1601391523415.png
 
Upvote 0
You've moved the goalposts again and adding attachments is quite difficult.

Try this macro, which uses the cell addresses in your last screenshot and assumes the email body text starts in C12. I've also shown how to insert text in the attachments loop. The code uses VBA SendKeys to close a Notes dialogue prompt; although entirely reliable in my tests, SendKeys can be unreliable and I tried various ways of suppressing the prompt using LotusScript but was unsuccessful.

Note that the Lotus Notes application must be open and Mail must be the active window (tab) before running this macro.

VBA Code:
Public Sub Create_and_Display_Notes_Email3()

    Const EMBED_ATTACHMENT As Long = 1454
    
    Dim NSession As Object              'NotesSession
    Dim NUIWorkspace As Object          'NotesUIWorkspace
    Dim NDatabase As Object             'NotesDatabase
    Dim NUIDocument As Object           'NotesUIDocument
    Dim NRichTextAttachment As Object   'NotesRichTextItem
    Dim NDocument As Object             'NotesDocument
    Dim ToEmail As String, CCEmail As String, BCCEmail As String, Subject As String, Attachments As Variant, BodyText As String
    Dim i As Long
    Dim DocID As String
    
    With ThisWorkbook.Worksheets(1) 'ActiveSheet
        ToEmail = .Range("C3").Value
        CCEmail = .Range("C4").Value
        BCCEmail = .Range("C5").Value
        Subject = .Range("C6").Value
        Attachments = .Range("C8:C10").Value
        BodyText = Join(Application.Transpose(.Range("C12", .cells(.Rows.Count, "C").End(xlUp)).Value), vbCrLf)
    End With
    
    Set NSession = CreateObject("Notes.NotesSession")   'OLE (late binding only) because we access UI classes
    Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
    Set NDatabase = NSession.GetDatabase("", "")
    NDatabase.OpenMail
            
    'Create an email using the Notes UI
    
    Set NUIDocument = NUIWorkspace.ComposeDocument(, , "Memo")
    
    With NUIDocument
        .FieldSetText "EnterSendTo", ToEmail
        .FieldSetText "EnterCopyTo", CCEmail
        .FieldSetText "EnterBlindCopyTo", BCCEmail
        .FieldSetText "Subject", Subject
    
        'Insert body text
        
        .GoToField "Body"
        .InsertText BodyText & vbLf
        
        'Insert attachments in a rich text item
    
        Set NRichTextAttachment = .Document.CreateRichTextItem("Attachments")
        For i = 1 To UBound(Attachments)
            If Not IsEmpty(Attachments(i, 1)) Then
                If Dir(Attachments(i, 1)) <> vbNullString Then
                    .InsertText vbLf "File attached: " & Mid(Attachments(i, 1), InStrRev(Attachments(i, 1), "\") + 1)
                    NRichTextAttachment.EmbedObject EMBED_ATTACHMENT, "", Attachments(i, 1)
                End If
            End If
        Next
        
        'Save this document's ID, so that we can find it in Drafts
        
        DocID = .Document.UniversalID
        
        'Save and close the mail document. This displays the 'Send Mail' dialogue with 5 buttons: Send & Save, Send Only, Save Only, Discard, Cancel
                
        .Save
        .Close
        
        'Send 'v' key to click the 'Save Only' button to save mail document in Drafts
        
        Application.Wait DateAdd("s", 3, Now)
        AppActivate "Send Mail", True
        SendKeys "v"
        
    End With
    
    'Find the document in Drafts
    
    Set NDocument = Find_Document(NDatabase, "($Drafts)", DocID)
    
    'Reopen the document for user review and to update the rich text field containing the attachments
    
    If Not NDocument Is Nothing Then
        Set NUIDocument = NUIWorkspace.EditDocument(True, NDocument)
        NUIDocument.GoToField "Body"
    End If
    
End Sub


Private Function Find_Document(NMailDb As Object, View As String, DocumentUniversalID As String) As Object

    Dim NView As Object, NDoc As Object, NDocNext As Object
    
    Set Find_Document = Nothing
    Set NView = NMailDb.GetView(View)
    Set NDoc = NView.GetFirstDocument
    While Not NDoc Is Nothing And Find_Document Is Nothing
        Set NDocNext = NView.GetNextDocument(NDoc)
        If NDoc.UniversalID = DocumentUniversalID Then
            Set Find_Document = NDoc
        End If
        Set NDoc = NDocNext
    Wend

End Function

Here is a similar macro which creates and displays a Notes email with body text, automatic signature and attachment. It also copies and pastes in Excel cells as an image. It uses a different method which might work better for you.
 
Upvote 0
Thank you John. I am forever indebted to you. I am getting a syntax error below red line....do you know what the cause of this is?

1601548546795.png
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top