VBA- how to check within a row within a range- completely stuck! :(

Hi guys- i'm quite new to vba and i've been following some of the guidance on this site which has been really useful. Unfortunately i'm just really stuck on something.
1.jpg

I have produced a spreadsheet where I enter checkmarks (as an "X") and it cycles through the columns and when it finds an X (with no "sent" alongside) it composes an email to an address on the same row in addition to adding a "sent" to the next column. My code is below

x = 9 'column I'

For x = 9 To 35

For Each Cell In Columns(x).Cells.SpecialCells(xlCellTypeConstants)

If Cell.Value = "X" And Cell.Offset(, 2).Value <> "sent" Then
Cell.Offset(, 2).Value = "sent"

Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Display
.to = Cells(Cell.Row, "E")
.CC = Cells(Cell.Row, "F")
.Subject =
.HTMLBody = strbody & Cells(Cell.Row, "D") & "," & "<br><br>" & strbody1 & Cells(3, Cell.Column) & strbody2 & .HTMLBody

.Display
End With
On Error GoTo 0
Set OutMail = Nothing
End If
Next Cell
Next x

This works really well but I need to add another thing which I just cant seem to do. In the email I now need to compile a list of the previous "X" in terms of the dates (in the displayed example it would be 01 Oct but if there were more than one X on the row all those dates need to be listed). I've tried some ideas but nothing seems to work within my existing code.

Any suggestions?

Hi, Thank you very much for responding.

Please find my excel sheet attached.

This sheet records absences over a period of several of weeks.
Each week I input the absences with a "X" and run the code. This checks the sheet for "X"'s then batch generates emails to absent individuals.
It also puts a "sent" next to the "X" so when the code is run again the absences already emailed are not picked up again.

The additional functionality i'm trying to add is that each email sent to an absentee should also contain a list of previous absences (if any) at the bottom but annoyingly all my attempts to do this have failed so far.

Please also find attached an image of what I ideally want to happen. Any advice or suggestions would be most appreciated!

Try this

Please Login or Register  to view this content.

This works perfectly! thank you so much!!