Getting a 400 error when I try to run a macro that worked yesterday

[vipermaus]

I am trying to write a macro that automatically saves a copy of the spreadsheet and then attaches it to an email in preparation for sending it to the vendor. The thing is, the macro worked perfectly yesterday and now today, the macro returns error 400 when I try to run it from the spreadsheet, or error 1004 when I step into the code and try to run it from there. I am very perplexed by this and wish that I could figure out what is going on with it. I have made no modifications to the spreadsheet, the macro, or my machine since yesterday. It still runs on one of my coworker’s machine, but when I tried to run a copy of the spreadsheet on my machine, I got the same errors. I would really like to get this figured out quickly because I have to present my results to the President of the company on Beginning of next week!

Here is a copy of the code for the Macro. It is a heavily modified version one of Ron de Bruin's macros. Please ignore the commented lines of code, they are there for my reference.</SPAN>

Sub Email_link()
'Working in Office 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

thisfile = "RFQ for " & Range("h13").Value & " for " & Range("g11").Value _
& " " & Format(Now, "dd-mmm-yy")

' filepath = "o:\Quotes Folder\test run for new form\"

' "o:\Quotes Folder\" & Range("g11").Value & "\" & "RFQ for " & Range("h13").Value & " for " & Range("g11").Value _
& " " & Format(Now, "dd-mmm-yy") & ".xls"
' "t:\documents\my media\" & Range("g11").Value & "\" & "test" & Format(Now, hh - mm - ss)
' "RFQ for " & Range("h13").Value & " for " & Range("g11").Value _
& " " & Format(Now, "dd-mmm-yy hh-mm-ss")
' filepath = "\\aero-pdc\public\estimating\bids\test folder\"

'Application.Dialogs(xlDialogSaveAs).Show thisfile
ActiveWorkbook.SaveAs filename:="o:\Quotes Folder\test run for new form\" & thisfile & ".xls"
If ActiveWorkbook.Path <> "" Then
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
strbody = "Please respond at your earliest convienence with your best pricing and delivery." _
& "

Best regards,
" & Range("g6").Value

On Error Resume Next
With OutMail
.To = ""
.CC = ""
.BCC = ""
.Subject = ActiveWorkbook.Name
.HTMLBody = strbody
.Attachments.Add ActiveWorkbook.FullName
.Display 'or use .Send'
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
Else
MsgBox "The Activesheet does not have a path, Save the file first."
End If
End Sub

Any help would be greatly appreciated!

Thanks!
Matt

 

[wigi]

Care to tell us what line the error produces?

Put On Error Resume Next in commentary to not mask any errors.

 

[wigi]

Also, when debugging, concentrate on the essential and take out all unnecessary overhead. Use indenting as well:

Sub Email_link()'Working in Office 2000-2010


    thisfile = "RFQ for " & [H13] & " for " & [G11] & " " & Format(Now, "dd-mmm-yy")
    ActiveWorkbook.SaveAs "O:\Quotes Folder\test run for new form\" & thisfile & ".xls", 51


    On Error Resume Next
    With CreateObject("Outlook.Application").CreateItem(0)
        .To = ""
        .Subject = ActiveWorkbook.Name
        .HTMLBody = "Please respond at your earliest convienence with your best pricing and delivery. Best regards, " & [G6]
        .Attachments.Add ActiveWorkbook.FullName
        .Display    'or use .Send'
    End With
    On Error GoTo 0
    
End Sub

 

[vipermaus]

Thank you for such a quick response, wigi, but I still get the same error message whenever I try to run it and the code worked flawlessly yesterday; could it possibly be an excel setting that needs to tweaked?

Thanks,
Matt

 

[wigi]

It's logical that the same error is shown, functionally, I did not change your existing code listing.
For the second time (probably the last time), what line produces the error?
No Excel settings should be tweaked. You said that it worked earlier and nothing has changed.

 

[vipermaus]

I believe that it is the save as function.

ActiveWorkbook.SaveAs filename:="o:\Quotes Folder\test run for new form\" & thisfile & ".xls"

thank you for your paitence,
Matt

 

[James Snyder]

What is coming in from H13 and G11?

 

[James Snyder]

Also, add a variable or MsgBox to catch the error description:

Err.Description

 

[James Snyder]

XL2000: ShowDataForm Method Fails if Data Cannot Be Found

 

[vipermaus]

What is coming in from H13 and G11?

Ok James, this is really weird, but H13 is a data validation form, and I think that I read somewhere that using that to populate a list was the fastest and easiest way to create and link a list to an VBA project. Now the weird part is that as soon as I changed what the form was at when I opened it, the macro worked flawlessly! So obviously the problem is with H13, so now my question is, what could cause that and how do I keep it from randomly happening in the future?

Best regards and warmest thanks,
Matt