I am making a macro to create an email for each line in a spreadsheet. This macro makes the emails, but it is not using the right loop because it replaces the previous email with the next one. So when the macro is finished, there is only 1 email instead of 53 separate emails. Can someone help with the right loop to make this work?
Code:
Dim myOlApp As Object
Dim MyItem As Object
Set myOlApp = CreateObject("Outlook.Application")
Set MyItem = myOlApp.CreateItemFromTemplate("File path" & "File name")
Dim body As String
For i = 3 To 55
If Workbooks(Client).Worksheets("Email Info").Cells(i, 1).Value = True Then
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "00/00/00", Format(Cells(i, 4), "m/dd/yyyy hh:mm"))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSP1", FormatNumber(Cells(i, 5), 0))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSP2", FormatNumber(Cells(i, 6), 0))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSLF%1", FormatPercent(Cells(i, 7), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSLF%2", FormatPercent(Cells(i, 8), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMLF%1", FormatPercent(Cells(i, 9), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMLF%2", FormatPercent(Cells(i, 10), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMLF%3", FormatPercent(Cells(i, 11), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMLF%4", FormatPercent(Cells(i, 12), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSCP%", FormatPercent(Cells(i, 13), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMCP%1", FormatPercent(Cells(i, 14), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMCP%2", FormatPercent(Cells(i, 15), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMCP%3", FormatPercent(Cells(i, 16), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "AMCP%4", FormatPercent(Cells(i, 17), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSO-P", FormatPercent(Cells(i, 18), 1))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSTkW1", FormatNumber(Cells(i, 19), 0))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SSTkW2", FormatNumber(Cells(i, 20), 0))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SDE1", FormatCurrency(Cells(i, 21), 0))
MyItem.HTMLBody = Replace(MyItem.HTMLBody, "SDE2", FormatCurrency(Cells(i, 22), 0))
With MyItem
.To = email address
.Subject = "Subject - " & Cells(i, 3)
.Display
End With
End If
Next i