Macro to Send E-Mail (Lotus Notes): Need to Create Hotspot

I have written a macro to generate a PDF of my spreadsheet and generate and send an e-mail through Lotus Notes v8.5 to my distribution group for this report. This report is stored on a network drive, and rather than send out the file as an attachment, I'd like to send out a link in Lotus Notes. Highlighting the text, then selecting Create -> Hotspot -> Link Hotspot is the way I've accomplished this when manually sending the e-mail. Is there a way that I can send a link to the file using a macro (and if so, what is it)? Thanks in advance for your help!

As of 09/09/12 at 18:56 EST, I haven't gotten any replies yet, but 37 views. Is that because those viewing aren't sure how to help, or my question isn't explained clearly enough?

I think you'll need to create a Notes HTML email with MIME body and headers. Then the local/network file link hotspot is a standard HTML hyperlink (anchor tag). Search these terms to find examples.

Originally Posted by zumbalj

VBA gives me the Compile error: Expected: expression, and highlights the = < section of the line beginning oDoc.body.

The Body property is a string, so the whole string must be surrounded by double quotes, with pairs of double quotes ("") or single quotes (') to surround embedded strings, like this:

oDoc.body = "<a href=""\\server\share\folder\file.pdf"">Launch report</a>"

However, I doubt whether this will render as HTML because the Body property expects plain text.

As I said, MIME headers must be used to create a HTML email. Here is an example - http://publib.boulder.ibm.com/infoce...ETHOD_DOC.html. If you can't get it to work, search for some of the object names/properties in the code and you should find more examples.

More sample code here - http://www-01.ibm.com/support/docvie...id=swg21098323. It's written in LotusScript, but should work in VBA with minimal changes.

For VBA to recognise object (class) names such as NoteSession and NotesDocument, in the VBA project Tools - References - tick Lotus Domino Objects. If not listed, browse to and select domobj.tlb in your Program Files\Lotus Notes folder.

Below is the code from the 2nd link converted to VBA.

For the 'not initialized' error, which also happens with this code once you correct the syntax errors, a call to s.Initialize is needed because the code is not running as a LotusScript agent (within Lotus Notes) which would automatically initialise the session.

For the syntax errors, this is explained by "Notice that the stream.WriteText method uses the pipe (|) string delimiter rather than quotation marks. This usage makes it easier to place quotation marks directly into the string.". VBA uses " as the string delimiter, so we change every | to ", and use ' (or "") instead of " for a string within the string (" or ' can be used for strings within HTML).

You will need to edit the code for it to work correctly on your system. The lines which need changing are indicated by 'CHANGE THIS' in the code.

Please Login or Register  to view this content.

Glad it works for you. Just a warning that the email may be detected as spam by the recipient's mail server (depending on whether it uses a spam checker and how the spam tests are configured). One item which may fail the tests is the From address which needs a space separating the display name from the angle brackets containing the email address:

Please Login or Register  to view this content.

Another is due to the code only creating a text/html part, and might require a text/plain part as well. Let me know if the email fails the spam tests for this reason and I will fix the code.

Hi, Guys and Chippy,

I will like to do the same thing too as in create hotspot for my Excel file when send to Lotus Notes but when try to use Chippy code, there is error..:-(

As first time trying the NotesMimeEntity language, can anyone help me to check what's wrong in my language? Really desperate need help now... Thank you so much.

Please Login or Register  to view this content.

Originally Posted by EvelynLoh

I will like to do the same thing too as in create hotspot for my Excel file when send to Lotus Notes but when try to use Chippy code, there is error..:-(

What is the exact error message and which line causes it? To find the line, click Debug on the error message prompt and post the line highlighted in yellow.

Try to get my code working (as it is known to work), changing only the lines with the "CHANGE THIS" comment. Once my code is working in your environment you can customise it as required.

PS - please post VBA code inside CODE tags, like this:

[CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]

EvelynLoh,

Welcome to the forum. 2 pointers -

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.