mymail.subject error

[dark11984]

Hi,
I'm trying to setup and email to automatically send on a specific date each month. Tehn once the email sends i want to log it in a table that ive created.

I've got the email to send alright but once i get to the logging part i get an error on mymail.subject - "The item has been moved or deleted."

MyTrackingTable.AddNew
        MyTrackingTable("LogDate") = Date
        MyTrackingTable("Subject_Task") = MyMail.Subject
        MyTrackingTable("Loguser") = GetUserName()
        MyTrackingTable.Update

 

[boblarson]

I think we need the rest of your code in order to tell, but it is likely that because the mail was sent, the mail item doesn't exist anymore. So you will want to capture the item data into variables BEFORE the mail is sent and then retrieve the data using those variables instead.

 

[dark11984]

Thanks for your reply. How would i capture the item data into a variable?

Public Function LRAuditMail()
 
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 SubmitDate As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String
Dim CurrentMth As String
Dim MyDate1 As Date
Dim MyDate2 As Date
Dim MyTrackingTable As Recordset
 
Set fso = New FileSystemObject
 
Set MyOutlook = New Outlook.Application
Set db = CurrentDb()
 
Set MailList = db.OpenRecordset("tblmailingList")
Set MyTrackingTable = CurrentDb.OpenRecordset("TblDBTracking")
Set MyMail = MyOutlook.CreateItem(olMailItem)
 
CurrentMth = Format(Date, "Mmm yyyy")
SubmitDate = LastWorkDay()
MyDate1 = WeekBeforeEOM
MyDate2 = TwoDaysBeforeEOM
 
    If DCount("[LogDate]", "TblDBTracking", "[Subject_Task] like 'LR Audits reminder*' AND [LogDate]= #" & Date & "#") < 1 Then
 
 
            MyMail.To = DLookup("[email]", "tblMailingList", "[distributename] = 'LoadRestraint'")
            If Date = MyDate1 Then
 
                MyMail.Subject = "LR Audits - " & [CurrentMth]
 
                MyMail.Body = "Test1."
 
            ElseIf Date = MyDate2 Then
 
                MyMail.Subject = "LR Audits reminder - " & [CurrentMth]
 
                MyMail.Body = "Test2."
 
            Else
                Exit Function
            End If
 
 
        MyMail.Send
 
        MyTrackingTable.AddNew
        MyTrackingTable("LogDate") = Date
        MyTrackingTable("Subject_Task") = MyMail.Subject
        MyTrackingTable("Loguser") = GetUserName()
        MyTrackingTable.Update
 
 
    Else
        Exit Function
    End If
 
Set MyMail = Nothing
 
Set MyOutlook = Nothing
 
MailList.Close
Set MailList = Nothing
db.Close
Set db = Nothing
 
 
End Function

 

[boblarson]

You already have a variable there called Subjectline which appears to be unused. You can just change this part:

MyMail.Subject = "LR Audits - " & [CurrentMth]

to this:

Subjectline = "LR Audits - " & [CurrentMth]
MyMail.Subject = Subjectline

And then down below use:

MyTrackingTable("Subject_Task") = Subjectline

 

[dark11984]

I'ts a beautiful thing! Thanks for your help.

 

[boblarson]

Glad to be able to help.