Question about emailing through Outlook and Arrays


New Member
Dec 28, 2016
I have 13 strings that hold cell email address values, is there a way to put all of those values in the .to portion of the with oMail section? Do I need to store all 13 variables into an array? Also, I know I could've looped the variable assignments, but since 13 is the max there will ever be on this sheet I decided to hard code it. I made a comment below in the code to hopefully make it easy to find. Thanks for any help!!

VBA Code:
Private Sub SendBtn_Click()

EmailTextBox1.Value = Trim(EmailTextBox1.Value)
EmailTextBox2.Value = Trim(EmailTextBox2.Value)
EmailTextBox3.Value = Trim(EmailTextBox3.Value)

If EmailTextBox1.Value = "" Then
TypeBox1.Value = False
End If

If EmailTextBox2.Value = "" Then
TypeBox2.Value = False
End If

If EmailTextBox3.Value = "" Then
TypeBox3.Value = False
End If

Dim Email1, Email2, Email3, Email4, Email5, Email6, Email7, Email8, _
    Email9, Email10, Email11, Email12, Email13 As String
Dim ws As Worksheet

If EmailBox1.Value = True Then
Email1 = Sheet1.Range("M3")
End If

If EmailBox2.Value = True Then
Email2 = Sheet1.Range("M4")
End If

If EmailBox3.Value = True Then
Email3 = Sheet1.Range("M5")
End If

If EmailBox4.Value = True Then
Email4 = Sheet1.Range("M6")
End If

If EmailBox5.Value = True Then
Email5 = Sheet1.Range("M7")
End If

If EmailBox6.Value = True Then
Email6 = Sheet1.Range("M8")
End If

If EmailBox7.Value = True Then
Email7 = Sheet1.Range("M9")
End If

If EmailBox8.Value = True Then
Email8 = Sheet1.Range("M10")
End If

If EmailBox9.Value = True Then
Email9 = Sheet1.Range("M11")
End If

If EmailBox10.Value = True Then
Email10 = Sheet1.Range("M2")
End If

If TypeBox1.Value = True Then
Email11 = EmailTextBox1.Value
End If

If TypeBox2.Value = True Then
Email12 = EmailTextBox2.Value
End If

If TypeBox3.Value = True Then
Email13 = EmailTextBox3.Value
End If

Dim Approvers As Variant
Approvers = Array(Email1, Email2)

    Set ws = ActiveWorkbook.ActiveSheet

'Write your email message body here , add more lines using & vbLf _ at the end of each line
    body = "Please see attached for withdrawal sign off request. " & vbLf _
    & vbLf _
    & "Thanks," & vbLf _
'Copy Active Sheet and save it to a temporary file

    Set cWB = ActiveWorkbook
    Set tWB = ActiveWorkbook
    FileName = "Withdrawal" 'You can define the name
    FilePath = Environ("TEMP")
    On Error Resume Next
    Kill FilePath & "\" & FileName
    On Error GoTo 0
    Application.DisplayAlerts = False
    tWB.SaveAs FileName:=FilePath & "\" & FileName, FileFormat:=56
    Application.DisplayAlerts = True
'Sending email through outlook

    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    With oMail
        .to = Email1 & Email2' <---------------------------- This is the part that's giving me problems
        '.CC = mailid
        .Subject = "WISH Tracker Signoff Request"
        .body = body
        .Attachments.Add tWB.FullName
    End With
'Delete the temporary file and restore screen updating
    tWB.ChangeFileAccess Mode:=xlReadOnly
    Kill tWB.FullName
    tWB.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing

End Sub

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.


MrExcel MVP
Oct 15, 2007
Separate each email address with a semi-colon, so .to = Email1 & ";" & Email2

Watch MrExcel Video

Forum statistics

Latest member