Using Excel Checkboxes as variables in email VBA

bmpreston

Board Regular
Joined
Jun 18, 2016
Messages
113
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
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Watch MrExcel Video

Forum statistics

Threads
1,127,573
Messages
5,625,591
Members
416,120
Latest member
B3nj1

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
Top