BamaColtsFan
Registered User.
- Local time
- Today, 13:41
- Joined
- Nov 8, 2006
- Messages
- 91
Well, its a new week and we have new mysteries. For me, the mystery is why my code below keeps giving me the error "Run-time error '3265': Item not found in the collection." Please note that the debug shows me that the error occurs on the specific lines are where I'm replacing [[Grp]] and [[Type]] with their related values in my query. I looked up the 3265 error and, in general, it means that the referenced object is not part of the called query. The mystery is that those two columns ARE in the query. If I take out those two lines, the code runs perfectly. I've checked and rechecked the column names in the query. I've double checked the text file to make sure there are no typo's there. I just can't see where the error is... Any suggestions?
Code:
Public Function SendEMail()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim MyNewBodyText As String
Dim newPath As DAO.Recordset
Dim strPath As String
Dim strFileName As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")
strFileName = "Mail Merge - Mail Test.txt"
strPath = newPath!path & strFileName
Set fso = New FileSystemObject
Subjectline$ = "Daily Status"
If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If
BodyFile$ = strPath
If BodyFile$ = "" Then
MsgBox "No body, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain’t Got No-Body!"
Exit Function
End If
If fso.FileExists(BodyFile$) = False Then
MsgBox "The body file isn’t where you say it is. " & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "I Ain’t Got No-Body!"
Exit Function
End If
Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)
MyBodyText = MyBody.ReadAll
MyBody.Close
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
Set MailList = db.OpenRecordset("MyEmailAddresses")
Do Until MailList.EOF
MyNewBodyText = MyBodyText
MyNewBodyText = Replace(MyNewBodyText, "[[Name]]", MailList("Name"))
MyNewBodyText = Replace(MyNewBodyText, "[[Status]]", MailList("Status"))
MyNewBodyText = Replace(MyNewBodyText, "[[End]]", MailList("Timecard Stop Date"))
MyNewBodyText = Replace(MyNewBodyText, "[[Dpt]]", MailList("Dept"))
MyNewBodyText = Replace(MyNewBodyText, "[[Title]]", MailList("Title Rank"))
MyNewBodyText = Replace(MyNewBodyText, "[[Name2]]", MailList("Name"))
MyNewBodyText = Replace(MyNewBodyText, "[[Grp]]", MailList("People Group"))
MyNewBodyText = Replace(MyNewBodyText, "[[Appv]]", MailList("Time Approver"))
MyNewBodyText = Replace(MyNewBodyText, "[[Type]]", MailList("Person Type"))
MyNewBodyText = Replace(MyNewBodyText, "[[Late]]", MailList("DaysLate"))
Set MyMail = MyOutlook.CreateItem(olMailItem)
MyMail.To = MailList("email")
MyMail.Subject = Subjectline$
MyMail.Body = MyNewBodyText
MyMail.Display
MailList.MoveNext
Loop
Set MyMail = Nothing
Set MyOutlook = Nothing
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
End Function
