Programmatically send Email text containing hyperlinks from Access

[momotaro]

I would like to send Email test containing hyperlinks programmatically from
Access using a table that contains each mail receipant's information with
some hyperlink fields.
I compose a message body combining the contents of the fields in the table for
each receipant (record). A testing programs worked fine when the contents
only includes "string" data. However, I would ultimately like to contain the
contents of the hyperlinnk fields of the filed. The following codes failed
with Run-Time error '91': Object variable or with variable not set - at the
line for assignment of the content of hyperlink field to hyperlink type
variable. How can I make message body contains both text strings and
hyperlinks?

Private Sub SendEmail()

Dim subjectEmail As String
Dim bodyEmail As String
Dim toEmail As String

Dim db As Database
Dim rst As DAO.Recordset
Dim icount As Integer
Dim lActual As Hyperlink

subjectEmail = "Monthly Resource Management Report(MR2)"
bodyEmail = "Please check the link "

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblSupervisor")

With rst
If .RecordCount > 0 Then
icount = .RecordCount
Debug.Print icount ' # of records in tblSupervisor
' move to the first
.MoveFirst
For i = 1 To icount
Debug.Print ![Supervisor]
toEmail = ![Email_Name]
lActual = ![Link_Actual_Collection] <------- error
occurred at this line
bodyEmail = bodyEmail & lActual
Call CreateEmailItem(subjectEmail, _
toEmail, bodyEmail)
End If
.MoveNext
Next i
.Close

End If
End With
End Sub

 

[Dave Kane [MVP - Outlook]]

According to the object browser it looks like an Access Hyperlink is an
object. In that case you would need a Set statement to assign it (Set
lActual = ![Link_Actual_Collection]), which is probably the source of your
error. But that may not be very useful anyway. According to this article
http://www.asp101.com/tips/index.asp?id=100 a hyperlink field is really a
specialized text field that combines the Display Text, Address and
SubAddress separated by # symbols. Outlook won't understand or display a
string like that as a hyperlink without some help. You will need to parse
the string in the hyperlink field and convert it to an HTML hyperlink. Try
this
Dim strLink as String
Dim astrLink() as String
Dim strDisplay as String
Dim strAddress as String

'split the string at the # delimiters and drop in an array
astrLink=Split(![Link_Actual_Collection], "#")
'Display Text may be blank
If Len(astrLink(0))>0 Then
strDisplay = astrLink(0)
Else
'use the address as the display
strDisplay = astrLink(1)
End If
'Sub Address may be blank
If Len(astrLink(2))>0 Then
strAddress = astrLink(1) & "#" & astrLink(2)
Else
strAddress = astrLink(1)
End If
'construct HTML hyperlink
strLink = "<a href='" & strAddress & "'>" & strDisplay & "</a>"

Append that link to your bodyEmail value. When you create the email in
CreateEmailItem you can assign that string to the HTMLBody property of your
MailItem.

I would like to send Email test containing hyperlinks programmatically from
Access using a table that contains each mail receipant's information with
some hyperlink fields.
I compose a message body combining the contents of the fields in the table
for
each receipant (record). A testing programs worked fine when the contents
only includes "string" data. However, I would ultimately like to contain
the
contents of the hyperlinnk fields of the filed. The following codes failed
with Run-Time error '91': Object variable or with variable not set - at
the
line for assignment of the content of hyperlink field to hyperlink type
variable. How can I make message body contains both text strings and
hyperlinks?

Private Sub SendEmail()

Dim subjectEmail As String
Dim bodyEmail As String
Dim toEmail As String

Dim db As Database
Dim rst As DAO.Recordset
Dim icount As Integer
Dim lActual As Hyperlink

subjectEmail = "Monthly Resource Management Report(MR2)"
bodyEmail = "Please check the link "

Set db = CurrentDb()
Set rst = db.OpenRecordset("tblSupervisor")

With rst
If .RecordCount > 0 Then
icount = .RecordCount
Debug.Print icount ' # of records in tblSupervisor
' move to the first
.MoveFirst
For i = 1 To icount
Debug.Print ![Supervisor]
toEmail = ![Email_Name]
lActual = ![Link_Actual_Collection] <------- error
occurred at this line
bodyEmail = bodyEmail & lActual
Call CreateEmailItem(subjectEmail, _
toEmail, bodyEmail)
End If
.MoveNext
Next i
.Close

End If
End With
End Sub

 

[momotaro]

Append that link to your bodyEmail value. When you create the email in
CreateEmailItem you can assign that string to the HTMLBody property of your
MailItem.

Thank you very much for your advice. I composed the HTML string containing
message and address and used the HTMLBody property of Mailitem. It solved my
question!