I am using VBA code. Refer below.
Public Sub Emails()
'Variable declaration.
Dim db As Database, rst As Recordset, driver As String, email As String, qdf As QueryDef
Dim rsta As Recordset, strSQL As String, strQueryName As String, strESubject As String
Dim strCustEmail As String, strBody As String
Dim OutApp As Object, OutMail As Object
'Dim qdf As QueryDef
Set db = CurrentDb
'Run Driver Email Addresses which produces a dynaset of drivers and their email addresses.
DoCmd.OpenQuery "DRIVER EMAIL ADDRESSES", acViewNormal, acReadOnly
strQueryName = "Vehicles"
'Initialise values.
Set rst = db.OpenRecordset("DRIVER EMAIL ADDRESSES", dbOpenDynaset)
rst.MoveFirst
'Loop through Driver Email Address table to capture Driver and Email Address for production of cars held
'by that driver and to create an email using that address.
Do
driver = rst("Driver")
email = rst("Email")
' Refresh QueryDefs collection.
db.QueryDefs.Refresh
'Debug.Print driver, email
'Determine cars allocated to the driver
'While Not rst.EOF
strSQL = ("SELECT DECLARATIONS.DRIVER,DECLARATIONS.REGO, DECLARATIONS.DATEFROM, DECLARATIONS.DATETO " & _
"FROM DECLARATIONS " & _
" WHERE (((DECLARATIONS.DRIVER) = """ & driver & """));")
'Wend
'Debug.Print rsta!Rego, rsta!DateFrom, rsta!DateTo
Set qdf = CurrentDb.QueryDefs(strQueryName)
qdf.SQL = strSQL
DoCmd.OutputTo acOutputQuery, "Vehicles", acFormatXLS, "D:\TAX\CARS\Vehicles.XLS"
'******* end query def update ********
strESubject = "FBT Pool Car Declarations YTD September 2011"
'during testing override the cust email destination with your email
strCustEmail = driver
strBody = "All fringe benefits provided to staff must be reported against the employee's APS number to facilitate the reporting of certain benefits on employee group certificates under the Fringe Benefit Tax Reporting Legislation." & vbCrLf & vbCrLf & _
"According to the SAPFLEET system, the pool cars on the attached Vehicles file have been provided to your area for the period indicated. Where the vehicle has been home garaged during the period, an FBT Pool Car Allocation declaration should be completed indicating the apportionment of the private use among employees so the above FBT requirements can be met. Where the vehicle has been garaged overnight at an AP facility or commercial car park during the period, a No Private Usage Declaration should be completed indicating where the vehicle was garaged." & vbCrLf & vbCrLf & _
"Please note only one declaration is required for each vehicle shown below." & vbCrLf & vbCrLf & _
"Blank declarations are attached for your use. The appropriate declaration(s) should be completed for each vehicle listed, scanned and emailed to the Tax Helpline by 8th November, 2011. A hard copy should be forwarded where scanning facilities are not available. It is recommended that you retain a copy for your own records. It should be noted that the percentage allocations amongst the users MUST sum to 100%." & vbCrLf & vbCrLf & _
"Transport branch should be advised promptly of all changes of custodianship of pool cars to ensure that these requests will be accurate." & vbCrLf & vbCrLf & _
"Regards" & vbCrLf & vbCrLf & _
"David McIlveen" & vbCrLf & _
"Tax Analyst" & vbCrLf & _
"Australia Post" & vbCrLf & _
"Financial Control & Governance - Finance & Business Services" & vbCrLf & _
"Level 4, 219 - 241 Cleveland Street, Strawberry Hills, NSW, 1427" & vbCrLf & vbCrLf & _
"P 02 9202 6324 | F 02 9202 6887" & vbCrLf & _
"E
david.mcilveen@auspost.com.au | W auspost.com.au"
'create the email w/ attached rtf of query
'last parameter allows editing of the email before sending
'to send without viewing email use: False
'DoCmd.SendObject acSendQuery, strQueryName, acFormatRTF, strCustEmail, , , strESubject, strBody, True
'move to next record
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
With OutMail
.Subject = strESubject
.To = strCustEmail
.Body = strBody
.Attachments.Add ("D:\TAX\CARS\Vehicles.XLS")
.Attachments.Add ("D:\TAX\CARS\FRINGE BENEFITS POOL CAR ALLOCATION DECLARATION.DOC")
.Attachments.Add ("D:\TAX\CARS\No Private Usage Declaration Car.DOC")
.Display
End With
rst.MoveNext
Loop Until rst.EOF
End Sub
