How to make a loop that will incrementally add lines to an email body if a certain condition is met?

Question

So I'm really new to VBA (and by new I mean a couple of days in). I'm looking to make a loop that will incrementally add lines to an email body if a certain condition is met. I apologise in advance if it is horrible to read but it does seem to work so far! If anyone can tell me how I can add something to the loop so that it adds a new line to the email body every time the condition is met, I would appreciate it.

Here's what I have so far:

Sub SendEmailReminder()
      Dim x As Integer
      Dim Removal As String
      Dim RemovalTitle As String


      Removal = Removal
      RemovalTitle = RemovalTitle
      ' Set numrows = number of rows of data.
      numrows = Range("C2").End(xlDown).row - 1
      ' Select cell 2.
      Range("C2").Select
      ' Establish "For" loop to loop "numrows" number of times.
      For x = 1 To numrows
         If ActiveCell = Date - 30 Then
         Removal = ActiveCell.Offset(0, -2)
         RemovalTitle = ActiveCell.Offset(0, -1)

        Dim OutApp As Object
        Dim OutMail As Object
        Dim strbody As String

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

        strbody = Removal & " - " & RemovalTitle & " needs to be removed from New Releases"


        On Error Resume Next
        With OutMail
            .To = ""
            .CC = ""
            .BCC = ""
            .Subject = ""
            .Body = strbody
            .Display   'or use .Send
        End With
        On Error GoTo 0

        Set OutMail = Nothing
        Set OutApp = Nothing
         End If
         ' Selects cell down 1 row from active cell.
         ActiveCell.Offset(1, 0).Select
    Next
   End Sub

You shouldn't use the `ActiveCell` or `Select` [here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) a topic on how to avoid it. `Integer` shouldn't be used in excel VBA, use `Long` instead, because `Integer` is actually a `Long` truncated. To finish, use the loop to build your `strbody` and then insert that variable to your mail, do not loop through the whole procedure. — Damian, Jun 09 '20 at 17:17

score 2 · Answer 1 · answered Jun 09 '20 at 17:24

You can do this:

Sub SendEmailReminder()
    Dim x As Integer
    Dim c As Range
    Dim OutApp As Object
    Dim strbody As String

    Set c = Range("C2")
    'loop while cell is not empty
    Do While Len(c.Value) > 0
        If c.Value = Date - 30 Then
            'build the message
            strbody = strbody & vbCrLf & c.Offset(0, -2) & " - " & _
                c.Offset(0, -1) & " needs to be removed from New Releases"
        End If
        Set c = c.Offset(1, 0) 'next cell
    Loop

    Set OutApp = CreateObject("Outlook.Application")
    On Error Resume Next
    With OutApp.CreateItem(0)
        .To = ""
        .CC = ""
        .BCC = ""
        .Subject = ""
        .Body = strbody
        .Display   'or use .Send
    End With
    On Error GoTo 0
    Set OutApp = Nothing

End Sub

Thank you so much - absolute legend! – Ben Roberson Jun 09 '20 at 17:26 — Ben Roberson, Jun 09 '20 at 17:26

How to make a loop that will incrementally add lines to an email body if a certain condition is met?

1 Answers1