Try this and let me know how you go:

Sub GenerateCodes()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentRow As Long
    Dim code As String
    Dim counter As Long
    Dim attachmentCounter As Long


    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Replace "Sheet1" with the name of your worksheet
    lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row


    counter = 1
    For currentRow = 2 To lastRow
        If InStr(ws.Cells(currentRow, "B"), "Attachment") = 0 Then
            code = Format(counter, "000")
            ws.Cells(currentRow, "D").Value = code
            attachmentCounter = 65 ' ASCII value of "A"
            counter = counter + 1
        Else
            ws.Cells(currentRow, "D").Value = code & Chr(attachmentCounter)
            attachmentCounter = attachmentCounter + 1
        End If
    Next currentRow


End Sub
Before running the macro, make sure to replace "Sheet1" with the name of your worksheet in the Set ws = ThisWorkbook.Worksheets("Sheet1") line of code.

In summary, this macro goes through each row and checks if the word "Attachment" is present in column B. If it's not present, it assigns a three-digit code to column D. If the word "Attachment" is present, it assigns the parent's code plus a letter (A, B, C, etc.) to column D. The macro considers multiple layers of attachments when generating codes.