VBA SendObject and Hyperlinks

[Knicks]

I am trying to get a hyperlink in an email in VBA using the SendObject command. I would like to use a variable as the hyperlink in the body of the message that will change each time it is run. The hyperlink type is toward a network folder that will change depending upon the data. I've tried adding "#" to the beginning and end of the string but that didn't work. I've also played around with the "<a href", but I'm not sure if that is right or if I am not including "......" quotes to the whole data element. I've also tried using the hyperlink data type and that created an error (the other techniques have just entered the string without the hyper portion)

Any help would be appreciated,
Thank you
Charles

Dim txtMonth as string
Dim strTemp2 as string

strTemp2 = O:\reports\November\Data
DoCmd.SendObject , , , "Sam Smith", , , "CAP Data Cleanup: " & txtMonth, "Please open your Excel Spreadsheet to review records and fix in Service Point" & vbCrLf & vbCrLf & strTemp2 & vbCrLf & strTempALL, False

 

[Knicks]

I solved this problem. From what I can see it is difficult if not impossible to use SendObject and utilize hyperlinks. This can be achieved by using the Outlook object. Buyer beware though because the avenue to achieve this is through HTML the usual coding like VbCrLF for line breaks don't work and you will need to use HTML coding to achieve formatting. See below

Set OutApp = CreateObject("outlook.application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
With OutMail
.To = "Sam Smith"
.CC = ""
.Subject = "CAP Data Cleanup: " & txtMonth
.Attachments.Add ("O:\Programs\Support Services\Reports\RW\CAREWare\Data Elements\ServicePointServiceMap.xlsx")
.HTMLBody = "For Mismapped Errors Review Attached Spreadsheet" & "<p><b>" & "Please open your Excel Spreadsheet to review records and fix in Service Point" & "</b></p>" & "<p>" & "<a href=""" & "O:\\Programs\Support Services\Reports\Data Quality\" & txtMonth & """>" & "O:\\Programs\Support Services\Reports\Data Quality\" & txtMonth & "</a > " & "</p>" & "<p> """ & strTempALL & "</p>"""
.Display
End With
Set OutMail = Nothing
Set OutApp = Nothing

As you can see the biggest hassles are dealing with the text delimeters (an Access tedious spot)and the added label portion of the hyperlink which makes it look like you have to do it twice. <p></p> is used for line breaks. I hope this helps anyone trying to embed hyperlinks in emails - Oddly very little on the network on this subject.