Using Excel Checkboxes as variables in email VBA

[bmpreston]

I currently use the below VBA to simply email the document it's open in to the 2 email addresses listed. I'm hoping to use a list and checkboxes to select which email addresses to send the document to.

Any help with using Check Boxes as variables would be very helpful.

I'm thinking that a check box is the name, in a separate cell is the email address, which create an if statement:

If Check box 'dave' then .To=CellA2 value

I am hoping to multi-select users and add/delete email addresses as I go.

Thanks!

'Make a copy of the file/Open it/Mail it/Delete it
'If you want to change the file name then change only TempFileName
TempFilePath = Environ$("temp") & "\"
TempFileName = "DALE & ETM for " + TechName
FileExtStr = ".xlsm"
'& LCase(Right(wb1.Name, Len(wb1.Name) - InStrRev(wb1.Name, ".", , 1)))

wb1.SaveCopyAs TempFilePath & TempFileName & FileExtStr
MsgBox "Email Sent to blah@blah.com & fubar@email.com" & vbNewLine & "Thank You"
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "Blah@blah.com"
.CC = "fubar@email.com"
.BCC = ""
.Subject = "Weekly DALE & ETM for " + TechName
.Body = "Weekly DALE & ETM"
.Attachments.Add TempFilePath & TempFileName & FileExtStr
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
'.Display

End With
On Error GoTo 0

'Delete the file
Kill TempFilePath & TempFileName & FileExtStr

Set OutMail = Nothing
Set OutApp = Nothing

With Application
.ScreenUpdating = True
.EnableEvents = True
End With