Outlook email loop

xenedra13

Board Regular
Joined
Aug 15, 2014
Messages
58
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
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,848
.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

Board Regular
Joined
Aug 15, 2014
Messages
58
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
Code:
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

Well-known Member
Joined
May 24, 2005
Messages
3,848
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,609
Messages
5,523,885
Members
409,542
Latest member
Shezz01

This Week's Hot Topics

Top