Problem with VBA email code (1 Viewer)


New member
Local time
Today, 22:28
May 30, 2011
Hi there

I have some code that creates an html email and it works fine.

However, I have just tried to add a second line that includes a link to a web page. How do I make the web address "clickable"? Whenever I try an insert html tags, I get a compile error.

Private Sub Command0_Click()

Dim rst As Recordset
Dim dbs As Database
Dim strSql As String
Dim stEmail As String
Dim stDate As String
Dim stPath As String
Dim stSubject As String
Dim stBody As String
Dim OLApp As New Outlook.Application
Dim OLMsg As Outlook.MailItem
Dim oShell As Object
Dim olkApp
Dim SecurityManager As Object
Dim salName As String
Dim salMessage As String
Dim salFooter As String
Dim salPhone As String
Dim salCell As String
Dim salFax As String

On Error Resume Next
Set olkApp = GetObject(, "Outlook.Application") 'Gets Outlook if open
If Err.Number > 0 Then
Set oShell = CreateObject("WScript.Shell") 'Opens instance of Outlook
oShell.Run "outlook" 'Outlook is not open so open it
'Outlook is already open
End If

Set olkApp = Nothing

Set SecurityManager = CreateObject("AddInExpress.OutlookSecurityManager")
SecurityManager.DisableSMAPIWarnings = True

DoCmd.SetWarnings False

frmCreatingEmails.Visible = True

DoCmd.OpenQuery "qryMakeEmailTable", acViewNormal, acEdit
DoCmd.Close acQuery, "qryMakeEmailTable", acSaveYes

strSql = "SELECT DISTINCT ClientName, EmailAddress, Filepath, InvoiceEndDate FROM tblEmail"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSql)

If rst.RecordCount = 0 Then
frmCreatingEmails.Visible = False
MsgBox "Nothing to send, please check the dates", vbOKOnly, "Warning"



Do While Not rst.EOF

stName = rst.Fields("ClientName").Value
stPath = rst.Fields("Filepath").Value
stDate = rst.Fields("InvoiceEndDate").Value
stEmail = rst.Fields("EmailAddress").Value

salMessage = Me.SalutationMessage
salName = Me.SalutationName
salFooter = Me.SalutationFooter
salCell = Me.Cell
salPhone = Me.Phone
salFax = Me.Fax
stSubject = "The Buying Group Fuelcard Invoice for month ending " & stDate

HTML = "<html><body><font face = Tahoma size = 2>"

HTML = HTML & "<p>Hi there</p>"
HTML = HTML & "<p>Please find attached your Fuelcard invoice for the month ending" & " " & stDate & "</p>"
HTML = HTML & "<p>We have also updated out Terms of Trade. To get a copy of them, please visit the following page by copy-and-pasting this link into your browser: link_address</p>"
HTML = HTML & "<p>Kind regards<br>" & salName & "<br>The Buying Group Limited<br>Phone:" & " " & salPhone & "<br>Fax:" & " " & salFax & "</p>"
HTML = HTML & "<p><font color = #c60018><b>" & salMessage & "</b></font></p>"
HTML = HTML & "<p><font size = 1>" & salFooter & "</font></p>"
HTML = HTML & "</font></body></html>"

stBody = HTML

Thanks for your help.


New member
Local time
Today, 22:28
May 30, 2011
Brilliant, thanks for the advice darbid.:)

Users who are viewing this thread

Top Bottom