I have this code below. Whenever I send an email, I want to put a "Y" in the corresponding row with a unique code that I entered. As I go through this code, I get to the line If item_in_review = Sheets("Data").Range("K" & row_number), but it keeps saying the clipboard is empty, but the excel sheet shows that it has been copied. How can I change this?
Code:
Private Sub SendMail_Click()
row_number = 0
Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Set olApp = New Outlook.Application
For i = 5 To Sheet7.Cells(Rows.Count, 4).End(xlUp).Row
Set olMail = olApp.CreateItem(olMailItem)
With olMail
.To = Cells(i, 6).Value
.Subject = "You Have a Delivery"
.Body = "Hello " & Cells(i, 3) & vbNewLine & vbNewLine & "There is " & Cells(i, 4).Value & " for you in the plan room from " & Cells(i, 5).Value & "."
.Display
End With
Next
Set olMail = Nothing
Set olApp = Nothing
For i = 5 To Sheet7.Cells(Rows.Count, 4).End(xlUp).Row
Cells(i, 7).Copy
Do
DoEvents
row_number = row_number + 1
item_in_review = Sheets("Data").Range("K" & row_number)
If item_in_review = Clipboard Then
Sheets("Data").Range("Q" & row_number) = "Y"
End If
Loop Until item_in_review = ""
Next
End Sub
Last edited: