发送电子邮件收件人列表Excel [英] Send Email To Recipient List Excel

问题描述

我想在运行报告时自动从Excel发送，但是我需要它在客户端名称上执行VLOOKUP，并选择分配给该客户端的所有电子邮件地址。您可以帮助吗？

I want to send an automatically from Excel as it is running reports, but I need it to do a VLOOKUP on the client name and select all email addresses that are assigned to that client. Can you help please?

所以我将在一张名为的客户邮箱中的桌子上显示如下表格。 / p>

So I will have a table such as below, on a sheet named Client Emails

 Company 1 | [e-mail]
 Company 1 | [e-mail]
 Company 2 | [e-mail]
 Company 3 | [e-mail]
 Company 1 | [e-mail]

，以便更轻松地保持最新。现在我有下面的代码正确地发送电子邮件，但是我希望从工作簿中拉取地址而不是代码，因为这样更容易更新。

to make it easier to keep up to date. Right now I have the below code which sends the email correctly, but I would like it to pull the addresses from workbook and not the code as it is easier to update this way.

 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)
 With OutMail
    .To = "[e-mail]; [e-mail]"
    .CC = ""
    .BCC = ""
    .Subject = "Subject"
    .Body = "Hello World."
    .Attachments.Add ("Attachment")
    '.Display
    .Send
 End With
 On Error GoTo 0
 Set OutMail = Nothing
 Set OutApp = Nothing

推荐答案

您可以设置循环查看电子邮件地址表，并将匹配公司的电子邮件连接成一个字符串变量，然后用于to部分。

You could set up a loop to look through the table of email address and concatenate the emails of matching companies into a string variable which you then use for the "to" section.

例如（插入在你的陈述之前）：

For example (inserted prior to your with statement):

Dim Lastrow as long
dim myemail as string
dim myrange as Range


'counts the number of rows in use
lastrow = Sheets("Client Emails").Cells(Rows.Count, 1).End(xlUp).Row

    For Each myrange In Sheets("Client Emails").Range("A2:A" & lastrow)

        If myrange = "Company1" then

            myEmail = myEmail & myrange.offset(0,1).value & ";"

        End if

       Next Myrange

你将替换上述Company1作为您当前通过电子邮件发送的公司的名称。

You would replace "Company1" in the above to be the name of the company you were currently emailing.

您现有的代码中替换：

.To = Email1, Email2,email3, .........

与

.To = myEmail

这篇关于发送电子邮件收件人列表Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！