excel - 如何调用 Excel 中的两个变量来抄送 Outlook 电子邮件？

Question

此宏使用包含模板的 HTML 文件来发送大量电子邮件。

我设置了一个函数来调用一列电子邮件地址来填写抄送。

需求变了，我不想把两列浓缩成一个抄送两个人。

我尝试制作第二个函数以将第二个变量添加到 CC。

我想要 .Cc = [e-mail]; 电子邮件@email.com

该宏从预先填写的电子邮件地址列表中提取，这就是它在函数中有一个范围的原因。

Sub PreviewEmail(wsNew As Worksheet, looper As Range, month As Range, year As Range)

'Macro Purpose: To send an email through Outlook

    Dim rng As Range
    Dim ToEmailList As String
    Dim CcEmailList As String
    Dim CcEmailListT As String
    Dim sSubject As String
    Dim sName As String
    Dim line As String
    Dim oFSO
    Dim oFs
    Dim pathName As String

    pathName = ActiveWorkbook.Path & "\template.htm"

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFs = oFSO.OpenTextFile(pathName)

    ToEmailList = setToEmail
    CcEmailList = setCcEmail
    CcEmailListT = setCcEmailT
    sSubject = "This is a test"
    sName = setSendName

    Set OutApp = GetObject(, "Outlook.Application")

    If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set rng = wsNew.Range("A1:F" & wsNew.UsedRange.Rows.Count)

    With OutMail
        stext = oFs.readall
        For Each cell In looper
            line = line & cell.Text & " " & cell.Offset(0, 1).Text & " <br />"
        Next

        stext = Replace(stext, "%variable%", line)
        stext = Replace(stext, "monthmonthmonth", month.Text)
        stext = Replace(stext, "yearyearyear", year.Text)

        .SentOnBehalfOfName = "JPMC Workforce Screening"
        .To = ToEmailList
        .Cc = CcEmailList, CcEmailListT
        .Subject = sSubject
        .HTMLBody = stext
        .Display

    End With

End Sub

标签： excelvbaoutlook

Answer 1

代替

.Cc = CcEmailList, CcEmailListT

和

.Cc = CcEmailList & ";" & CcEmailListT