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

CarlStephens

New Member
Joined
Sep 25, 2020
Messages
35
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
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
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:

CarlStephens

New Member
Joined
Sep 25, 2020
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
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.
 

CarlStephens

New Member
Joined
Sep 25, 2020
Messages
35
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
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
 

CarlStephens

New Member
Joined
Sep 25, 2020
Messages
35
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you John, you have been amazing and helped me get a project off of the ground, which is greatly appreciated?
 

CarlStephens

New Member
Joined
Sep 25, 2020
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,465
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.
 

CarlStephens

New Member
Joined
Sep 25, 2020
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,114,279
Messages
5,546,943
Members
410,764
Latest member
Dedeke
Top