如何从Outlook邮件正文中提取Invoice后的Long类型编号

实现思路 #

1. 用Split函数将邮件正文按固定短语"Invoice: "分割，获取短语后的内容段
2. 从该内容段中提取连续数字字符，剔除后续非数字内容
3. 将纯数字字符串转换为Long类型

VBA代码示例 #

Sub ExtractInvoiceNumber()
    Dim targetMail As MailItem
    Dim mailBody As String
    Dim splitParts As Variant
    Dim rawNumberSection As String
    Dim invoiceNumStr As String
    Dim charIndex As Integer
    
    ' 假设已获取目标邮件对象targetMail
    mailBody = targetMail.Body
    
    ' 按"Invoice: "分割正文内容
    splitParts = Split(mailBody, "Invoice: ")
    
    ' 检查是否存在目标短语
    If UBound(splitParts) >= 1 Then
        rawNumberSection = splitParts(1)
        invoiceNumStr = ""
        
        ' 提取连续数字部分
        For charIndex = 1 To Len(rawNumberSection)
            If IsNumeric(Mid(rawNumberSection, charIndex, 1)) Then
                invoiceNumStr = invoiceNumStr & Mid(rawNumberSection, charIndex, 1)
            Else
                Exit For ' 遇到非数字字符立即停止
            End If
        Next charIndex
        
        ' 转换为Long类型并处理
        If invoiceNumStr <> "" Then
            Dim longInvoiceNum As Long
            longInvoiceNum = CLng(invoiceNumStr)
            ' 此处可添加编号的后续使用逻辑，如写入单元格或变量
            Debug.Print "提取到的Invoice编号(Long类型): " & longInvoiceNum
        Else
            Debug.Print "未找到有效Invoice编号"
        End If
    Else
        Debug.Print "邮件正文中未发现""Invoice: ""短语"
    End If
End Sub

关键说明 #

• Split函数拆分后，splitParts(1)对应"Invoice: "之后的所有内容，确保我们定位到编号起始位置
• 循环提取数字的逻辑，避免了后续非数字内容干扰，保证只获取纯编号
• 若编号超出Long类型范围（-2147483648 至 2147483647），可替换CLng为CLngLng，转换为LongLong类型

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