Batch Records On Daily Basis

[scottian]

I need a way to send an e-mail to certain bods with records from my database once they have been verified and accepted by the user. At present, the user works through each record and if the record is accepted then an e-mail is sent to the user advising. this creates one e-mail per record accepted. Is there a way to accept but have the database batch the accepted records and then send one e-mail with all the records. Id prefer to have the details form part of the message body, but if this is not possible then an attachement would suffice. Does anyone know how this could be done? I know how to automate the e-mail, and i know how to trigger the event to run only once per day, its just the compiling of the records.

"My God! It's full of stars...

 

[easyit]

Hi,

Can you create a (yes/no) field in the table for each record, which instead of sending an email will be set to true? Then you can create a query to select the accepted records, and send that as an attachemnt (ie Excel).


EasyIT

"Do you think that’s air you're breathing?

 

[scottian]

The problem i have with using attachments is that records are sent to another department to be attached to problem issues. so if the user recieves 2 or 3 records which need to be associated with a particular problem then one mail would be sent. this is the same if only one record needs to be associated. So to use a spreadsheet when only one record is to be attached is a bit of overkill. Thats why id prefer if there is a way of placing the record/records within the body of the mail.

"My God! It's full of stars...

 

[easyit]

Use oulook object:

Public Function SendEmail(TeamName As String, FileName As String)
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    Dim myO As Outlook.Application
    Dim mobjNewMessage As Outlook.MailItem

    With rst
        .Open "EA05_q_mailinglist", CurrentProject.Connection, adOpenStatic, adLockOptimistic
        .MoveFirst
        Do While Not .EOF
            If .Fields("teamname") = TeamName Then
                Set myO = CreateObject("Outlook.Application")
                Set mobjNewMessage = myO.CreateItem(olMailItem)
                mobjNewMessage.Subject = "Worklist  " & Format(Date, "dd-mm-yy") & "."
                mobjNewMessage.Body = "This mail copntains blabla " & Format(Date, "dd-mm-yyyy") & "." & Chr(13) & "From record." & "  " & .Fields("email") & " " & TeamName
                mobjNewMessage.To = .Fields("email")
                mobjNewMessage.Attachments.Add FileName
                mobjNewMessage.Display
            End If
            .MoveNext
        Loop
        .Close
    End With
    Set rst = Nothing

End Function

EasyIT

"Do you think that’s air you're breathing?

 

[scottian]

Got it.....just in case anyone is interested.

Not sure if anyone knows of a smoother or more efficient way. But feel free to rip it to bits.

'=====================================================
'=====================================================
Option Compare Database
Option Explicit

Function BatchIssues()

Dim db As Database
Dim strEmail As String
Dim strBody As String
Dim strSubject As String
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim rec As Recordset
Dim strsql As String

strsql = "SELECT (Your SQL Here);"

Set db = CurrentDb()
Set rec = db.OpenRecordset(strsql)

Do Until rec.EOF
strBody = strBody & " == " & rec![AllocatedTo] & vbCrLf
rec.MoveNext
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
Loop

strEmail = "my@emailaddress.com"
strSubject = "Problem Report, Please attach the following records to Problem Number 01"

With objEmail
.To = strEmail
.SentOnBehalfOfName = "MyTeam"
.Subject = strSubject
.Body = strBody
.Importance = olImportanceHigh
.Send
End With
rec.Close
End Function



"My God! It's full of stars...

 

[PHV]

Why creating the outlook objects inside the loop ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[easyit]

Ouch! Should have seen that myself.



EasyIT

"Do you think that’s air you're breathing?

 

[scottian]

Not sure i understand what you mean by this :-

Why creating the outlook objects inside the loop ?

"My God! It's full of stars...

 

[PHV]

Not sure i understand what you mean
Set rec = db.OpenRecordset(strsql)
[!]Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)[/!]
Do Until rec.EOF
strBody = strBody & " == " & rec![AllocatedTo] & vbCrLf
rec.MoveNext
[!]Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)[/!]
Loop



Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886