Check Box Email

JRR1229

New Member
Joined
Jun 23, 2020
Messages
9
Office Version
  1. 2016
Platform
  1. Windows
I have a column of check boxes that corrisboned with Contact information to clients. I already have a command button that will open a new email, with populated body, and add all the emails in the list to all the BCC (see code below). I am trying to change it where I can choose what emails will be added to the BCC by checking the box and then hitting the command button.
VBA Code:
Private Sub Email_Click()
'Unprotect Sheet
     ActiveSheet.Unprotect ("NEXTELECTRIC")

'Setting up the Excel variables.
   Dim olApp As Object
   Dim olMailItm As Object
   Dim iCounter As Integer
   Dim Dest As Variant
   Dim SDest As String
    
  
   'Create the Outlook application and the empty email.
   Set olApp = CreateObject("Outlook.Application")
   Set olMailItm = olApp.CreateItem(0)
  
   'Body of Email
   xRFQBody = "Good Morning Afternoon," & vbCrLf & vbCrLf & "Please provide a * quote for the * project. This quote is for a bid." & _
   vbCrLf & vbCrLf & "Response by * is needed." & _
   vbCrLf & vbCrLf & _
   "o          All materials must comply with the project specifications and drawings" & vbCrLf & _
   "o          Provide quantities and unit prices for attic stock per specifications if applicable" & vbCrLf & _
   "o          All orders to be FOB destination" & vbCrLf & _
   vbCrLf & "See link for additional information" & _
   vbCrLf & vbCrLf & vbCrLf & "Feel free to contact me with any questions." & _
   vbCrLf & vbCrLf
  
  
   'Using the email, add multiple recipients, using a list of addresses in column A.
   With olMailItm
       SDest = ""
       For iCounter = 5 To WorksheetFunction.CountA(Columns(5))
           If SDest = "" Then
               SDest = Cells(iCounter, 4).Value
           Else
               SDest = SDest & ";" & Cells(iCounter, 3).Value
           End If
       Next iCounter
      
    'Do additional formatting on the BCC and Subject lines, add the body text from the spreadsheet, and send.
       .BCC = SDest
       .Subject = "RFQ"
       .Body = xRFQBody
       .Display
   End With
  
   'Clean up the Outlook application.
   Set olMailItm = Nothing
   Set olApp = Nothing
  
'Protect Sheet
     ActiveSheet.Protect ("NEXTELECTRIC")
  
End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Watch MrExcel Video

Forum statistics

Threads
1,123,346
Messages
5,601,087
Members
414,426
Latest member
fraru

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