Repeat macro for next row

Hi Team,

I've got a macro that will create an email based on the active cell and inputting relative content in to the email from the worksheet

As it stands, it only works on the activecell. How would I change my coding so that it does the active cell, then goes to the next row (same column) and repeats until the end.

Here's the code (I've had to strip out some of the HTML coding)

Sub Training_Enrolment_email()
' Working in Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    strbody = "Dear " & ActiveCell.Offset(, -2) & ",<br/><br/>" & _
"This is an email<br/><br/>" & _
"In the body of the email will be <b>Colours for Deadline dates and other formatting<br/><br/>" & _
"GOOD LUCK and enjoy the training!<br/><br/>" & _
"Kindest Regards,"

    On Error Resume Next

    With OutMail
        .Display
        .SentOnBehalfOfName = "Name of mailbox"
        .To = ActiveCell
        .CC = ""
        .BCC = ""
        .Subject = "Mandatory Training"
        .HTMLBody = strbody & "<br>" & .HTMLBody
        .Display '.Send will be used
    
    End With

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    'Turn back on screen updating
    Application.ScreenUpdating = True
    
 
End Sub

In the example, I would want an email to be formulated for Fred Bloggs, then another for Joe Soap then another for "Another Person" through to end.

Any help would be greatly appreciated.

Try:

Sub Training_Enrolment_email()
    ' Working in Office 2000-2016
    Dim OutApp      As Object
    Dim OutMail     As Object
    Dim strbody     As String

    Set OutApp = CreateObject("Outlook.Application")

    Do Until IsEmpty(ActiveCell.Value)
        Set OutMail = OutApp.CreateItem(0)

        strbody = "..." <-- Insert relevant HTML code

        On Error Resume Next

        With OutMail
            .Display
            .SentOnBehalfOfName = "Name of mailbox"
            .To = ActiveCell
            .CC = ""
            .BCC = ""
            .Subject = "Mandatory Training"
            .HTMLBody = strbody & "<br>" & .HTMLBody
            .Display    '.Send will be used

        End With
        ActiveCell.Offset(1).Select
    Loop

    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing

    'Turn back on screen updating
    Application.ScreenUpdating = True

End Sub

Artik

sintek, I wonder if With CreateObject("Outlook.Application") shouldn't be before the For Each loop.

Artik

Artik and sintek,

Thank you so much, both suggestions work and achieve what I asked (I'll rep you guys up shortly)

I have to say there is absolutely NOTHING wrong with sintek's solution, but I'm going to go with Artik's. The reason being that, whether by accident or design, it includes an ability for me to select a certain cell within a row and the macro operates from this point.

If for example I have an original list of 100 people and send the email to them. Then, at a later date, I add another 50 people (effectively rows 101 - 150), I can select the appropriate cell on row 101, run the macro and it will work from that row onward. This could prove to be invaluable.

Thank you both again.

Kindest Regards

MagicMan

MagicMan, you can also use the sintek code slightly modified

Sub Training_Enrolment_email_Sintek2()
    Dim StrBody As String, cell As Range

    With CreateObject("Outlook.Application")
        
        For Each cell In Range("A" & ActiveCell.Row & ":A" & Cells(Rows.Count, 1).End(xlUp).Row)

            StrBody = "Whatever"

            With .CreateItem(0)
                .Display
                .SentOnBehalfOfName = "Name of mailbox"
                .To = cell.Offset(, 2)
                .Subject = "Mandatory Training"
                .HTMLBody = StrBody & "<br>" & .HTMLBody
                '.Display
            End With
            
        Next cell
        
    End With
End Sub

It will be even safer as you do not have to select the cell in column C. You can select in any other column. As important is that the active cell is not in a row below the data range.

Artik