E-mail from Excel - macro errors when copied

[fangfacekitty]

Working in Excel 2010 I have a workbook I'll call Group 1. There are 11 available worksheets, and anywhere from 1-11 of these sheets are assigned to a different company name. The worksheets themselves are simply numbered 1-11, the company names are always referenced in cell D3. I am not going to change this for various reasons.

I have a macro that copies each worksheet into a new workbook and saves as the name in cell D3. The new workbooks are saved to the same folder. I am using one of Ron de Bruins macros for sending e-mails from excel, which is saved in the original master workbook Group 1. On my SendFiles tab I have the columns set up as needed, with the e-mail addresses in column B and the file path/name in column C.

My problem comes when I make a copy of the workbook Group 1 and rename it Group 2. The new workbook has a different group of company names and e-mail addresses. I updated the macro to reflect the new company names and the new workbook name. Except now I get a "Run-Time error "53", file not found" and the debug takes me to the red text in the macro below.

Except the file DOES exist, it is NOT a typo because I copy and paste the file name and path so it is exact. I can manually navigate to the location and access the file. I am still testing so the e-mail comes to me, so I know it is not erroring out because of a faulty e-mail either.

Because the company names are unique I know which one is causing the error and if I remove the e-mail address in column B for that specific company the macros work again and everything gets sent except for that 1 company.

Any ideas on why this is happening?

Again the only thing that changes are the workbook names and I am updating those in the macros (I've tried manually and with find/replace) by copying/pasting the exact names & file paths instead of manually typing them.

Sub Email()
'
' Email_Files Macro
' Macro recorded 3/6/2012 by piercer
'
'Working in 2000-2010
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
Dim sh As Worksheet
Dim cell As Range, FileCell As Range, rng As Range
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
Set sh = Sheets("SendFiles")
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
 
strbody = "Dear Company," & vbNewLine & vbNewLine & _
"Attached please find your results." & vbNewLine & _
"Please contact me if you have any questions." & vbNewLine & _
"If actions are required you will be contacted." & vbNewLine & _
" " & vbNewLine & _
"Regards," & vbNewLine & _
" " & vbNewLine & _
"Me"
 
For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)
'Enter the file names in the C:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("C1:Z1")
If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)
With OutMail
.to = cell.Value
.Subject = "Results " & cell.Offset(0, -1).Value
.Body = strbody & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine & vbNewLine
For Each FileCell In rng.SpecialCells(xlCellTypeConstants)
If Trim(FileCell) <> "" Then
If Dir(FileCell.Value) <> "" Then
.Attachments.Add FileCell.Value
End If
End If
Next FileCell
.Send 'Or use Display
End With
Set OutMail = Nothing
End If
Next cell
Set OutApp = Nothing
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
'
End Sub

 

[fangfacekitty]

I found the answer & thought I'd post here in case any one else runs into the same problem. Basically I was telling the macro to look for files in more columns than I was using for file paths and was putting other text in the unused columns. The macro sometimes thought that other text was a file path & errored out when looking for it.

Once I updated the columns in the macro to show C:G only (instead of C:whatever I had originally) the problem went away.