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 OR/AND if
Workbook contains "wksht 3" or "Wksht 4" then send to . 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 ", _
"Test e-mail"
On Error GoTo 0
End Sub

Hi andrewbt

You can use this in your code

On Error Resume Next
If SheetExists("wksht 3") = True Then
wb.SendMail ", _
"Test e-mail"
Else
wb.SendMail ", _
"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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"andrewbt" wrote in message ...
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 OR/AND if
Workbook contains "wksht 3" or "Wksht 4" then send to . 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 ", _
"Test e-mail"
On Error GoTo 0
End Sub

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 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
"Ron de Bruin" wrote:

Hi andrewbt

You can use this in your code

On Error Resume Next
If SheetExists("wksht 3") = True Then
wb.SendMail ", _
"Test e-mail"
Else
wb.SendMail ", _
"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



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"andrewbt" wrote in message ...
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 OR/AND if
Workbook contains "wksht 3" or "Wksht 4" then send to . 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 ", _
"Test e-mail"
On Error GoTo 0
End Sub