For VBA, how can you use an IF statement to display a specific range as a picture for an email?

Question

I have a VBA module that I found which sends out an email of a range sent as a picture. I want to use checkboxes to select which ranges need to be sent. Is there a way to use checkboxes that when you press a button, VBA grabs the ranges as a picture then displays them in the body of the email?

Here is the code that emails the whole sheet, rather than the specific ranges.

Sub esendtable14()


Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)

With newEmail
.To = "Example"
.CC = "Example"
.BCC = ""
.Subject = Sheet1.Range("G1").Text
.Body = ""
.display

Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor

Sheet17.Range("A1:M212").Copy


pageEditor.Application.Selection.Start = Len(.Body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
.display
Set pageEditor = Nothing
Set xInspect = Nothing
End With

Set newEmail = Nothing
Set outlook = Nothing

End Sub

The range of data, that if true, would look like "A1:H12", "A13:H24", "A25:H36". Sometimes not all the ranges are in use, and I would rather have them not show up in the email. Thank you in advance!

Answer 1

This is possible. Here's one way to pull this off.

1. Put a checkbox on your worksheet. Make sure that this checkbox is the "ActiveX Controls" type not the "Form Control" type. ActiveX Objects have more hooks back into VBA and will work best for this sort of thing.

   

2. Head over to your VBA and double click on your worksheet where your checkbox resides so that the worksheet code page is brought to the front. At the top there are two boxes where we can select events. These events will fire code when the event happens. Select Checkbox1 (the one we just added) and Click. (Click will actually be default so you really don't have to select that one.)

   

3. You should have a subroutine named "Checkbox1_Click()" created automatically at this point. Every time you click on that checkbox to check or uncheck the checkbox, this code will fire. So lets put a test/if-condition to see if the click that fired this code resulted in a checked checkbox.

   Private Sub CheckBox1_Click()
       'check if checked
       If CheckBox1.Value = True Then
   
   
       End If
   End Sub
   

4. Now we are going to modify your subroutine to add an input parameter. This is a parameter that we will send to this subroutine when we call it. We want to send to your subroutine the range that we want it to print.

   a. First add the parameter in the subroutine definition:

       Sub esendtable14(SendRange As Range)
   

   b. Second change the bit of code where you specify the range to use this parameter instead

   change:

       Sheet17.Range("A1:M212").Copy
   

   to:

       SendRange.Copy
   

5. Go back to the Checkbox1_Click() procedure and call your subroutine passing it whatever range you want in your email:

   Private Sub CheckBox1_Click()
       'check if checked
       If CheckBox1.Value = True Then
           Call esendtable14(Sheet17.Range("A1:M212"))
       End If
   End Sub
   

Now just follow those steps for every additional checkbox/range you want to add.

---

As an aside, the test to see if the checkbox is checked is written a little heavy-handed. CheckBox1.Value is going to return True or False which is all that If needs, so the = True is superfluous. That could be rewritten as

 If CheckBox1.Value Then