How do I use VB to attach an Excel workbook to an active memo in Lotus Notes?

I have an Excel workbook with an email hyperlink on one of its worksheets. Activating the hyperlink creates a new memo in Lotus Notes, my default mail client. I'm trying to find a way to use Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) to attach the active workbook to the new memo. Furthermore, I'd like to strip all VB code from the attachment while keeping it intact in the original workbook.

I have code (not my work - pasted below) that I use to complete a similar task that involves using VB to create a new memo in Lotus Notes - now I'm looking for a way to attach an Excel workbook to an ACTIVE Lotus Notes memo instead of creating a new one.

Private Sub LotusSend
Dim Maildb As Object
Dim UserName As String
Dim MailDbName As String
Dim MailDoc As Object
Dim Session As Object
Dim sPath As String
sPath = ActiveWorkbook.FullName
Set Session = CreateObject("Notes.NotesSession")
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
Set Maildb = Session.GetDatabase("", MailDbName)
If Maildb.IsOpen = True Then
End If
MailDoc.Form = "Memo"
MailDoc.sendto = ""
MailDoc.Subject = "Test Memo"
MailDoc.Body = "Please confirm receipt of this email."
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", sPath, "Attachment")
Call MailDoc.Save(True, True)
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

Open in new window

Who is Participating?
Sjef BosmanGroupware ConsultantCommented:
Using COM, as you did in your code, you have no access to the foreground Notes-UI whatsoever.

So, IMHO, it is not possible like that. *

The only way I could think that might work is to use the APIs and the keyboard DLL. Get a handle on the Lotus Notes window, make it the active one, and use key sequences to manoeuvre. Then use File/Import to add the Excel-file.

* I had to withdraw that statement several times, so others might prove me wrong here once again...
Try to find solution here:

No need to use UI methods at all.
Or here:

Or use this code:
Sub SendMimeMemo(sendto As Variant, ByVal subject As String, ByVal html As String, attachments() As String, imageFiles() As String, imageTypes() As String, imageIds() As String, ByVal fromFile As Boolean)
	' * Sends an html formatted email.
	' * This function supports file attachments and inline images.
	' * The html source may be passed in as a string or may be read from a file.
	' * @param sendto A string (or array of strings) containing the email recipient(s).
	' * @param subject A string containing the email subject.
	' * @param html A string containing html for the email body or the path to an html file.
	' * @param attachments An array of strings containing the full path to files to attach to this email.
	' * @param imageFiles An array of strings containing the full path to any inline images.
	' * @param imageTypes An array of strings containing the mime type for the images in the imageFiles parameter ("image/jpg", "image/gif").
	' * @param imageIds An array of strings containing the content id (CID) for the images in the imageFiles parameter.
	' * @param fromFile Set this parameter to True if the html parameter points to a file.
	' */
	Dim sess As New NotesSession
	Dim db As NotesDatabase
	Dim doc As NotesDocument
	Dim stream As NotesStream
	Dim mimeBody As NotesMIMEEntity, mimeHtml As NotesMIMEEntity
	Dim mimeFile As NotesMIMEEntity, mimeImage As NotesMIMEEntity
	Dim mimeHeader As NotesMIMEHeader
	Dim mailServer As String, mailFile As String
	Dim convertMime As Boolean
	' Init Notes session
	Call sess.Initialize("")
	' Open user's email
	mailServer = sess.GetEnvironmentString("MailServer", True)
	mailFile = sess.GetEnvironmentString("MailFile", True)
	Set db = sess.GetDatabase(mailServer, mailFile)
	' Create an email doc
	Set doc = db.CreateDocument
	Call doc.ReplaceItemValue("Form", "Memo")
	Call doc.ReplaceItemValue("SendTo", sendto)
	Call doc.ReplaceItemValue("Subject", subject)
	doc.SaveMessageOnSend = True
	' add the body as a mime html part
	convertMime = sess.convertMime
	sess.convertMime = False
	Set stream = sess.CreateStream()
	If (fromFile) Then html = FileRead(html)
		stream.WriteText (html)
		Set mimeBody = doc.CreateMIMEEntity("Body")
		Set mimeHtml = mimeBody.CreateChildEntity
		Call mimeHtml.SetContentFromText(stream, "text/html; charset=""iso-8859-1""", ENC_QUOTED_PRINTABLE)
		Call stream.Close
		' add file attachments
		For i = 0 To UBound(attachments)
			Set mimeFile = mimeBody.CreateChildEntity
			Set mimeHeader = mimeFile.CreateHeader("Content-Transfer-Encoding")
			Call mimeHeader.SetHeaderVal("binary")
			Set mimeHeader = mimeFile.CreateHeader("Content-Disposition")
			Call mimeHeader.SetHeaderVal("attachment; filename=" & attachments(i))
			Call stream.Open(attachments(i), "binary")
			Call mimeFile.SetContentFromBytes(stream, "text/plain", ENC_NONE)
			Call mimeFile.EncodeContent(ENC_IDENTITY_8BIT)
			Call stream.Close
		' add images referenced by cid tags
		For i = 0 To UBound(imageFiles)
			Set mimeImage = mimeBody.CreateChildEntity()
			Set mimeHeader = mimeImage.CreateHeader("Content-ID")
			Call mimeHeader.SetHeaderVal("<" & imageIds(i) & ">")
			Call stream.Open(imageFiles(i))
			Call mimeImage.SetContentFromBytes(stream, imageTypes(i) & "; name=" + imageIds(i), ENC_IDENTITY_BINARY)
			Call stream.Close
		sess.convertMime = convertMime
		Call doc.CloseMIMEEntities(True, "Body")
		Call doc.Send(False)
End Sub
Public Function FileRead(ByVal path As String) As String
	' * Reads an entire file into a string.
	' * @param path The path to the file to read.
	' * @return A string containing the contents of the specified file.
	' */
	Dim fileNum As Integer
	fileNum = FreeFile
	Open path For Input As fileNum
	FileRead = Input$(LOF(fileNum), fileNum)
	Close fileNum
End Function

Open in new window

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Sjef BosmanGroupware ConsultantCommented:
> attach an Excel workbook to an ACTIVE Lotus Notes memo

Marko, now it's you who should read more carefully... at least, that means to me a new mail visible in the Notes client. I could be wrong though...

Yes, I've seen that, but when I looked at the code I saw Michael was using back-end approach.
Maybe it'll be fine... he already know it's not doable through UI, you provided that info...
Sjef BosmanGroupware ConsultantCommented:
Let's hope so...  :-)

And read the sentence again, the one starting with "I have code...".
Yap, you're right, active is even written in uppercase, my bad.

Michael, just an idea, if the workbook itself contains mailto address,
maybe you could modify code that after creating new memo it reads mailto address from excel and address the memo accordingly?

Or similar workaround, to select mailto address in excel and click on action that will attach that sheet and do the rest.

Anything to avoid locating UI document (all classes that represent front-end object has those two letters in their names L/N).
MichaelC22Author Commented:
mbonachi - I think I need to clarify my question. The code that I posted is for a similar, but ultimately different, task in Excel - it creates a new memo in Lotus Notes, saved as a draft, with the hard-coded multivalue text and the workbook attached as a Richtext document. This code is sort of my starting point for my current project.

My current project involves a workbook with an email hyperlink that already includes all of necessary multivalue text (incluing mailto:). Upon clicking on the hyperlink, I'm trying to find a method using FollowHyperlink to then attach the workbook to the newly-created memo.

Thank you for all of your comments and help so far!
You say that it doesn't matter for new e-mail to be already opened, all you care about is that all values from the sheet get transfered to e-mail.
Is that right?
MichaelC22Author Commented:
mbonaci - I'm not sure if I totally understand; I apologize if my question isn't clear. Here's my ideal workflow, described on a very high level:

1. User clicks email hyperlink in Excel worksheet
2. Hyperlink creates new memo in Lotus Notes
3. Code written in the FollowHyperlink section of the VB project promts the workbook to attach itselft to the newly-created memo while stripping ALL VB code from the attachment.

Does this sound doable? Thanks again!
I would consider some minor changes that may make this easier

1. User clicks email hyperlink in Excel worksheet
1A. worksheet without VB code is saved in a temp sir with a specific name

2. Hyperlink creates new memo in Lotus Notes
3. Code written in the FollowHyperlink section of the VB project that does a standard file attach , perhaps even using sendkey, since you know the exact filename and location.

I hope this helps !
MichaelC22Author Commented:
SysExpert - thank you for your input!

How exactly do I code steps 1A and 3? I'm not really sure where to start.

Thanks again!
MichaelC22Author Commented:
I am bumping up the point value of this question since it appears to be a little more difficult than I imagined!

Thanks again for the imput, everyone.
Sjef BosmanGroupware ConsultantCommented:
Just to help the helpers: what's your project's status, and what are your lines of investigation? I lost the overview a little...
You'll need to find out another way (other then user clicking on a mailto: link) to create a new memo, since an LN opened memo cannot be retrieved using COM.

Sysexpert, I don't see how you can alter the behavior of mailto link, since it's OS feature.
MichaelC22Author Commented:
OK - am I correct to understand that my original plan isn't feasible?

mbonaci - is there a logical alternative I should persue? I've got nothin'.

Thanks again, everyone.
Sjef BosmanGroupware ConsultantCommented:
Aaaaahhh, there may be a way, but it required code in Excel and code in Notes... And it won't be easy I think. And I've never even tried this, but it might just work.

The idea is to get your work done in Excel, then click a button or so in Excel.
The code there saves the worksheet, and creates a URL that starts with notes:///localdatabase/agent?OpenAgent&parameters or notes://server/database/agent?OpenAgent&parameters
The name of the database can probably be retrieved using COM.
The parameters should contain the worksheet file
The Agent might run in the foreground, might find the currently open mail and might add it

It's just an idea... Marko, what's your opinion? Is it feasible?
I think that this may well be the best advice so far...

Here's your answer Michael.
Start working on it and we'll help you if you get stuck...
MichaelC22Author Commented:
sjef_bosman - would your proposed solution require creating an Agent in Lotus Notes? I really have no idea how to go about creating a new agent, although I suppose I could figure it out. Any advice?

Thanks again.
Sjef BosmanGroupware ConsultantCommented:
Hmm, that can complicate matters... You need a Domino Designer client to modify the design of a database, in this case to add an agent.

Is this another dead end??
If you don't have access to Lotus design and you're familiar with basics of C++ and Java here's a complicated (but may be the only) solution:
The code provided here creates a L/N Java agent using C++ code (you cannot create LotusScript agent without already having LS code compiled inside Domino Designer):

But I would rather suggest some workaround, like this:
If you can get the mail address from your VBA code (e.g. your mail address is always in the same Excel cell, or you have only one e-mail address inside worksheet), you could use the code I posted earlier (comment ID:24422695) - create a VBA action that reads the address, and calls the provided function.
The key thing is that you have to get (inside VBA code) mail address before calling the function.
This way you don't have to obtain reference to memo document, which is your problem from the start.
MichaelC22Author Commented:
Let me see if I've got this right - thanks for walking me through everything:

- User clicks a mail hyperlink in the active Excel workbook

- Using FollowHyperlink in VB, Excel launches a URL initializing an agent in Lotus Notes that will attach the open workbook to the newly-created memo

Are we on the same page, or have I misinterpreted your proposal?

Thanks again for all of the help!
First - forget about mailto link and followHyperlink - it cannot be done, as we mentioned many times.

Second - answer the questions we asked:
 - do you have access to Domino Designer?
 - can you get the mail (mailto link) address from your code?
 - are you familiar with basics of C++ and Java?
MichaelC22Author Commented:
mbonaci - apologies, my understanding was that the URL-based solution proposed by sjef_bosman could be triggered using FollowHyperlink.

With regards to your questions:

 - do you have access to Domino Designer?
Yes - it appears that I have this functionality built into my version of Lotus Notes.

 - can you get the mail (mailto link) address from your code?
Yes, I should be able to pull that off.

 - are you familiar with basics of C++ and Java?
No - but I can probably get through simple tasks.

Thanks again for all of the help!
The easiest solution is to create LotusScript agent that will attach xls and address the memo, as Sjef suggested.

When user is ready to mail the sheet, he will click on an action or button.
That action will create (and save) a new NotesDocument inside your mail database, from Excel VBA or from VB (what is your IDE?) with three fields:
 - sendTo
 - subject
 - fullPath (e.g. "C:\Excel\Attachments\myAttachment.xls")

which you'll fill with appropriate values.
After that, the action will call the LS agent using URL syntax Sjef provided.

So LS agent should look like this:
Sub Initialize
  Dim s As New NotesSession
  Dim agent As NotesAgent
  Dim db As NotesDatabase
  Dim doc As NotesDocument, memo As NotesDocument
  Dim rtitem As NotesRichTextItem
  Set agent = s.CurrentAgent
  Set db = s.CurrentDatabase
     'Get document used for passing data
  Set doc = db.GetDocumentByID( agent.ParameterDocID )
     'Send mail containing passed data
  Set memo = New NotesDocument(db)
  memo.Form = "Memo"
  memo.SendTo = doc.GetItemValue( "sendTo" )(0)
  memo.Subject = doc.GetItemValue( "subject" )(0)
  Set rtitem = New NotesRichTextItem( doc, "Body" )
  Call rtitem.EmbedObject ( EMBED_ATTACHMENT, "", doc.GetItemValue( "fullPath" )(0))
  Call memo.Send( False )
     'Delete document used for passing data
  Call doc.Remove( True )
End Sub

Open in new window

Is this resolved?
MichaelC22Author Commented:
I haven't had the necessary resources to work on this as of late - don't worry, I haven't forgotten. Thank you for bearing with me...
Sjef BosmanGroupware ConsultantCommented:
Difficult to say if there's a correct answer. Depends on which road was taken. Asker should give his verdict.
MichaelC22Author Commented:
Sorry, forgot to close
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.