Hello,

I wrote a macro that calls a query, and sends out an customized email to each person in a separate query based on certain criteria, using the CDO object. The problem that is happening is that an exclamation point "!" is being inserted into random places in the email message. This becomes a big problem if the exclamation point is inserted as part of a dynamic a href tag I create because the link will no longer go to the correct place. I have tried debugging but cannot figure it out. The latest place it inserted itself was the line that says "Note: This email is gener! ated eve ry Monday", this is exactly how it appeared, it seemed to have inserted some spaces too. To make it even more strange is that this problem did not appear on every email sent, only on some random people. I even tried to use the Replace() function to search for an exclamation point upon creating my variables but it still appeared. I have provided my code that queries the database and creates the email content:


QPTRecordSet.ActiveConnection = myConnection 'set recordset var

'Select actions for each user in USER QUERY
'QPT List
qptSQL = "SELECT [Issue ID], [Type], [What Action Is Required], [Commit Date] FROM getActionsDueThisWeek WHERE getActionsDueThisWeek.[Assignee]='" & UserName & "' AND getActionsDueThisWeek.[Type]='QPT' ORDER BY [Issue ID];"


QPTRecordSet.Open qptSQL, , adOpenStatic

'get the record count
QPTRecordSetCount = QPTRecordSet.RecordCount

If QPTRecordSetCount > 0 Then
'set list name of email
QPTHeader = "<font color=blue><b>QPT</b></font></br>"
End If

While Not QPTRecordSet.EOF
QPTBody = QPTBody & "<font size=-1><b>(ID:" & QPTRecordSet.Fields("Issue ID").Value & ")</b> - """ & "<a href=https://myServer/sites/wtbu/TISD/pmo/process/Lists/QPT%20Action%20Items/DispForm.aspx?ID=" & QPTRecordSet.Fields("Issue ID").Value & ">" & QPTRecordSet.Fields("What Action Is Required").Value & """</a> Due: " & FormatDateTime(QPTRecordSet.Fields("Commit Date"), vbLongDate) & "</font><br><br>"
QPTRecordSet.MoveNext
Wend
'**********************************************************************
'REPEAT END
'**********************************************************************
EmailBody = QPTHeader & QPTBody

Set iMsg = CreateObject("CDO.Message")
With iMsg
Set .Configuration = iConf
.To = UserEmail
.FROM = "[email protected]"
.Subject = "Action Items Summary: " & Date
.HTMLBody = "<font face=helvetica, arial>**THIS IS A SYSTEM GENERATED EMAIL. PLEASE DO NOT REPLY**<br><br>" & Intro & EmailBody & "<br><br><i><b>Note: This email is generated every Monday.</b></i><br><br>If you have any questions or comments, please contact: <a href=mailto:[email protected]>John Smith</a> or submit a <a href=https://help/arsys/apps/en/starbase/ITSDS/login.jsp>STARS ticket</a></font>"
.Send
End With
Set iMsg = Nothing

++++++++++++++++++++++++++++++++++++++++++++++++
thank you for your help,

SR