Send Email programmatically text containing hyperlinks from Access

G

Guest

I would like to send Email text 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
 
G

Guest

freakazeud said:
You might find this discussion helpful:
http://www.utteraccess.com/forums/s...=m&olderval=&oldertype=#Post1164740&bodyprev=
Click to expand...

The discusstion that you suggested was very helpful. It solved my question.
Thank you very much!

The following is the code that I wrote for the subroutine:

Sub CreateEmailItem(ByVal subjectEmail As String, _
ByVal toEmail As String, ByVal strHyperlink As String)

Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim strHTML As String
Dim strExportDirectory As String

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)

strHTML = "<HTML><Body>Test Body.<p><p><a Href=" & strHyperlink & "
target=new>Testing Link</a></body></html>"
'The Testing Link is the message that appears in blue to be clicked.

With objOutlookMsg
' Set the Subject, Body, and Importance of the message.
.BodyFormat = olFormatHTML
'This converts the message to HTML for the hyperlink to be inserted
.HTMLBody = strHTML
.Subject = subjectEmail
.To = toEmail
.Importance = olImportanceNormal 'Normal importance
.Send
End With
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Programmatically send Email text containing hyperlinks from Access 2
Sending email via vba code(question) 5
Want to merge access with word; what is wrong with this code? 2
email from Access 1
sending email message with Access user defined type error 1
Adding hyperlink to access database from excel 1
Send a Delivery Status Notification - from access 1
Email Hyperlinks to workbooks! 1

Top