如何使用存储在 Excel 中的地址向多个收件人发送电子邮件? [英] How to send email to multiple recipients with addresses stored in Excel?

查看:28
本文介绍了如何使用存储在 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:	est.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:	est.txt")

        .Display
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

这篇关于如何使用存储在 Excel 中的地址向多个收件人发送电子邮件?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆