Loop help

Hi all,

I’ve been looking to send out reminders automatically from my Action Tracker spreadsheet to all actionees. I assumed this would be a common use of excel and that I would be able to find a piece of code that I could then bastardise. Which I did but can’t seem to add the action text itself to the email.

I’ve used the code from: https://www.rondebruin.nl/win/s1/outlook/bmail5.htm

and it works great but I can’ add the text on the cells in column “G” to the email. I.e. The code adds all the text kept in the G1:G20 where I need it to add the content of G2 to email to person A2, and so on.

Any help would be greatly appreciated.


Sub Test2()
‘For Tips see: http://www.rondebruin.nl/win/winmail/Outlook/tips.htm
‘Working in Office 2000-2016
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range

    Application.ScreenUpdating = False
    Set OutApp = CreateObject(“Outlook.Application”)

 Dim strbody As String
     For Each cell In Range(“G1:G20”)
         strbody = strbody & cell.Value & vbNewLine

    On Error GoTo cleanup
    For Each cell In Columns(“B”).Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like “?*@?*.?*” And _
           LCase(Cells(cell.Row, “C”).Value) = “yes” _
           And LCase(Cells(cell.Row, “D”).Value) <> “send” Then

            Set OutMail = OutApp.CreateItem(0)

            On Error Resume Next
            With OutMail
                .To = cell.Value
                .Subject = “Reminder”
                .Body = “Dear ” & Cells(cell.Row, “A”).Value & vbNewLine & vbNewLine & strbody
                ‘You can add files also like this
                ‘.Attachments.Add (“C:test.txt”)
                .Send  ‘Or use Display
            End With
            On Error GoTo 0
            Cells(cell.Row, “D”).Value = “send”
            Set OutMail = Nothing
        End If
    Next cell

    Set OutApp = Nothing
    Application.ScreenUpdating = True
End Sub

By: john

Leave a Reply

Your email address will not be published. Required fields are marked *