Errors with sending different pdf attachments to different recipients - VBA

[KGIL789]

Hello all,

I'm attempting to help a coworker automate part of her daily tasks. A PDF named in the same format is sent to different people about once per day. IE Person A gets his company's invoice, Person B gets his company's invoice...etc, etc. Invoices are all named in the same fashion - MMDDYYY-Company-InvoiceNo.

Right now I have the following spreadsheet:
A: Company Name
B: Email address
C: other contact info
D: File Base location (C:\users\(username)\Documents\)
E: Date (entered in as 11/6/2018)
F: Date (to convert to 11062018)
H: File location: uses this formula: =D2&""&F2&"-"&A2&"-"&G2&".pdf" to form file pathway

Then I have a button that links to this macro:

Sub Send_Files()
'Working in Excel 2000-2016
'For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
Dim OutApp As Object
Dim OutMail As Object
Dim sh As Worksheet
Dim cell As Range
Dim FileCell As Range
Dim rng As Range


With Application
.EnableEvents = False
.ScreenUpdating = False
End With


Set sh = Sheets("Sheet1")


Set OutApp = CreateObject("Outlook.Application")


For Each cell In sh.Columns("B").Cells.SpecialCells(xlCellTypeConstants)


'Enter the path/file names in the H:Z column in each row
Set rng = sh.Cells(cell.Row, 1).Range("H1:Z1")


If cell.Value Like "?*@?*.?*" And _
Application.WorksheetFunction.CountA(rng) > 0 Then
Set OutMail = OutApp.CreateItem(0)


With OutMail
.to = cell.Value
.Subject = "Testfile"
.Body = "Hi " & cell.Offset(0, -1).Value


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


.Display '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


I keep getting an 1004 error on "For Each FileCell In rng.SpecialCells(xlCellTypeConstants)". All I need this excel file to do is that IF there is an invoice number entered, to send the corresponding file to the email.

Appreciate the help!!

****** id="cke_pastebin" style="position: absolute; top: 1128px; width: 1px; height: 1px; overflow: hidden; left: -1000px;"> For Each FileCell In rng.SpecialCells(xlCellTypeConstants)

 

[KGIL789]

Forgot G - which is the invoice number.