vba 使用 If 语句和查找发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26655348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Send Email using If Statements and lookups
提问by user2965077
I am trying to send an email to a list of recipients when their list is triggered. I have lists of about 10 names in columns A:K of a sheet named "Email List".
我试图在收件人列表被触发时向他们发送电子邮件。我在名为“电子邮件列表”的工作表的 A:K 列中列出了大约 10 个姓名。
I have another sheet called "Info Table". This sheet contains a table that includes a column where countries are located.
我有另一张名为“信息表”的表格。此工作表包含一个表格，其中包含一列国家/地区所在的列。
I am looking to add code that will do the following: When a country in the "Info Table" (Column C) matches a country in the "Email List" (row 2, columns A:K), an email will be sent to the email addresses listed in rows 3-13 for that country. The email at this stage can be generic.
我希望添加将执行以下操作的代码：当“信息表”（C 列）中的国家/地区与“电子邮件列表”（第 2 行，A:K 列）中的国家/地区匹配时，将向第 3-13 行中列出的该国家/地区的电子邮件地址。此阶段的电子邮件可以是通用的。
Sub Send_Email()
Dim Email_Subject, Email_Send_From, Email_Body As String, i As Integer
Dim Mail_Object, nameList As String, o As Variant
Email_Send_From = ""
For i = 3 To 13 'use cells 3 to 13 in column "A" where names are stored
If Sheets("Email List").Range("A3").Value <> "" Then
nameList = nameList & ";" & Sheets("Email List").Range("A" & i).Value
End If
Next
Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(o)
.Subject = ""
.To = nameList
.Cc = ""
.Body = "I am testing a new VBA, sorry if you received this message in error." & vbNewLine & vbNewLine & _
"Best Regards," & vbNewLine & _
""
.display
End With
Application.DisplayAlerts = False
End Sub
回答by David Zemens
Yes, you can use the Application.VLookupfunction which takes the same sort of arguments as the worksheet function of same name.
是的，您可以使用与Application.VLookup同名工作表函数采用相同类型参数的函数。
Dim myValue as Variant
'Modify the function to use YOUR arguments
myValue = Application.Vlookup(value_to_lookup, range_to_search, column_number, exact_match)
if IsError(myValue) Then
MsgBox "Not found!", vbInformation
Exit Sub
End If
Please clarify what this means and it should be easy to help revise your code:
请澄清这意味着什么，帮助修改代码应该很容易：
When a country in the "Info Table" (Column C) matches a country in the "Email List" (row 2, columns A:K), an email will be sent to the emails listed in rows 3-13 for that country.
当“信息表”（C 列）中的国家/地区与“电子邮件列表”（第 2 行，A:K 列）中的国家/地区匹配时，将向第 3-13 行中列出的该国家/地区的电子邮件发送一封电子邮件。
Specifically: what is going to trigger the Send_Emailprocedure? How will you call it? Does it need to be in a loop searching against allvalues in Info Table (column C)? Etc.
具体来说：什么将触发该Send_Email程序？你会怎么称呼它？是否需要循环搜索信息表（C 列）中的所有值？等等。
In the meantime, let's improve your code a bit. VBA does notsupport implicit type declarations, each variable must be Dimwith it's own As {type}, otherwise it will be treated as Variant. Best practice is to avoid variant in favor of strongly-typed variables whenever possible.
同时，让我们稍微改进一下您的代码。VBA并没有支持隐式类型声明，每个变量必须Dim有它自己的As {type}，否则将被处理Variant。最佳实践是尽可能避免使用强类型变量的变体。
You can also avoid the For i = 3 to 13loop and assign to nameListin a single statement.
您还可以避免For i = 3 to 13循环并nameList在单个语句中赋值。
Then, I would just call this procedure in a loop over Column C, using the Matchfunction to check if the country exists on the Email List worksheet.
然后，我将在 C 列上循环调用此过程，使用该Match函数检查电子邮件列表工作表中是否存在该国家/地区。
Sub foo()
Dim c as Range
Dim match as Integer
With Sheets("Info Table")
For each c in .Range("C1:C10").Cells ''Modify this as needed
match = 0
On Error Resume Next
match = IfError( _
Application.Match( _
c.Value, Sheets("Email List").Range("A2:K2"), False)
)
On Error GoTo 0
If Not match = 0 Then Call Send_Email(match)
Next
End Sub
If it exists, then you send the result of the match function to the Send_Emailprocedure, and define the range using matchto indicate the columnwhich contains the email list for that particular country:
如果存在，则将匹配函数的结果发送到该Send_Email过程，并使用match来定义范围以指示包含该特定国家/地区的电子邮件列表的列：
Sub Send_Email(match as Integer)
Dim Email_Subject As String, Email_Send_From As String, Email_Body As String, i As Integer
Dim Mail_Object as Object, nameList As String, o As Variant
Email_Send_From = ""
If Sheets("Email List").Cells(match, 3).Value <> "" Then
nameList = Join(Application.Transpose(Sheets("Email List").Range("A3:A13").Offset(,match-1).Value, ";")
End If
Next
Set Mail_Object = CreateObject("Outlook.Application")
With Mail_Object.CreateItem(o)
.Subject = ""
.To = nameList
.Cc = ""
.Body = "I am testing a new VBA, sorry if you received this message in error." & vbNewLine & vbNewLine & _
"Best Regards," & vbNewLine & _
""
.display
End With
Application.DisplayAlerts = False
End Sub
