如何用Excel VBA或邮件合并自动发送对应行数据至指定邮箱？

Absolutely! Both Excel VBA and Mail Merge can automate this workflow—let’s walk through each method so you can choose what fits your needs best.

This method is perfect if you want full control over how the email looks, or if you need to add extra logic (like skipping rows with empty emails).

Step-by-Step Setup: #

1. Open your Excel file, then go to the Developer tab (if you don’t see it, enable it via File > Options > Customize Ribbon).
2. Click Visual Basic to open the VBA editor, right-click your workbook in the Project Explorer, and select Insert > Module.
3. Paste the following code into the module:

Sub SendRowDataToEmail()
    Dim OutApp As Object
    Dim OutMail As Object
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim headerText As String
    Dim rowData As String
    
    ' Set the worksheet (change "Sheet1" to your actual sheet name)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Create Outlook object
    Set OutApp = CreateObject("Outlook.Application")
    
    ' Loop through each row starting from row 2 (skip header row)
    For i = 2 To lastRow
        ' Skip if email cell is empty
        If ws.Cells(i, "D").Value = "" Then GoTo NextRow
        
        ' Get header text (A1-C1)
        headerText = ws.Cells(1, "A").Value & vbTab & ws.Cells(1, "B").Value & vbTab & ws.Cells(1, "C").Value & vbNewLine
        
        ' Get row data (Ai-Ci)
        rowData = ws.Cells(i, "A").Value & vbTab & ws.Cells(i, "B").Value & vbTab & ws.Cells(i, "C").Value
        
        ' Create a new email
        Set OutMail = OutApp.CreateItem(0)
        With OutMail
            .To = ws.Cells(i, "D").Value
            .Subject = "Your Requested Data"
            .Body = "Here's the data you requested:" & vbNewLine & vbNewLine & headerText & rowData
            ' Uncomment below to send immediately; keep commented to preview first
            '.Send
            .Display ' Use this to preview emails before sending
        End With
        
        ' Clean up
        Set OutMail = Nothing
NextRow:
    Next i
    
    Set OutApp = Nothing
    MsgBox "Emails processed successfully!", vbInformation
End Sub

Key Notes: #

• Replace "Sheet1" with your actual worksheet name.
• The code uses vbTab to separate columns (you can change this to commas or spaces if preferred).
• Use .Display to preview emails first, then switch to .Send once you’re ready to send them automatically.
• Make sure Outlook is open when running the code (it uses Outlook to send emails).

If you prefer a point-and-click approach without writing code, Mail Merge in Word is ideal for this task.

Step-by-Step Setup: #

1. Prepare your Excel data: Make sure your table has headers in A1-C1 and emails in D1 (the first row should be headers, no empty rows between data).
2. Open a new Word document, go to the Mailings tab.
3. Click Start Mail Merge > E-mail Messages.
4. Click Select Recipients > Use an Existing List, then choose your Excel file and select the correct worksheet.
5. Design your email content:
   • Type a greeting or intro text (e.g., "Hi, here's your requested data:").
   • To insert the header and row data, click Insert Merge Field and add each field (A, B, C) in order. You can add tabs or spaces between them to format neatly.
   • For example:

     <<A>> <<B>> <<C>>

6. Click Preview Results to check how each email will look with the row data.
7. When ready, click Finish & Merge > Send E-mail Messages.
   • In the pop-up, select "To" as your email field (D column), enter a subject line, and choose to send all emails.

Key Notes: #

• Mail Merge uses your default email client (like Outlook) to send messages.
• You can customize the email format (add bold, colors, or tables) in Word before merging.
• It’s great for quick, one-time tasks where you don’t need complex logic.

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