Outlook email loop

[xenedra13]

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?

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

 

[pbornemeier]

.Display shows the email, but it does not pause execution, so I expect you see a blur of messages whipping by and at the end of the code only see the last message.
.Save would save the email to the Drafts folder
.Send would send the email

 

[xenedra13]

Yes, that is exactly what happens. However, I need to display the emails so that I can check the contents before they are sent. There have been quite a few mistakes that I have caught that way. I am not sure why or what the logic is behind this, but I found an answer to my question. Each of the emails opened in a separate window after I moved this line

Set MyItem = myOlApp.CreateItemFromTemplate("File path" & "File name")

inside the "For I = 3 to 55" segment. If someone could explain why this makes each email open in a new window instead of overriding the email window before it, that would be great. I would like to understand why this works.

 

[pbornemeier]

With that line inside the loop a separate email is created each time that line is run.
With that line outside the loop a single email is created then rewritten each time the loop increments.

My earlier solution would not have worked since once the first email was sent (with .Send ) there would be nothing for the remainder of the code to work on.

If errors were caught afte the emails were generated, you may want to add some error checking routines to process the data before the original code is run.