email conditions

[Guest]

Hello everyone.
I am currently using the code below when assigned to a form button to e-mail
off a workbook. Over the course of using my system different worksheets are
added to the workbook. Does anyone know of any way in which i can change the
e-mail address if the Wb contains a certain worksheet? e.g If workbook
contains "Wksht1" or "Wksht2" then send to (e-mail address removed) OR/AND if
Workbook contains "wksht 3" or "Wksht 4" then send to (e-mail address removed). If
there is any way that can be added to the code below you will be a lifesaver.
VB is not my first language and it's getting me in strife! Thanks in
advance, Andrew bt

Dim wb As Workbook
Set wb = ActiveWorkbook
If Val(Application.Version) >= 12 Then
If wb.FileFormat = 51 And wb.HasVBProject = True Then
MsgBox "There is VBA code in this xlsx file, there will be no
VBA code in the file you send." & vbNewLine & _
"Save the file first as xlsm and then try the macro
again.", vbInformation
Exit Sub
End If
End If
On Error Resume Next
wb.SendMail "(e-mail address removed)", _
"Test e-mail"
On Error GoTo 0
End Sub

 

[Ron de Bruin]

Hi andrewbt

You can use this in your code

On Error Resume Next
If SheetExists("wksht 3") = True Then
wb.SendMail "(e-mail address removed)", _
"Test e-mail"
Else
wb.SendMail "(e-mail address removed)", _
"Test e-mail"
End If
On Error GoTo 0



Copy this function also in the module

Function SheetExists(SName As String, _
Optional ByVal wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If wb Is Nothing Then Set wb = ThisWorkbook
SheetExists = CBool(Len(wb.Sheets(SName).Name))
End Function

 

[Guest]

Ron you are a life saver yet again, if you ever want the favour returning and
need any help with php, asp, JavaScript, Flash/ActionScript or any thing like
that then drop me a line at (e-mail address removed) i might be able to help, seen
as your excel stuff as answered my prayers on plenty of occasions. ( as you
can tell i'm not a fan of VB ).
Thanks