Send Email programmatically text containing hyperlinks from Access

M

momotaro

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
 
M

momotaro

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
How to make eMail message body with text strings and hyperlink data 0
Sending email via vba code(question) 5
OutlookAddin Solution 5
issues with looping using Outlook for sending emails via Access 4
Error sending emails from Access through Outlook... 1
Problem using =HYPERLINK() formula rather than literal hyperlinks incells 13
Want to merge access with word; what is wrong with this code? 2

Top