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

 

[James Snyder]

This is just a stab in the dark since I know I am not seeing all the code, but for the Range("H13"), where is the sheet and workbook that this belongs to. Is it in a "With Workbook" block?

 

[vipermaus]

That is a good point, I don't think it is! I'm including the all of the code for the macro, minus the comments. Maybe that will help.

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

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


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." _
& "<br><br>Best regards,<br>" & 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

Hopefully this will help!

Many thanks!
Matt

 

[wigi]

These cell values are taken from the active sheet.
You would better qualify the ranges appropriately, so that they are correct.
Or, add in a test on the activesheet when executing the macro: if it's not the sheet(s) you anticipate, end the macro.

 

[vipermaus]

That actually should not matter because there is only one sheet in the workbook. I actually ended up adding a selection to the the top of each list marked "select one" so people had no choice but to change the data validation and thus keep the error from appearing. Thanks to all who helped, as it really solved my problem very well.

-Matt