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