Every time I run this macro I get a run-time 5 error 'Invalid procedure call or argument' on each of the marked lines. What am I doing wrong???
Code:
Dim Cell As Range, Rng As Range
Dim Body As String, i As Integer
Dim JuneTemplate As String, JulyTemplate As String
Dim myOlApp As Object, MyItem As Object
Set myOlApp = CreateObject("Outlook.Application")
Set Rng = Range("A6:A57")
JuneTemplate = FILEPATH & FILENAME
JulyTemplate = FILEPATH & FILENAME
For i = 6 To 57
For Each Cell In Rng
'If Checkboxes are checked...
If Cells(i, 2) = True Then
'Choosing June or July template...
If Cells(i, 3).Value = "June" Then
Set MyItem = myOlApp.CreateItemFromTemplate(JuneTemplate)
ElseIf Cells(i, 3).Value = "July" Then
Set MyItem = myOlApp.CreateItemFromTemplate(JulyTemplate)
End If
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "CTD%1", FormatPercent(Abs(Cells(i, 7)) / (Cells(i, 8)) - 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "CTD%2", FormatPercent(Abs(Cells(i, 8)) / (Cells(i, 9)) - 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$R-PC1", FormatCurrency(Cells(i, 10)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$R-PC2", FormatCurrency(Cells(i, 11)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$CTD1", FormatCurrency(Cells(i, 12)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$CTD2", FormatCurrency(Cells(i, 13)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$NI1", FormatCurrency(Cells(i, 14)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$NI2", FormatCurrency(Cells(i, 15)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$CNI1", FormatCurrency(Cells(i, 16)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "$CNI2", FormatCurrency(Cells(i, 17)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "PC%1", FormatPercent(Cells(i, 18)), 0) 'ERROR HERE
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "PC%2", FormatPercent(Cells(i, 19)), 0) 'ERROR HERE
