VBA Button To Send Email Via Notes 9

[MH22]

Good day everyone,

Looking for a simple way to add a form button onto an Excel 2016 database to simply open IBM Notes 9, fill in the TO and CC fields (with specific email addresses), fill in the SUBJECT and BODY (with specific information), and add my Notes email signature. Then I can hit SEND manually.

I know this should be fairly simple, but having a hard time finding the proper information.

Thanks all!

 

[portews]

Here's what I used to use before we switched over to Outlook, I'm not sure of the version it worked on, but give it a shot. I originally stole it from Send a Range as rich text in body through lotus notes and tweaked it some. This one also copies a range from the worksheet into the email body. If you don't need that, just rem out the section indicated.

Sub Notes_Email_Excel_Cells()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim noEmbedObject As Object
Dim noAttachment As Object

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")

'*** see who the user is and get their shared mail database
Set NDatabase = NSession.GETDATABASE("", "")

ReDim Preserve recipient(0)
ReDim Preserve ccto(0)

If Not NDatabase.IsOpen Then
    NDatabase.OPENMAIL
End If
'*** Set the Body text
Body = "What do you want to say?"

'the signature
sig = "Put your signature here"
'Create a new document
st = ThisWorkbook.FullName
Set NDoc = NDatabase.CREATEDOCUMENT
Set noAttachment = NDoc.CreateRichTextItem("st")
Set noEmbedObject = noAttachment.EmbedObject(1454, "", st)

With NDoc
    .SendTo = "recipient list"
    .Subject = "Email Subject"
End With

'Edit the just-created document to copy and paste the Excel cells into it

Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, NDoc)

With NUIdoc
    
    'Email body text, delete the old signature
    .FIELDCLEAR ("Body")
    .GOTOFIELD ("Body")
    'insert the top of the body from aboce
    .INSERTTEXT (Body)
    
'↓ Rem out this section if you don't want to copy your worksheet range to the body
    'insert the Excel sheet list copy
    ThisWorkbook.Sheets("Sheet1").Range("A1:D" & ThisWorkbook.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row).Copy
    .Paste
    Application.CutCopyMode = False
'↑ Rem out to above arrow
    
    .INSERTTEXT (sig)
End With

Set NSession = Nothing
End Sub

 

[MH22]

Here's what I used to use before we switched over to Outlook, I'm not sure of the version it worked on, but give it a shot. I originally stole it from Send a Range as rich text in body through lotus notes and tweaked it some. This one also copies a range from the worksheet into the email body. If you don't need that, just rem out the section indicated.

Sub Notes_Email_Excel_Cells()
Dim NSession As Object
Dim NDatabase As Object
Dim NUIWorkSpace As Object
Dim NDoc As Object
Dim NUIdoc As Object
Dim noEmbedObject As Object
Dim noAttachment As Object

Set NSession = CreateObject("Notes.NotesSession")
Set NUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")

'*** see who the user is and get their shared mail database
Set NDatabase = NSession.GETDATABASE("", "")

ReDim Preserve recipient(0)
ReDim Preserve ccto(0)

If Not NDatabase.IsOpen Then
    NDatabase.OPENMAIL
End If
'*** Set the Body text
Body = "What do you want to say?"

'the signature
sig = "Put your signature here"
'Create a new document
st = ThisWorkbook.FullName
Set NDoc = NDatabase.CREATEDOCUMENT
Set noAttachment = NDoc.CreateRichTextItem("st")
Set noEmbedObject = noAttachment.EmbedObject(1454, "", st)

With NDoc
    .SendTo = "recipient list"
    .Subject = "Email Subject"
End With

'Edit the just-created document to copy and paste the Excel cells into it

Set NUIdoc = NUIWorkSpace.EDITDOCUMENT(True, NDoc)

With NUIdoc
   
    'Email body text, delete the old signature
    .FIELDCLEAR ("Body")
    .GOTOFIELD ("Body")
    'insert the top of the body from aboce
    .INSERTTEXT (Body)
   
'↓ Rem out this section if you don't want to copy your worksheet range to the body
    'insert the Excel sheet list copy
    ThisWorkbook.Sheets("Sheet1").Range("A1:D" & ThisWorkbook.Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row).Copy
    .Paste
    Application.CutCopyMode = False
'↑ Rem out to above arrow
   
    .INSERTTEXT (sig)
End With

Set NSession = Nothing
End Sub

Thank you,

I will play around with it now, greatly appreciated.