How can I make the VBA code reference the active row it is is looping through and put that in email?

Copper Contributor
0

I am trying to create a macro that scans a column whenever opened to see if it has past so many days. If a cell has, then I would like it to write an email to me for each row in that column past the identifier. I would ideally like to send this email to different people from the emails of those people that would be referenced in the same row as the identifier and then send an email with specific information from that row. Here is my code so far(Attached).

Here is the code: Sub Ifree() Dim I As Integer I = 2 Do While ThisWorkbook.Sheets("Sheet1").Cells(I, 1).Value <> "" If ThisWorkbook.Sheets("Sheet1").Cells(I, 1).Value > 59 Then Dim OutApp As Object Dim OutMail As Object Dim strbody As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) strbody = "Hi there, row XYZ has passed 60 days, please take the necessary actions" On Error Resume Next With OutMail .To = "" .CC = "" .BCC = "" .Subject = "test" .Body = strbody 'You can add a file like this '.Attachments.Add ("C:\test.txt") .Send End With On Error GoTo 0 Set OutMail = Nothing Set OutApp = Nothing Else End If I = I + 1 Loop End Sub

 

Can someone please help instruct me how to move further.

0 Replies