Solved

Creating Hotspot URL in VBA

Posted on 2004-03-24
4
1,569 Views
Last Modified: 2013-12-18
I am in the process of creating an application using VBA that will send out messages via notes.  I can get it to do everything I want except display a hotspot URL link.  What I want to happen is that when a user receives the mail they will see for example "www.yahoo.com" as a hotspot and be able to click on it and bring them to the site.

Here is the code I have so far.  Thanks in advance.

Public Sub SendNotesMail(Subject As String, myrecipients As String, myccrecipients As String, mybcrecipients As String)

'Set up the objects required for Automation into lotus notes
    Dim Maildb As Object    'The mail database
    Dim UserName As String    'The current users notes name
    Dim MailDbName As String    'THe current users notes mail database name
    Dim MailDoc As Object    'The mail document itself
    Dim AttachME As Object    'The attachment richtextfile object
    Dim session As Object    'The notes session
    Dim EmbedObj As Object    'The embedded object (Attachment)
    Dim strBodyText As String
    Dim con As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmdtext As String
    Dim myreturnstring As String
    Dim hotspottext As String
    Dim rtitem As Object
   
    'Start a session to notes
    Set session = CreateObject("Notes.NotesSession")
   
    'Next line only works with 5.x and above. Replace password with your password
    'session.Initialize

    'Get the sessions username and then calculate the mail file name
    'You may or may not need this as for MailDBname with some systems you
    'can pass an empty string or using above password you can use other mailboxes.
    'UserName = Session.UserName
    'MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
   
    'MailDbName = "mail\jfdemul.nsf"
   
    'Open the mail database in notes
    Set Maildb = session.GetDatabase("", "")

    If Maildb.ISOPEN = True Then
        'Already open for mail
    Else
        Maildb.OPENMAIL
    End If
   
    'Set up the new mail document
    Set MailDoc = Maildb.CREATEDOCUMENT
    MailDoc.Form = "Memo"
    MailDoc.SendTo = myrecipients
    MailDoc.CopyTo = myccrecipients
    MailDoc.BlindCopyTo = mybcrecipients
    MailDoc.Subject = Subject
   
    strBodyText = "blah....."
    strBodyText = strBodyText & " blah....."
    strBodyText = strBodyText & " a free download at: " & vbCrLf
   
    MailDoc.Body = strBodyText
   
    '*************** Here is where I want to add the URL
   'www.yahoo.com for example

   
    'Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
    'Set EmbedObj = AttachME.embedobject(1454, "", GetSnapShotViewerDownloadPageURL(setconnection), "HotSpot")
    'MailDoc.CREATERICHTEXTITEM ("hotspot")
       
    MailDoc.SaveMessageOnSend = True
   
    'Place Attachments
    cmdtext = "SELECT"
    cmdtext = cmdtext & " TempAttachment.AttachmentName"
    cmdtext = cmdtext & " FROM"
    cmdtext = cmdtext & " TempAttachment"
    cmdtext = cmdtext & " WHERE (((TempAttachment.AttachmentName) Is Not Null))"
    cmdtext = cmdtext & " ORDER BY"
    cmdtext = cmdtext & " TempAttachment.AttachmentName;"

    con = setlocaldbconnection
    con.Open
   
    rs.Open cmdtext, con
   
    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
       
        Do Until rs.EOF
            Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
            Set EmbedObj = AttachME.EmbedObject(1454, "", rs.Fields("AttachmentName"), "Attachment")
            MailDoc.CREATERICHTEXTITEM ("Attachment")
            rs.MoveNext
        Loop
       
    Else
        'Do Nothing
    End If
   
    rs.Close
    con.Close
   
    Set rs = Nothing
    Set con = Nothing
   
   
   
    'Set up the embedded object and attachment and attach it
    If myfile <> "" Then
        Set AttachME = rtitem.EmbedObject(1454, "", myfile)
            If UBound(myfiles) > -1 Then
                For x = 0 To UBound(myfiles)
                    Set AttachME = rtitem.EmbedObject(1454, "", myfiles(x))
                Next x
            End If
    End If
    'Send the document
    MailDoc.PostedDate = Now()    'Gets the mail to appear in the sent items folder
    MailDoc.Send (False)
    'Clean Up
    Set Maildb = Nothing
    Set MailDoc = Nothing
    Set AttachME = Nothing
    Set session = Nothing
    Set EmbedObj = Nothing
End Sub
0
Comment
Question by:jeffreydemulling
4 Comments
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 65 total points
ID: 10670136
Just insert http://urlstring and it will activate http link.

~Hemanth
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 65 total points
ID: 10670202
I answered a similar question a while back... it isn't a VB problem, it is a limitation of the Notes object model, which provides no direct method for scripting a hotspot URL.

You either have to use an API of some sort (Notes API, Midas), which don't work in VBA, or you have to "trick" Notes into creating a hotspot for you, and append that hotspot to the message.

The basic idea:
Create a form, name it: (GEN_URL_HOTSPOT)
Create -> Computed Text.  The formula for the computed text shoudl be: LINKTEXT
Select the computed text on the form.  Create -> Hotspot -> URL
Click the @ button in the hotspot properties.  The formula should be LINKURL

Now, the code to make use of this...
Drop MailDoc.Body = ... that creates plain text, not rich text.

Set body mailDoc.createRichTextItem ( "Body" )
body.appendText strBodyText
Set URLDoc = MailDB.createDocument
URLDoc.replaceItemValue "Form" , "(GEN_URL_HOTSPOT)"
URLDoc.replaceItemValue "LinkText" , "www.yahoo.com"
URLDoc.replaceItemValue "LinkURL" , "http://www.yahoo.com/"
URLDoc.renderToRTItem body

By the way, you shoudl attach the file into the body field as well, instead of attaching it to your "made-up" field named "Attachment"
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

705 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

21 Experts available now in Live!

Get 1:1 Help Now