Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-05-18
30
Medium Priority
?
789 Views
Last Modified: 2013-12-18
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
 
ThisWorkbook.Save
 
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
Else
Maildb.OPENMAIL
End If
 
Set MailDoc = Maildb.CREATEDOCUMENT
 
MailDoc.Form = "Memo"
MailDoc.sendto = "test@email.com"
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

0
Comment
Question by:MichaelC22
  • 11
  • 10
  • 7
  • +1
30 Comments
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 1800 total points
ID: 24419750
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.

PS
* I had to withdraw that statement several times, so others might prove me wrong here once again...
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24422589
Try to find solution here:
http://www.bigresource.com/VB-Pasting-Bitmap-from-Excel-To-Lotus-Notes-ITAkAnGETs.html

No need to use UI methods at all.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24422695
Or here:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Objects/Q_22555251.html

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
		Next
 
		' 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
		Next
 
		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

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24423008
> 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...

0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24423698
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...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24424308
Let's hope so...  :-)

And read the sentence again, the one starting with "I have code...".
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24429604
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).
0
 

Author Comment

by:MichaelC22
ID: 24432198
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!
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24433276
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?
0
 

Author Comment

by:MichaelC22
ID: 24434170
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!
0
 
LVL 63

Expert Comment

by:SysExpert
ID: 24434518
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 !
0
 

Author Comment

by:MichaelC22
ID: 24434797
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!
0
 

Author Comment

by:MichaelC22
ID: 24455238
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24457078
Just to help the helpers: what's your project's status, and what are your lines of investigation? I lost the overview a little...
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24471520
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.
0
 

Author Comment

by:MichaelC22
ID: 24485589
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24488417
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?
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24490676
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...
0
 

Author Comment

by:MichaelC22
ID: 24503477
Thanks!
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.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 24503559
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??
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24515525
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):

http://www.experts-exchange.com/Software/Server_Software/Email_Servers/Lotus_Domino/Q_24437260.html

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.
0
 

Author Comment

by:MichaelC22
ID: 24518915
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!
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24524449
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?
0
 

Author Comment

by:MichaelC22
ID: 24536747
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!
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24544822
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
 
  Const EMBED_ATTACHMENT = 1454
  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

0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24735887
Is this resolved?
0
 

Author Comment

by:MichaelC22
ID: 24745826
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...
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 26339340
Difficult to say if there's a correct answer. Depends on which road was taken. Asker should give his verdict.
0
 

Author Closing Comment

by:MichaelC22
ID: 31582722
Sorry, forgot to close
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question