none
creating Lotus Note Email - signature deleted and colour removed RRS feed

  • Question

  • Hi Team,

    Need some assistance guys. I have searched teh web, but can't seem to find a solution.  The below attached code creates a lotus note email from excel. However when it creates the new email in Lotus notes, it remove's the default signature that I use in my Lotus notes.  Also would it be possible to change the colour of the wording "Excel cells are shown above" in the body of the text to be in blue?

    Thanks for all your help.

    Sub Request() Dim NSession As Object Dim NDatabase As Object Dim NUIWorkSpace As Object Dim NDoc As Object Dim NUIdoc As Object Dim WordApp As Object Dim subject As String subject = "Pasted Excel cells using Word PasteSpecial method " & Now Debug.Print subject Set NSession = CreateObject("Notes.NotesSession") Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace") Set NDatabase = NSession.GetDatabase("", "") If Not NDatabase.IsOpen Then NDatabase.OPENMAIL 'Create a new Lotus Notes document Set NDoc = NDatabase.CreateDocument With NDoc .SendTo = "email.address@email.com" 'CHANGE RECIPIENT EMAIL ADDRESS .CopyTo = "" .subject = subject 'Email body text, including marker text which will be replaced by the Excel cells .body = "Text in email body" & vbLf & vbLf & _ "**PASTE EXCEL CELLS HERE**" & vbLf & vbLf & _ "Excel cells are shown above" .Save True, False End With 'Edit the just-created document to copy and paste the Excel cells into it via Word Set NUIdoc = NUIWorkSpace.EDITDocument(True, NDoc) With NUIdoc 'Find the marker text in the Body item .GotoField ("Body") .FINDSTRING "**PASTE EXCEL CELLS HERE**" '.DESELECTALL 'Uncomment to leave the marker text in place (cells are inserted immediately before) 'Copy Excel cells to clipboard Sheets("Request_data").Range("A1:C6").Copy 'CHANGE SHEET AND RANGE TO BE COPIED AND PASTED 'Create a temporary Word Document Set WordApp = CreateObject("Word.Application") WordApp.Visible = False 'True to aid debugging WordApp.Documents.Add 'Paste into Word document and copy to clipboard With WordApp.Selection .PasteSpecial DataType:=10 'Enum WdPasteDataType: 10 = HTML; 2 = Text; 1 = RTF .WholeStory .Copy End With 'Paste from clipboard (Word) to Lotus Notes document .Paste Application.CutCopyMode = False WordApp.Quit SaveChanges:=False Set WordApp = Nothing .Send .Close End With Set NSession = Nothing

    End sub


    Wednesday, February 26, 2014 12:33 PM