将特定文件附加到相应的收件人 [英] Attaching Specific files to corresponding recipients

问题描述

我有一长串的费用报告要发送给不同的收件人.

I have a long list of cost reports to be sent to different recipients.

我当时想我可以有一个带有地址和相应位置的Excel文件，即A1 [e-mail] A2 0001B1 [e-mail] B1 0002

I was thinking I could have one Excel file with addresses and corresponding Location i.e A1 [e-mail] A2 0001 B1 [e-mail] B1 0002

然后使用VBA在每一行(1)中循环，并在文件夹中搜索对应的(A2)命名文件，并将其附加到邮件发送到单元格(A1).

Then using VBA cycle through each row (1) and search a folder for the corresponding (A2) named file and attach it to mail out to cell (A1).

推荐答案

我假设您在第一行中具有标题.未经测试.

I assume you have headers in the first row. Untested.

Sub AntMan()

Dim OutLookApp As Object
Dim OutLookMailItem As Object
Dim lastRow As Long
Dim MailDest As String
Dim subj As String

lastRow = ThisWorkbook.WorkSheets("Sheet6").Cells(Rows.Count, "A").End(xlUp).Row 'change worksheet

For i = 2 To lastRow

    Set OutLookApp = CreateObject("Outlook.application")
    Set OutLookMailItem = OutLookApp.CreateItem(0)
    Set Attach = OutLookMailItem.Attachments

    With OutLookMailItem
        .To = Cells(i, 1).Value
        .SUBJECT = "Put your subject here"
        .Body = "Put your body here"
        Attach.Add "C:\your\file\path\here\" & Cells(i, 2).Value & ".xlsx"
        .Display 'for debugging
        .Send
    End With

Next

End Sub

这篇关于将特定文件附加到相应的收件人的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！