Excel SendMail Not working in XP Office 2003

I have an Excel Spreadsheet that contains a button. In the button there is a
macro that uses the Sendmail VB command. It works in Window 2000 Office
2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
get the following error.

The VBProject of the Copy of Facility Request form.xls is either protected
or has no compoents.

Can it be fixed? Why do I get this error in XP Office 2003 and not in
Windows 2000 Office 2003?

Thanks for you help in advance.

Can you post the entire code which is behind the Button. Without it, it's
quite hard to tell.


"Dannyboy1001" wrote:

> I have an Excel Spreadsheet that contains a button. In the button there is a
> macro that uses the Sendmail VB command. It works in Window 2000 Office
> 2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
> get the following error.
>
> The VBProject of the Copy of Facility Request form.xls is either protected
> or has no compoents.
>
> Can it be fixed? Why do I get this error in XP Office 2003 and not in
> Windows 2000 Office 2003?
>
> Thanks for you help in advance.
>
>

Here is the macro in the button:

Sub SendDocumentAsAttachment()

Dim wb As Workbook
Dim strdate As String
Dim fac, disemail As String
strdate = Format(Now, "dd-mm-yy h-mm-ss")
Application.ScreenUpdating = False
ActiveSheet.Copy
Set wb = ActiveWorkbook
fac = wb.ActiveSheet.Range("B7").Value
disemail = wb.ActiveSheet.Range("Z7").Value

With wb
.SaveAs "Copy of " & ThisWorkbook.Name _
& " " & strdate & ".xls"
If disemail = "" Then
.SendMail "[email protected]", _
"Cloverleaf Interface Request for " & fac
Else
.SendMail Array("[email protected]", disemail), _
"Cloverleaf Interface Request for " & fac
End If
RemoveAllMacros ActiveWorkbook
.Close True
End With
Application.ScreenUpdating = True
ActiveWorkbook.Close True

End Sub

Sub RemoveAllMacros(objDocument As Object)
' deletes all VBProject components from objDocument
' removes the code from built-in components that can't be deleted
' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
' requires a reference to the
' Microsoft Visual Basic for Applications Extensibility library
Dim i As Long, l As Long
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
End Sub


"gocush" wrote:

> Can you post the entire code which is behind the Button. Without it, it's
> quite hard to tell.
>
>
> "Dannyboy1001" wrote:
>
> > I have an Excel Spreadsheet that contains a button. In the button there is a
> > macro that uses the Sendmail VB command. It works in Window 2000 Office
> > 2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
> > get the following error.
> >
> > The VBProject of the Copy of Facility Request form.xls is either protected
> > or has no compoents.
> >
> > Can it be fixed? Why do I get this error in XP Office 2003 and not in
> > Windows 2000 Office 2003?
> >
> > Thanks for you help in advance.
> >
> >

A couple of things come to mind:
1. The message you are getting is not an error. It is part of the
RemoveAllMacros Sub that is included with your code. It says that either
there are no VBA components to remove, or that you have protected the VB Code
with a password. I would try looking at the vb code: Alt +F11. If it asks
for a password then you (or somebody) have protected the vb code, hence the
msg.
If this is the case, you can unprotect it or change the part of you code
that gives you the message.

2. In the first part of you code you are sending a copy of your worksheet
as email, THEN deleting the vb code. Usually, the vb code is removed BEFORE
sending the email in order to protect your code or if the recipient has no
need for it. Do you have some other reason for removing the code after
sending the email? If not then move the Remove..... line up before the
Sendmail line



"Dannyboy1001" wrote:

> Here is the macro in the button:
>
> Sub SendDocumentAsAttachment()
>
> Dim wb As Workbook
> Dim strdate As String
> Dim fac, disemail As String
> strdate = Format(Now, "dd-mm-yy h-mm-ss")
> Application.ScreenUpdating = False
> ActiveSheet.Copy
> Set wb = ActiveWorkbook
> fac = wb.ActiveSheet.Range("B7").Value
> disemail = wb.ActiveSheet.Range("Z7").Value
>
> With wb
> .SaveAs "Copy of " & ThisWorkbook.Name _
> & " " & strdate & ".xls"
> If disemail = "" Then
> .SendMail "[email protected]", _
> "Cloverleaf Interface Request for " & fac
> Else
> .SendMail Array("[email protected]", disemail), _
> "Cloverleaf Interface Request for " & fac
> End If
> RemoveAllMacros ActiveWorkbook
> .Close True
> End With
> Application.ScreenUpdating = True
> ActiveWorkbook.Close True
>
> End Sub
>
> Sub RemoveAllMacros(objDocument As Object)
> ' deletes all VBProject components from objDocument
> ' removes the code from built-in components that can't be deleted
> ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
> ' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
> ' requires a reference to the
> ' Microsoft Visual Basic for Applications Extensibility library
> Dim i As Long, l As Long
> If objDocument Is Nothing Then Exit Sub
> i = 0
> On Error Resume Next
> i = objDocument.VBProject.VBComponents.Count
> On Error GoTo 0
> If i < 1 Then ' no VBComponents or protected VBProject
> MsgBox "The VBProject in " & objDocument.Name & _
> " is protected or has no components!", _
> vbInformation, "Remove All Macros"
> Exit Sub
> End If
> With objDocument.VBProject
> For i = .VBComponents.Count To 1 Step -1
> On Error Resume Next
> .VBComponents.Remove .VBComponents(i)
> ' delete the component
> On Error GoTo 0
> Next i
> End With
> With objDocument.VBProject
> For i = .VBComponents.Count To 1 Step -1
> l = 1
> On Error Resume Next
> l = .VBComponents(i).CodeModule.CountOfLines
> .VBComponents(i).CodeModule.DeleteLines 1, l
> ' clear lines
> On Error GoTo 0
> Next i
> End With
> End Sub
>
>
> "gocush" wrote:
>
> > Can you post the entire code which is behind the Button. Without it, it's
> > quite hard to tell.
> >
> >
> > "Dannyboy1001" wrote:
> >
> > > I have an Excel Spreadsheet that contains a button. In the button there is a
> > > macro that uses the Sendmail VB command. It works in Window 2000 Office
> > > 2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
> > > get the following error.
> > >
> > > The VBProject of the Copy of Facility Request form.xls is either protected
> > > or has no compoents.
> > >
> > > Can it be fixed? Why do I get this error in XP Office 2003 and not in
> > > Windows 2000 Office 2003?
> > >
> > > Thanks for you help in advance.
> > >
> > >

Item 1 is not the issue, the VBA compoents when opened does not ask for a
password. I moved the RemoveAllMacros ActiveWorkbook statement before the IF
statement and I still get the same error. This code works without any issues
in Windows 2000/Office 2000. Can anyone tell me why this code is not working
in WIN XP/Office 2003?

"gocush" wrote:

> A couple of things come to mind:
> 1. The message you are getting is not an error. It is part of the
> RemoveAllMacros Sub that is included with your code. It says that either
> there are no VBA components to remove, or that you have protected the VB Code
> with a password. I would try looking at the vb code: Alt +F11. If it asks
> for a password then you (or somebody) have protected the vb code, hence the
> msg.
> If this is the case, you can unprotect it or change the part of you code
> that gives you the message.
>
> 2. In the first part of you code you are sending a copy of your worksheet
> as email, THEN deleting the vb code. Usually, the vb code is removed BEFORE
> sending the email in order to protect your code or if the recipient has no
> need for it. Do you have some other reason for removing the code after
> sending the email? If not then move the Remove..... line up before the
> Sendmail line
>
>
>
> "Dannyboy1001" wrote:
>
> > Here is the macro in the button:
> >
> > Sub SendDocumentAsAttachment()
> >
> > Dim wb As Workbook
> > Dim strdate As String
> > Dim fac, disemail As String
> > strdate = Format(Now, "dd-mm-yy h-mm-ss")
> > Application.ScreenUpdating = False
> > ActiveSheet.Copy
> > Set wb = ActiveWorkbook
> > fac = wb.ActiveSheet.Range("B7").Value
> > disemail = wb.ActiveSheet.Range("Z7").Value
> >
> > With wb
> > .SaveAs "Copy of " & ThisWorkbook.Name _
> > & " " & strdate & ".xls"
> > If disemail = "" Then
> > .SendMail "[email protected]", _
> > "Cloverleaf Interface Request for " & fac
> > Else
> > .SendMail Array("[email protected]", disemail), _
> > "Cloverleaf Interface Request for " & fac
> > End If
> > RemoveAllMacros ActiveWorkbook
> > .Close True
> > End With
> > Application.ScreenUpdating = True
> > ActiveWorkbook.Close True
> >
> > End Sub
> >
> > Sub RemoveAllMacros(objDocument As Object)
> > ' deletes all VBProject components from objDocument
> > ' removes the code from built-in components that can't be deleted
> > ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
> > ' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
> > ' requires a reference to the
> > ' Microsoft Visual Basic for Applications Extensibility library
> > Dim i As Long, l As Long
> > If objDocument Is Nothing Then Exit Sub
> > i = 0
> > On Error Resume Next
> > i = objDocument.VBProject.VBComponents.Count
> > On Error GoTo 0
> > If i < 1 Then ' no VBComponents or protected VBProject
> > MsgBox "The VBProject in " & objDocument.Name & _
> > " is protected or has no components!", _
> > vbInformation, "Remove All Macros"
> > Exit Sub
> > End If
> > With objDocument.VBProject
> > For i = .VBComponents.Count To 1 Step -1
> > On Error Resume Next
> > .VBComponents.Remove .VBComponents(i)
> > ' delete the component
> > On Error GoTo 0
> > Next i
> > End With
> > With objDocument.VBProject
> > For i = .VBComponents.Count To 1 Step -1
> > l = 1
> > On Error Resume Next
> > l = .VBComponents(i).CodeModule.CountOfLines
> > .VBComponents(i).CodeModule.DeleteLines 1, l
> > ' clear lines
> > On Error GoTo 0
> > Next i
> > End With
> > End Sub
> >
> >
> > "gocush" wrote:
> >
> > > Can you post the entire code which is behind the Button. Without it, it's
> > > quite hard to tell.
> > >
> > >
> > > "Dannyboy1001" wrote:
> > >
> > > > I have an Excel Spreadsheet that contains a button. In the button there is a
> > > > macro that uses the Sendmail VB command. It works in Window 2000 Office
> > > > 2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
> > > > get the following error.
> > > >
> > > > The VBProject of the Copy of Facility Request form.xls is either protected
> > > > or has no compoents.
> > > >
> > > > Can it be fixed? Why do I get this error in XP Office 2003 and not in
> > > > Windows 2000 Office 2003?
> > > >
> > > > Thanks for you help in advance.
> > > >
> > > >

oK. The message you are getting is dependent upon the following code line
from your Remove..... sub:

i = objDocument.VBProject.VBComponents.Count

could you put a breakpoint on this line, then run the code, step into this
one line then pass your cursor over the variable "i"

If "i" is less than 1 then you don't have any vb components in this new
workbook . You are trying to remove vb with this code. If none exists
then you will get this msg. To verify this, open the Visual Basic Editor,
Select the file that you are trying to email and see if there are any modules
or code behind the sheet objects or ThisWorkbook module.

Get back to us with what the vb component count is in the wbk you are
trying to email. Then we can see where to go from there.

"Dannyboy1001" wrote:

> Item 1 is not the issue, the VBA compoents when opened does not ask for a
> password. I moved the RemoveAllMacros ActiveWorkbook statement before the IF
> statement and I still get the same error. This code works without any issues
> in Windows 2000/Office 2000. Can anyone tell me why this code is not working
> in WIN XP/Office 2003?
>
> "gocush" wrote:
>
> > A couple of things come to mind:
> > 1. The message you are getting is not an error. It is part of the
> > RemoveAllMacros Sub that is included with your code. It says that either
> > there are no VBA components to remove, or that you have protected the VB Code
> > with a password. I would try looking at the vb code: Alt +F11. If it asks
> > for a password then you (or somebody) have protected the vb code, hence the
> > msg.
> > If this is the case, you can unprotect it or change the part of you code
> > that gives you the message.
> >
> > 2. In the first part of you code you are sending a copy of your worksheet
> > as email, THEN deleting the vb code. Usually, the vb code is removed BEFORE
> > sending the email in order to protect your code or if the recipient has no
> > need for it. Do you have some other reason for removing the code after
> > sending the email? If not then move the Remove..... line up before the
> > Sendmail line
> >
> >
> >
> > "Dannyboy1001" wrote:
> >
> > > Here is the macro in the button:
> > >
> > > Sub SendDocumentAsAttachment()
> > >
> > > Dim wb As Workbook
> > > Dim strdate As String
> > > Dim fac, disemail As String
> > > strdate = Format(Now, "dd-mm-yy h-mm-ss")
> > > Application.ScreenUpdating = False
> > > ActiveSheet.Copy
> > > Set wb = ActiveWorkbook
> > > fac = wb.ActiveSheet.Range("B7").Value
> > > disemail = wb.ActiveSheet.Range("Z7").Value
> > >
> > > With wb
> > > .SaveAs "Copy of " & ThisWorkbook.Name _
> > > & " " & strdate & ".xls"
> > > If disemail = "" Then
> > > .SendMail "[email protected]", _
> > > "Cloverleaf Interface Request for " & fac
> > > Else
> > > .SendMail Array("[email protected]", disemail), _
> > > "Cloverleaf Interface Request for " & fac
> > > End If
> > > RemoveAllMacros ActiveWorkbook
> > > .Close True
> > > End With
> > > Application.ScreenUpdating = True
> > > ActiveWorkbook.Close True
> > >
> > > End Sub
> > >
> > > Sub RemoveAllMacros(objDocument As Object)
> > > ' deletes all VBProject components from objDocument
> > > ' removes the code from built-in components that can't be deleted
> > > ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
> > > ' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
> > > ' requires a reference to the
> > > ' Microsoft Visual Basic for Applications Extensibility library
> > > Dim i As Long, l As Long
> > > If objDocument Is Nothing Then Exit Sub
> > > i = 0
> > > On Error Resume Next
> > > i = objDocument.VBProject.VBComponents.Count
> > > On Error GoTo 0
> > > If i < 1 Then ' no VBComponents or protected VBProject
> > > MsgBox "The VBProject in " & objDocument.Name & _
> > > " is protected or has no components!", _
> > > vbInformation, "Remove All Macros"
> > > Exit Sub
> > > End If
> > > With objDocument.VBProject
> > > For i = .VBComponents.Count To 1 Step -1
> > > On Error Resume Next
> > > .VBComponents.Remove .VBComponents(i)
> > > ' delete the component
> > > On Error GoTo 0
> > > Next i
> > > End With
> > > With objDocument.VBProject
> > > For i = .VBComponents.Count To 1 Step -1
> > > l = 1
> > > On Error Resume Next
> > > l = .VBComponents(i).CodeModule.CountOfLines
> > > .VBComponents(i).CodeModule.DeleteLines 1, l
> > > ' clear lines
> > > On Error GoTo 0
> > > Next i
> > > End With
> > > End Sub
> > >
> > >
> > > "gocush" wrote:
> > >
> > > > Can you post the entire code which is behind the Button. Without it, it's
> > > > quite hard to tell.
> > > >
> > > >
> > > > "Dannyboy1001" wrote:
> > > >
> > > > > I have an Excel Spreadsheet that contains a button. In the button there is a
> > > > > macro that uses the Sendmail VB command. It works in Window 2000 Office
> > > > > 2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
> > > > > get the following error.
> > > > >
> > > > > The VBProject of the Copy of Facility Request form.xls is either protected
> > > > > or has no compoents.
> > > > >
> > > > > Can it be fixed? Why do I get this error in XP Office 2003 and not in
> > > > > Windows 2000 Office 2003?
> > > > >
> > > > > Thanks for you help in advance.
> > > > >
> > > > >

I figured out my problem. It's not an issue. I found out (and I think you
stated it early) that in Outlook 2003 there is a security box that now
appears. I apologize for taking up your time. I do have another issue, but
I will post it on a new thread.

Again thanks for your help

"gocush" wrote:

> oK. The message you are getting is dependent upon the following code line
> from your Remove..... sub:
>
> i = objDocument.VBProject.VBComponents.Count
>
> could you put a breakpoint on this line, then run the code, step into this
> one line then pass your cursor over the variable "i"
>
> If "i" is less than 1 then you don't have any vb components in this new
> workbook . You are trying to remove vb with this code. If none exists
> then you will get this msg. To verify this, open the Visual Basic Editor,
> Select the file that you are trying to email and see if there are any modules
> or code behind the sheet objects or ThisWorkbook module.
>
> Get back to us with what the vb component count is in the wbk you are
> trying to email. Then we can see where to go from there.
>
> "Dannyboy1001" wrote:
>
> > Item 1 is not the issue, the VBA compoents when opened does not ask for a
> > password. I moved the RemoveAllMacros ActiveWorkbook statement before the IF
> > statement and I still get the same error. This code works without any issues
> > in Windows 2000/Office 2000. Can anyone tell me why this code is not working
> > in WIN XP/Office 2003?
> >
> > "gocush" wrote:
> >
> > > A couple of things come to mind:
> > > 1. The message you are getting is not an error. It is part of the
> > > RemoveAllMacros Sub that is included with your code. It says that either
> > > there are no VBA components to remove, or that you have protected the VB Code
> > > with a password. I would try looking at the vb code: Alt +F11. If it asks
> > > for a password then you (or somebody) have protected the vb code, hence the
> > > msg.
> > > If this is the case, you can unprotect it or change the part of you code
> > > that gives you the message.
> > >
> > > 2. In the first part of you code you are sending a copy of your worksheet
> > > as email, THEN deleting the vb code. Usually, the vb code is removed BEFORE
> > > sending the email in order to protect your code or if the recipient has no
> > > need for it. Do you have some other reason for removing the code after
> > > sending the email? If not then move the Remove..... line up before the
> > > Sendmail line
> > >
> > >
> > >
> > > "Dannyboy1001" wrote:
> > >
> > > > Here is the macro in the button:
> > > >
> > > > Sub SendDocumentAsAttachment()
> > > >
> > > > Dim wb As Workbook
> > > > Dim strdate As String
> > > > Dim fac, disemail As String
> > > > strdate = Format(Now, "dd-mm-yy h-mm-ss")
> > > > Application.ScreenUpdating = False
> > > > ActiveSheet.Copy
> > > > Set wb = ActiveWorkbook
> > > > fac = wb.ActiveSheet.Range("B7").Value
> > > > disemail = wb.ActiveSheet.Range("Z7").Value
> > > >
> > > > With wb
> > > > .SaveAs "Copy of " & ThisWorkbook.Name _
> > > > & " " & strdate & ".xls"
> > > > If disemail = "" Then
> > > > .SendMail "[email protected]", _
> > > > "Cloverleaf Interface Request for " & fac
> > > > Else
> > > > .SendMail Array("[email protected]", disemail), _
> > > > "Cloverleaf Interface Request for " & fac
> > > > End If
> > > > RemoveAllMacros ActiveWorkbook
> > > > .Close True
> > > > End With
> > > > Application.ScreenUpdating = True
> > > > ActiveWorkbook.Close True
> > > >
> > > > End Sub
> > > >
> > > > Sub RemoveAllMacros(objDocument As Object)
> > > > ' deletes all VBProject components from objDocument
> > > > ' removes the code from built-in components that can't be deleted
> > > > ' use like this: RemoveAllMacros ActiveWorkbook ' in Excel
> > > > ' or like this: RemoveAllMacros ActiveWorkbookDocument ' in Word
> > > > ' requires a reference to the
> > > > ' Microsoft Visual Basic for Applications Extensibility library
> > > > Dim i As Long, l As Long
> > > > If objDocument Is Nothing Then Exit Sub
> > > > i = 0
> > > > On Error Resume Next
> > > > i = objDocument.VBProject.VBComponents.Count
> > > > On Error GoTo 0
> > > > If i < 1 Then ' no VBComponents or protected VBProject
> > > > MsgBox "The VBProject in " & objDocument.Name & _
> > > > " is protected or has no components!", _
> > > > vbInformation, "Remove All Macros"
> > > > Exit Sub
> > > > End If
> > > > With objDocument.VBProject
> > > > For i = .VBComponents.Count To 1 Step -1
> > > > On Error Resume Next
> > > > .VBComponents.Remove .VBComponents(i)
> > > > ' delete the component
> > > > On Error GoTo 0
> > > > Next i
> > > > End With
> > > > With objDocument.VBProject
> > > > For i = .VBComponents.Count To 1 Step -1
> > > > l = 1
> > > > On Error Resume Next
> > > > l = .VBComponents(i).CodeModule.CountOfLines
> > > > .VBComponents(i).CodeModule.DeleteLines 1, l
> > > > ' clear lines
> > > > On Error GoTo 0
> > > > Next i
> > > > End With
> > > > End Sub
> > > >
> > > >
> > > > "gocush" wrote:
> > > >
> > > > > Can you post the entire code which is behind the Button. Without it, it's
> > > > > quite hard to tell.
> > > > >
> > > > >
> > > > > "Dannyboy1001" wrote:
> > > > >
> > > > > > I have an Excel Spreadsheet that contains a button. In the button there is a
> > > > > > macro that uses the Sendmail VB command. It works in Window 2000 Office
> > > > > > 2000. Now, it does not work XP Office 2003. It goes into Debug mode and I
> > > > > > get the following error.
> > > > > >
> > > > > > The VBProject of the Copy of Facility Request form.xls is either protected
> > > > > > or has no compoents.
> > > > > >
> > > > > > Can it be fixed? Why do I get this error in XP Office 2003 and not in
> > > > > > Windows 2000 Office 2003?
> > > > > >
> > > > > > Thanks for you help in advance.
> > > > > >
> > > > > >