Followup sheet - Macro

Hey JKPieterse, ty for your help!

In fact, I need a VBA macro cause with those actual replies, it would fill another column with "REPLIED / NOT REPLIED" and this would lead to another VBA code (inside the same button), for sending a followup e-mail. Kind of joining macros here to make as practical as I could.

After hours of trial and error, I got into somewhere that worked like that VLOOKUP you mentioned, but using VBA. I´ll add here in this message in case you want to see or if it might be useful for you.

Just for emphasizing, I had 3 tabs, with the first called Mailing, with the second called Follow1 and, at last, Follow2.

In case there is any doubt, feel free to contact me and I will gladly help! 🙂

Sub SendResponses()
Dim wsFollowup As Worksheet
Dim wsFollowup2 As Worksheet
Dim wsMailing As Worksheet
Dim name As String
Dim email As String
Dim subject As String
Dim status As String
Dim objOutlook As Object
Dim objEmail As Object
Dim i As Long
Dim emailTitle As String
Dim emailBody1 As String
Dim emailBody2 As String
Dim signature As String
Dim phone As String
Dim assessorEmail As String
Dim oldEmail As String
Dim oldTitle As String
Dim body1 As String
Dim body2 As String

' Define the Followup worksheet
Set wsFollowup = ThisWorkbook.Sheets("Followup 1")
Set wsMailing = ThisWorkbook.Sheets("Mailing")
Set wsFollowup2 = ThisWorkbook.Sheets("Followup 2")

' Initialize Outlook
Set objOutlook = getoutlook()

' Email title
emailTitle = wsFollowup.Range("K2").Value

' Email body part 1
emailBody1 = wsFollowup.Range("K3").Value

' Email body part 2
emailBody2 = wsFollowup.Range("K4").Value

' Signature
signature = wsFollowup.Range("K7").Value

' Phone
phone = wsFollowup.Range("K8").Value
wsFollowup.Range("K8").NumberFormat = "(##) #####-####"

' Old email
oldTitle = wsMailing.Range("k2").Value
body1 = wsMailing.Range("K3").Value
body2 = wsMailing.Range("k4").Value

' Assessor email
assessorEmail = wsFollowup.Range("K9").Value

' Iterate over each row in the Followup worksheet
For i = 2 To wsFollowup.Cells(wsFollowup.Rows.Count, "A").End(xlUp).Row
email = wsFollowup.Cells(i, 3).Value ' Column C of the Followup sheet (Email)
status = wsFollowup.Cells(i, 6).Value

' Check if the cell in column C contains an email and the status is "AWAITING"
If Len(email) > 0 And (status = "AWAITING" Or status = "REPLIED!") Then
name = wsFollowup.Cells(i, 1).Value ' Name in column A
nickname = wsFollowup.Cells(i, 2).Value ' Subject in column B

' Create email item for follow-up
Set objEmail = objOutlook.CreateItem(0) ' 0 represents olMailItem
If Not objEmail Is Nothing Then
With objEmail
.Display
.Sender = assessorEmail
.Subject = nickname & emailTitle
.Body = name & ", how are you?" & vbCrLf & vbCrLf & _
emailBody1 & vbCrLf & vbCrLf & _
emailBody2 & vbCrLf & vbCrLf & _
"Sincerely," & vbCrLf & _
signature & vbCrLf & _
phone & vbCrLf & _
assessorEmail & vbCrLf & _
"URL link" & vbCrLf & _
"Address" & vbCrLf & _
"São Paulo, SP" & vbCrLf & vbCrLf & _
" " & _
"___________________________________________________________________________________________________________________________________________________________________________________________________________" & vbCrLf & vbCrLf & _
"Subject: " & nickname & oldTitle & vbCrLf & vbCrLf & _
name & ", how are you?" & vbCrLf & _
body1 & vbCrLf & vbCrLf & _
"Sincerely," & vbCrLf
.To = email
'.Send
End With
Else
MsgBox "Unable to create email item.", vbCritical
End If

' Update the status in column F of the Followup sheet to "REPLIED!"
wsFollowup.Cells(i, 6).Value = "REPLIED!"
wsFollowup2.Cells(i, 6).Value = "REPLIED!"
End If
Next i

End Sub