Question about emailing through Outlook and Arrays

Grant22

New Member
Joined
Dec 28, 2016
Messages
48
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
    ActiveSheet.Copy
    
    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
        .Send
    End With
                 
'Delete the temporary file and restore screen updating
    
    tWB.ChangeFileAccess Mode:=xlReadOnly
    Kill tWB.FullName
    tWB.Close SaveChanges:=False
    cWB.Activate
    Application.ScreenUpdating = True
    Set oMail = Nothing
    Set oApp = Nothing


End Sub
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Forum statistics

Threads
1,223,903
Messages
6,175,289
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top