如何使用 Outlook 在 Excel VBA 中向多个收件人发送电子邮件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14985261/
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
How can I use Outlook to send email to multiple recipients in Excel VBA
提问by user2092180
I am trying to set up several buttons on an Excel form to email different groups of people. I made several ranges of cells on a separate worksheet to list the separate email addresses. For example, I want "Button A" to open Outlook and put the list of email addresses from "Worksheet B: Cells D3-D6". Then all that has to be done is hit "Send" in Outlook.
我正在尝试在 Excel 表单上设置几个按钮来向不同的人群发送电子邮件。我在单独的工作表上制作了多个单元格范围以列出单独的电子邮件地址。例如，我希望“按钮 A”打开 Outlook 并放置“工作表 B：单元格 D3-D6”中的电子邮件地址列表。然后所有要做的就是在 Outlook 中点击“发送”。
Here is my VBA code so far, but I can't get it to work. Can someone tell me what I'm missing or doing wrong, please?
到目前为止，这是我的 VBA 代码，但我无法让它工作。有人可以告诉我我错过了什么或做错了什么吗？
VB:
VB：
Sub Mail_workbook_Outlook_1()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object
EmailTo = Worksheets("Selections").Range("D3:D6")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = EmailTo
.CC = "[email protected];[email protected]"
.BCC = ""
.Subject = "RMA #" & Worksheets("RMA").Range("E1")
.Body = "Attached to this email is RMA #" & Worksheets("RMA").Range("E1") & ". Please follow the instructions for your department included in this form."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
On Error Goto 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
回答by Siddharth Rout
You have to loop through every cell in the range "D3:D6"and construct your Tostring. Simply assigning it to a variant will not solve the purpose. EmailTobecomes an array if you assign the range directly to it. You can do this as well but then you will have to loop through the array to create your Tostring
您必须遍历范围内的每个单元格"D3:D6"并构建您的To字符串。简单地将它分配给一个变体并不能解决问题。EmailTo如果直接将范围分配给它，则成为数组。您也可以这样做，但随后您将不得不遍历数组以创建您的To字符串
Is this what you are trying? (TRIED AND TESTED)
这是你正在尝试的吗？（久经考验）
Option Explicit
Sub Mail_workbook_Outlook_1()
'Working in 2000-2010
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object
Dim emailRng As Range, cl As Range
Dim sTo As String
Set emailRng = Worksheets("Selections").Range("D3:D6")
For Each cl In emailRng
sTo = sTo & ";" & cl.Value
Next
sTo = Mid(sTo, 2)
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = sTo
.CC = "[email protected];[email protected]"
.BCC = ""
.Subject = "RMA #" & Worksheets("RMA").Range("E1")
.Body = "Attached to this email is RMA #" & _
Worksheets("RMA").Range("E1") & _
". Please follow the instructions for your department included in this form."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
回答by MD5
ToAddress = "[email protected]"
ToAddress1 = "[email protected]"
ToAddress2 = "[email protected]"
MessageSubject = "It works!."
Set ol = CreateObject("Outlook.Application")
Set newMail = ol.CreateItem(olMailItem)
newMail.Subject = MessageSubject
newMail.RecipIents.Add(ToAddress)
newMail.RecipIents.Add(ToAddress1)
newMail.RecipIents.Add(ToAddress2)
newMail.Send
