如何使用存储在Excel中的地址向多个收件人发送电子邮件? [英] How to send email to multiple recipients with addresses stored in Excel?
问题描述
我正在尝试在Excel表单上设置几个按钮,以向不同的人群发送电子邮件.
我在单独的工作表上创建了多个单元格区域,以列出电子邮件地址.
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 email addresses.
例如,我想要按钮A"打开Outlook并放入工作表B:单元格D3-D6"中的电子邮件地址列表.然后,将所有必须完成的操作按发送",然后单击发送".在Outlook中.
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.
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 = "person1@email.com;person2@email.com"
.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
推荐答案
您必须遍历"D3:D6"
范围内的每个单元格,并构造您的 To
细绳.简单地将其分配给变体并不能解决目的.如果直接将范围分配给 EmailTo
,则它将成为一个数组.您也可以执行此操作,但是随后必须遍历数组以创建 To
字符串
You have to loop through every cell in the range "D3:D6"
and construct your To
string. Simply assigning it to a variant will not solve the purpose. EmailTo
becomes 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 To
string
这是您要尝试的吗?(尝试并测试)
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 = "person1@email.com;person2@email.com"
.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
这篇关于如何使用存储在Excel中的地址向多个收件人发送电子邮件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!