如何从Outlook邮件指定LDAP语句提取ID以完善Outlook到Excel数据导入代码

Got it, let's nail this ID extraction part for your existing Outlook-to-Excel import code. Here's how to modify your import_code sub to pull the exact ID (like SVCLMCH) from that LDAP-style string:

Sub import_code()
    Dim O As Outlook.Application
    Dim ONS As Outlook.Namespace
    Dim OMAIL As Outlook.MailItem
    Dim ldapString As String
    Dim startPos As Integer
    Dim endPos As Integer
    Dim extractedID As String
    ' Add your existing Excel variables here (e.g., ws As Worksheet, rowNum As Long)
    
    Set O = New Outlook.Application
    Set ONS = O.GetNamespace("MAPI")
    
    ' Adjust this loop to match how you're currently iterating through emails
    For Each OMAIL In ONS.GetDefaultFolder(olFolderInbox).Items
        ' Replace this line with how you actually retrieve the LDAP string from the email body
        ldapString = "cn=SVCLMCH,OU=Users,OU=CX,DC=dm001,DC=corp,DC=dcsa,DC=com"
        
        ' Core logic to extract the ID between "cn=" and ",OU=Users"
        startPos = InStr(1, ldapString, "cn=") + Len("cn=")
        endPos = InStr(startPos, ldapString, ",OU=Users")
        
        ' Validate the markers exist before extracting
        If startPos > Len("cn=") And endPos > startPos Then
            extractedID = Mid(ldapString, startPos, endPos - startPos)
        Else
            extractedID = "ID not found" ' Fallback for invalid strings
        End If
        
        ' Integrate this with your existing Excel writing code (example below)
        ' ws.Cells(rowNum, 2).Value = extractedID ' Update column number as needed
        ' rowNum = rowNum + 1
    Next OMAIL
    
    ' Clean up objects to avoid memory leaks
    Set OMAIL = Nothing
    Set ONS = Nothing
    Set O = Nothing
End Sub

Key Details Breakdown: #

• InStr Function: Locates the starting index of cn= first, then finds the position of ,OU=Users starting from that point to avoid false matches.
• Mid Function: Grabs the substring between the two calculated positions to isolate your ID.
• Error Handling: The If check ensures we don't try to extract text if either marker is missing, preventing runtime errors and returning a clear fallback value.

Just swap the placeholder ldapString assignment with your existing code that pulls this string from the email body, and plug extractedID into wherever you're writing data to Excel.

内容的提问来源于stack exchange，提问作者Christopher Leandro Kwok