Outlook VBA保存邮件正文至SQL异常问题及字符串疑问求助

问题背景 #

在Outlook VBA中尝试将邮件正文（截取前500字符）保存到SQL表时，少数邮件会在赋值阶段触发错误，该错误发生在表更新步骤之前。无法提供异常的Outlook.EmailItem样本，询问是否有开发者遇到过同类问题。

临时解决方案与疑问 #

经排查，异常由特殊字符串引发。编写了BuildNewString函数，通过字符的ASCII值重建字符串（仅处理前500字符）解决了问题，但不清楚该函数生效的原因。

Function BuildNewString(strInput As String)
Dim strOUT As String
Dim pos1 As Long
    For pos1 = 1 To Len(strInput)
        strOUT = strOUT & Chr$(Asc(Mid$(strInput, pos1, 1)))
    Next
    BuildNewString = strOUT
End Function

关联的字符串比较异常 #

另外遇到一个通用问题：VBA中部分字符串直接比较时不相等，但按字符ASCII值比较却一致？

通过扫描代码发现约5%的邮件存在该异常，异常字符的ASCII值为63或32，MailItem.Body与MailItem.HTMLBody均存在此情况（仅异常位置不同）。

分析代码如下：

Sub ScanBodytext()
Dim olNS As Outlook.NameSpace
Dim olFolder As MAPIFolder
Dim olMail As Outlook.MailItem
Dim strBody As String
    Set olNS = Application.GetNamespace("MAPI")
    Set olFolder = olNS.PickFolder
    For Each olMail In olFolder.Items
        strBody = Left(olMail.Body, 1000)
        If DetectAnomaly(strBody) Then
            MsgBox "difference in mail d.d. " & olMail.ReceivedTime & vbCrLf & "from: " & olMail.SenderEmailAddress & vbCrLf & "subject: " & olMail.Subject
        End If
    Next
    MsgBox "All mails in folder were scanned."
End Sub

Function DetectAnomaly(strInput As String) As Boolean
Dim pos1 As Long
Dim strChar As String
Dim intCharVal As Integer
    For pos1 = 1 To Len(strInput)
        strChar = Mid$(strInput, pos1, 1)
        intCharVal = Asc(strChar)
        If strChar <> Chr(intCharVal) Then
            MsgBox "anomaly at " & CStr(pos1) & ", char: '" & strChar & ", ascii: " & CStr(intCharVal)
            DetectAnomaly = True
        End If
    Next
End Function

不确定所有异常字符串都会触发错误，但BuildNewString函数可解决所有问题，说明异常与错误直接相关。

内容的提问来源于stack exchange，提问作者Hans Bloemendal