How to format URL links in OL Message Body

[jamescol]

StaticBob
In response to your PM (original post at http://www.mrexcel.com/board2/viewtopic.php?t=103330)

You should test which Body Format the user has configured. If the format is Plain Text, Rich Text, or Word, then your original .body property will work. If the format is HTML, then you need to wrap your URL in an <A> tag as shown in the sample below.

The <A> tag syntax is:
<A href=myURL>URL Text</A>

Where myURL is the actual HTTP:// link and URL Text is the text you want to display for the link.

Hope this helps!

James

[vba]
Public Sub sendemail(savepath, docname)
On Error GoTo errorlog
'Declarations
Dim appOutlook As Object
Dim mi As Object
Dim Created As Boolean
Dim safeitem As Object

'Generate mail item
Set myOlApp = CreateObject("Outlook.Application")
Set myNameSpace = myOlApp.GetNamespace("MAPI")
myNameSpace.Logon
Set safeitem = CreateObject("Redemption.SafeMailItem") 'Create an instance of Redemption.SafeMailItem
'Set myFolder = myNameSpace.GetDefaultFolder(olFolderOutbox)
Set mi = myOlApp.CreateItem(olMailItem)
safeitem.Item = mi
'mi.Display
If Created Then appOutlook.Quit


'Get the names for document type from given range
Select Case docname
Case "RFI"
Set rng = Worksheets("email").Range("P4:P25")
Case "CVI"
Set rng = Worksheets("email").Range("Q4:Q25")
Case "SI"
Set rng = Worksheets("email").Range("R4:R25")
Case "Call Off"
Set rng = Worksheets("email").Range("S4:S25")
Case "Requisition"
Set rng = Worksheets("email").Range("T4:T25")
Case "Notice"
Set rng = Worksheets("email").Range("U4:U25")
Case Else
MsgBox "No e-mail addresses"
Exit Sub
End Select

'Scan through the range and verify each name
For Each cell In rng.Cells
If cell.Value <> "" Then
Set Rcp = safeitem.Recipients.Add(cell.Value)
'If Not Rcp.Resolve Then
' Rcp.Delete
Else
End If
Next cell

'Get info and send mail


With safeitem.Item

.subject = Worksheets("directory").Range("B3").Value & " - Workbook Notification"


If safeitem.Item.BodyFormat = olFormatHTML Then
safeitem.Item.HTMLBody = "This message has been generated automatically." & vbNewLine & vbNewLine & _
"A new mail has been created in this location. Please click the link to view . . ." & vbNewLine & vbNewLine & _
?<A href=??? & savepath & ???>? & savepath & ?</A>?

Else

safeitem.Item.Body = "This message has been generated automatically." & vbNewLine & vbNewLine & _
"A new " & docname & " has been created in this location. Please click the link to view . . ." & vbNewLine & vbNewLine & _
"<" & savepath & ">"

End If


End With

safeitem.send

Set mi = Nothing
If Created Then appOutlook.Quit
Set appOutlook = Nothing

Exit Sub
[/vba]

[Staticbob]

Thanks James,

That will help loads mate, but I still have a potentail issue ! I am rolling this out to 300 or so users, i do not know and cannot control what Outlook format they have set.

Is it possible for me to check for this programatically, or even better to control the format of the message created in Excel ? Ideally, I want to send this in rich text.

Thanks again,
Bob

[jamescol]

Bob,
The sample code already checks for the user's default mail in the following line:
[vba]
If safeitem.Item.BodyFormat = olFormatHTML Then
[/vba]

Basically, you only need to determine if HTML is the EditorType being used. If it is, you need to use the <A> tag. If the EditorType is anything else, the format of your message stays the same - the way you originally posted your code.

If you want to force a RTF Editor, then use the following line just before you set the message body:

[vba]
safeitem.Item.BodyFormat = olFormatRichText
[/vba]

The constants for the other formats are:
olPlainText
olHTML

Keep in mind, I conducted my tests of this code without Redemption. I do not use Redemption due to the security vulnerabilities it introduces. Since you are using Redemptions's safeitem mail object, I cannot vouch that the formats will work as expected.

Also important - from the OL2003 Help file: (This statement is why I didn't suggest changing the format in the first place, since your original post stated you did not want to change an HTML user's signature. If your force RTF, you stand a good chance of changing any HTML signature.)

All text formatting will be lost when the BodyFormat property is switched from RTF to HTML and vice-versa.

In earlier versions of Outlook, the BodyFormat property returned the olFormatUnspecified constant for a newly created item that has not been displayed or whose BodyFormat property is not yet set programmatically. In Microsoft Office Outlook 2003, the property returns the format that is currently set in the Outlook user interface.

Let me know if this post clears up the confusion.

James