Solved

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

Posted on 2009-05-18
30
709 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 450 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now