Excel SendMail Not working in XP Office 2003

[Guest]

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.

 

[Guest]

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

 

[Guest]

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 "(e-mail address removed)", _
"Cloverleaf Interface Request for " & fac
Else
.SendMail Array("(e-mail address removed)", 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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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