Send email if true

[cjjn]

Hello,

I know this is going to be painfully simple - but I need help.
Here is what I would like to do - if Cell B31 is true - I would like for an email to be sent to just one person
however - if it is False or blank, I would like to have the file sent to 2 people.

Here is my code - I keep getting an error (Object not found).

Sub Send_Mail()

Dim recipArr(1) As String
Dim CellValue As String
recipArr(0) = "person1@work.com"
recipArr(1) = "person2@work.com"
CellValue = cell.Range("B31").Value

If CellValue = "TRUE" Then
ActiveWorkbook.Save

ActiveWorkbook.SendMail Recipients:="person1@work.com", Subject:="Injuries"
Else
ActiveWorkbook.Save

ActiveWorkbook.SendMail Recipients:=recipArr, Subject:="Injuries"
End If

End Sub

Thank you in advance

 

[kphu]

try this:

Sub Send_Mail()

Dim recipArr(1) As String
Dim CellValue As String
recipArr(0) = "person1@work.com"
recipArr(1) = "person2@work.com"

CellValue = Range("B31").Value

If CellValue = "TRUE" Then
    ActiveWorkbook.Save
    
    ActiveWorkbook.SendMail Recipients:="person1@work.com", Subject:="Injuries"
Else
    ActiveWorkbook.Save
                
    ActiveWorkbook.SendMail Recipients:=recipArr(), Subject:="Injuries"
End If
        
End Sub

 

[cjjn]

Thank you kphu!

That worked much better - but now sends the e-mail to both, even if Cell B31 is TRUE.

I am using a check box - if it is checked then the linked cell (B31) would be TRUE. I am not sure that cell value still works in this case as it does not seem to recognize when it changes to TRUE.

Advice?

Thanks again - much appreciated

 

[PHV]

Sub Send_Mail()
Dim recipArr(1) As String
recipArr(0) = "person1@work.com"
recipArr(1) = "person2@work.com"
ActiveWorkbook.Save
If Range("B31").Value = True Then
ActiveWorkbook.SendMail Recipients:="person1@work.com", Subject:="Injuries"
Else
ActiveWorkbook.SendMail Recipients:=recipArr(), Subject:="Injuries"
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[kphu]

Instead of linking the value of cell B31 to true or false we can get the information directly from the source.

So lets get rid of cellvalue.

I'm genericly using the check box name as checkbox. You'll have to rename it accordingly.

Sub Send_Mail()

Dim recipArr(1) As String
Dim CellValue As String
recipArr(0) = "person1@work.com"
recipArr(1) = "person2@work.com"



If checkbox.value = True Then
    ActiveWorkbook.Save
    
    ActiveWorkbook.SendMail Recipients:="person1@work.com", Subject:="Injuries"
Else
    ActiveWorkbook.Save
                
    ActiveWorkbook.SendMail Recipients:=recipArr(), Subject:="Injuries"
End If
        
End Sub

 

[cjjn]

Thank you again,
That looks like a good plan. I apologize for yet another basic knowledge question - but here it is.
The checkbox I am using is named in Excel as Check Box 35
I know I cannot use spaces in the naming in VB - I tried quotes but it does not work.

How do I reference it in code?

thanks

 

[kphu]

I don't think its possible for you to name the check box as check box 35. What you might be confusing this is possibly the caption?

If you go into edit mode for the forms you can right click the check box and go to properties. From there it'll tell you the name of the checkbox.

When you reference it. you'll have to reference it from the parent object.

if the checkbox is in sheet1 and the actual name of the check box is checkbox35 then you'll reference it as.

worksheets("Sheet1").checkbox35.value

If this code is in Sheet1 then you can reference it as
checkbox35.value

If its in a module then you'll need to reference as the line with worksheets("...

 

[cjjn]

Worked!

Thanks so much - really I appreciate your taking the time.