Hello,
I'm wondering if anyone can help me decipher what I've missed in my VBA code? I'm trying to write a For Each Loop that looks at the due date in Column J and sends out a reminder e-mail if the date is within 7 days.
The code works great the first time, but will not continue through the loop to look at the remaining dates in rows 4-26 of column J. It pops up with an error stating "Ru-time error '-2147221238 (8004010a)': The item has been removed or deleted."
When clicking on debug following this error, it highlights the code line where the e-mail to address is located. I've tried both hardcoding the address and using a range object, but neither seem to resolve the error message.
I've included the code has it reads currently below. Any help is greatly appreciated!!
Sub Email_From_Excel_Basic()
Dim emailApplication As Object
Dim emailItem As Object
Dim DateDueCol As Range
Dim DateDue As Range
Set DateDueCol = Range("J3:J26")
Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)
For Each DateDue In DateDueCol
If DateDue <> "" And DateDue >= DateDue - Range("T1") Then
emailItem.To = Range("V5").Value
emailItem.Subject = "DOT Scheduling Reminder"
emailItem.Body = "This is a reminder that the DOT for Trailer " & DateDue.Offset(0, -8) & " is due on " & DateDue & " . Please schedule a DOT for this trailer."
emailItem.Send
End If
Next DateDue
End Sub
Thanks again!
Amanda
Hi Amanda,
Please supply a sample workbook with your question, it can be very difficult to debug code without the data it's working on.
In this case though what you need to do is create a new emailItem in each loop, so move this line inside your FOR
Set emailItem = emailApplication.CreateItem(0)
Every time you send an email that emailItem is gone and you need to create a new one.
Regards
Phil
Hi Amanda,
Your code creates the emailitem BEFORE the loop.
After the first email is sent, the emailitem does not exist anymore, you cannot refer to the same object.
Move the line:
Set emailItem = emailApplication.CreateItem(0)
after the For Each line, you have to create an emailitem at EACH iteration.
Thank you both for you help!
I've implemented your suggestions into my code and it runs perfectly now.
Thanks again!
