Outlook VBA导出邮件至osTicket时多附件JSON格式异常问题

你当前的问题是把所有附件都塞进了同一个Dictionary，导致生成的JSON里attachments数组只有一个对象，包含多个附件键值对；而osTicket要求的是数组里每个元素是独立的单附件对象。

修正思路 #

• 用Collection存储每个附件对应的独立Dictionary（每个附件对应一个仅包含自身键值对的字典）
• 循环中每次创建新字典，添加当前附件信息后加入Collection
• 最后将整个Collection转换为JSON数组

修正后的完整代码 #

1. 调整附件处理循环 #

Dim attachmentsColl As New Collection ' 存储每个附件的独立字典

If myMsg.attachments.Count > 0 Then
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    For i = 1 To myMsg.attachments.Count
        Set oFile = myMsg.attachments.Item(i)
        
        If oFile.Size <= MAX_ATTACHMENT Then
            sTmpFile = fs.GetTempName
            oFile.SaveAsFile sTmpFile
            
            n.dataType = "bin.base64"
            Set data = CreateObject("ADODB.Stream")
            data.Type = 1 'Binary
            data.Open
            data.LoadFromFile sTmpFile
            n.nodeTypedValue = data.Read
            
            n.Attributes.removeNamedItem "dt:dt"
            
            ' 每次循环创建新字典，仅存当前附件信息
            Dim singleAttm As New Dictionary
            singleAttm.Add oFile.FileName, "data:" & oFile.PropertyAccessor.GetProperty(PR_ATTACH_CONTENT_TYPE) & ";base64," & n.nodeTypedValue
            
            ' 将单附件字典加入Collection
            attachmentsColl.Add singleAttm
            
            Kill sTmpFile
            Set singleAttm = Nothing ' 释放对象
        End If
    Next
End If

2. 调整JSON生成代码 #

Dim Body As New Dictionary

Body.Add "alert", "true"
Body.Add "autorespond", "true"
Body.Add "source", "API"
Body.Add "name", myMsg.SenderName
Body.Add "email", FromAddress
Body.Add "subject", myMsg.Subject
Body.Add "topicId", CStr(rubriq)
Body.Add "message", "data:text/html," & strData 'myMsg.HTMLBody

' 将存储所有附件的Collection赋值给attachments字段
If attachmentsColl.Count > 0 Then
    Body.Add "attachments", attachmentsColl
End If

Dim json As String
json = JsonConverter.ConvertToJson(Body, Whitespace:=" ")

说明 #

VBA-JSON库会自动将Collection转换为JSON数组，数组中的每个Dictionary会转为独立的JSON对象，最终生成的格式将完全符合osTicket的要求。

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