Error Message Visual Basic 400 - help!

DitzySuz - Mar 23, 2009 at 08:13 PM
 khanhdaica - Mar 30, 2010 at 11:45 AM

I have a piece of code that automatically saves and sends a excel spreadsheet file (which is an empty spreadsheet send via email to people to fill in some details). However the code seems to work on some PCs and doesn't on others. A message comes up saying Microsoft Visual Baisc (a big red circle with a cross in it, then a number 400 in the middle of the message box). I don't know why it works sometimes and then it doesn't.

Does anyone know how to fix this problem?

Thank you in advance.

Sub SaveandSend()
ThisFile = Range("C6").Value
ThisFile1 = Range("C5").Value
ThisFile2 = Range("C3").Value
ActiveWorkbook.SaveAs Filename:="Survey - " & ThisFile & ThisFile1 & ThisFile2

'Working in 2000-2007
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "BM Survey"
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

3 replies

Blocked Profile
Mar 24, 2009 at 04:07 AM
Looks good to me :( don't see any errors in here...

Thanks for checking this for me. I run debug on a PC where the code would not work and it stopped at AciveWorkbook SaveAs FileName = "Survey - " & ThisFile1 & ThisFile2 & This File3, with a message saying "Application-defined or Object-defined error 1004". Do you think it could be because the Filename is too long for an older Excel version? (I noticed the code had worked on Excel 2003 but not Excel 2000).

Thanks for your help.
I was getting the same message and ended up tracing it back that I has some cell protected in the range. I un-proteced the cells and things are great once again.

Might be worth a try.
why my computer haven't File System Object in Library