如何查找并连接文本字符串？适配新邮件格式的Outlook宏改造需求

Got it, let's adjust your Outlook macro to handle the new comma-separated email format. Here's how to tackle this step by step:

Core Approach #

Instead of hunting for individual "Name:" or "E-post:" labels, we'll first target the line starting with Customer info:, split that line into comma-separated segments, then extract and format the exact fields we need for Excel.

Modified Macro Code #

Replace your original two If blocks with this updated code:

' Look for the line containing customer info
If InStr(1, vText(i), "Customer info: ") > 0 Then
    ' Isolate the comma-separated data after the "Customer info: " label
    Dim customerData As String
    customerData = Split(vText(i), "Customer info: ")(1)
    
    ' Split the data into individual fields using commas as delimiters
    Dim customerFields As Variant
    customerFields = Split(customerData, ",")
    
    ' Combine first and last name (trim extra spaces, replace comma with space)
    Dim fullName As String
    fullName = Trim(customerFields(0)) & " " & Trim(customerFields(1))
    x1Sheet.Range("A" & lastrow) = fullName
    
    ' Extract the email address (it's the 5th field, index 4 since VBA arrays start at 0)
    Dim customerEmail As String
    customerEmail = Trim(customerFields(4))
    x1Sheet.Range("D" & lastrow) = customerEmail
End If

Key Details Breakdown #

• Locate the Customer Line: We use InStr to spot the line with Customer info:, then split off the label to get only the comma-separated details.
• Split into Fields: Split(customerData, ",") turns the string into an array where each element represents one piece of customer information.
• Format Full Name: We take the first two array elements (first name and family name), clean up any extra spaces with Trim, then join them with a space to match your Excel A列 requirement.
• Extract Email: Based on your new email format, the email sits at the 5th position in the comma-separated list, which translates to index 4 (VBA uses 0-indexed arrays).

Quick Notes #

• If the order of fields in the new emails ever changes, just adjust the array index numbers (e.g., if email moves to the 4th position, use customerFields(3) instead).
• The Trim function ensures we eliminate any leading/trailing spaces that might come right after the commas in the email text.

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