You saw in Chapter 10 how easily mail can be sent from within Access, using DoCmd.SendObject , but this is rather restrictive . It doesn't allow you to set options on the message, or do anything complex - it's really just designed for sending objects. To achieve a greater degree of control over the mail procedure you can use the Outlook object model to send messages.
To use Outlook there are only a few objects you need to know about:
The main Outlook application.
A mail message.
A diary appointment.
In fact, most of the folders in Outlook have an associated Item object.
Using Outlook programmatically means that we can build sending mail into existing processes. Consider the ingredients table, which has the UnitsInStock and ReOrderPoint fields. Every time an order is placed the UnitsInStock field should be decreased, and if it drops below the ReOrderPoint , then an order could be mailed automatically. This would prevent the ingredients from ever running out. Or you could run this as a weekly procedure, so that you don't send lots of small orders to the same supplier. That's what we'll do here. We'll create a query that shows us which items are below the reorder point, and then we'll create a mail message to our supplier requesting these items.
If you are an AOL user , then you won't be able to use Microsoft Outlook to send mail messages. AOL uses a proprietary mail system. You can still run through the exercise, but you won't actually be able to send mail.
Create a new query, adding tblIngredient and tblSupplierList . Add fkCompanyID from tblSupplierList , and Name and ReOrderPoint from tblIngredient .
In the Criteria field for ReOrderPoint add the following:
The query should now look like this:
Close the query, saving it as qryReOrder . Now create a new query, adding tblCompany , tblSupplierList, and tblIngredient .
Add CompanyID , CompanyName , ContactName, and Email from tblCompany , and ReOrderPoint from tblIngredient .
For the Criteria for ReOrderPoint add the following:
The query should now look like this:
View the properties of the query. You can do this by the Properties button on the toolbar, or by selecting Properties from the View menu, or Properties from the context menu (right mouse button).
Change the Unique Records property to Yes . Now close the query, saving it as qryReOrderSuppliers .
Create a new module. From the Tools menu select References , check Microsoft Outlook 10.0 Object Library , and press OK to close the dialog.
Create a new procedure called ReOrder in the empty module. Add the following code:
Public Sub ReOrder() Dim db As Database Dim recReOrder As Recordset Dim recSupps As Recordset Dim objOutlook As New Outlook.Application Dim objMessage As MailItem Dim strSQL As String Dim strOrder As String Dim strItems As String Set db = CurrentDb() Set recSupps = db.OpenRecordset("qryReOrderSuppliers") While Not recSupps.EOF strSQL = "SELECT * FROM qryReOrder " & _ "WHERE fkCompanyID = " & recSupps("CompanyID") Set recReOrder = db.OpenRecordset(strSQL) strItems = "Item" & vbTab & " Quantity" While Not recReOrder.EOF strItems = strItems & vbCrLf & recReOrder("Name") & _ vbTab & recReOrder("ReOrderPoint") recReOrder.MoveNext Wend recReOrder.Close strOrder = "Dear " & recSupps("ContactName") & _ vbCrLf & vbCrLf & _ "Once again we are running short of the following items:" & _ vbCrLf & vbCrLf & _ strItems & _ vbCrLf & vbCrLf & _ "I'd be grateful if you could deliver " & _ "these as soon as possible." & _ vbCrLf & vbCrLf & _ "Many Thanks" & _ vbCrLf & vbCrLf & _ "Dave" If Not IsNull(recSupps("Email")) Then Set objMessage = objOutlook.CreateItem(olMailItem) With objMessage .To = recSupps("Email") .Subject = "New Order" .Body = strOrder .Send End With End If recSupps.MoveNext Wend recSupps.Close Set recSupps = Nothing Set recReOrder = Nothing Set objOutlook = Nothing Set objMessage = Nothing End Sub
Save the module as Email . Now switch to the Immediate window and try the procedure out, by typing ReOrder , or by pressing the Play button on the menu bar.
When the procedure has finished, open Outlook and have a look in either your Outbox or your Sent Items folder. You should have a mail message there somewhere The procedure sends one mail message to each supplier with an e-mail address (here, there's only one supplier). Where the message appears depends on whether you've got a connection set up:
Admittedly the order details could be formatted a little nicer, but that's not a great deal of trouble. We're really concentrating on sending the message here.
How It Works
Let's first look at the queries we created. The first, qryReOrder , shows the ingredients that need reordering . We've included the ReOrderPoint in this query because we are going to order the same number of items that the ReOrderPoint contains. The second query, qryReOrderSuppliers , shows the supplier details - contact name and e-mail address. Setting the Unique Records property to Yes ensures that we only see one record for each supplier. If this value was No then we would see a row for each supplier, for each product that needed reordering. We want one entry for each supplier because we are going to send a single mail message to each supplier with all of the order details on it.
Now on to the procedure. The first thing it does is declare all of the variables . The first three you've seen before - a database object and two recordsets. We need a recordset for the suppliers and one for the order items. Next comes the two Outlook objects - the main Outlook application, and a mail message item. Finally there are three strings - to hold a SQL query, the full order text and the order details:
Dim db As Database Dim recReOrder As Recordset Dim recSupps As Recordset Dim objOutlook As New Outlook.Application Dim objMessage As MailItem Dim strSQL As String Dim strOrder As String Dim strItems As String
Once the variables are declared, we can open the database and create the recordset of suppliers:
Set db = CurrentDb() Set recSupps = db.OpenRecordset("qryReOrderSuppliers")
We now need to loop through each supplier:
While Not recSupps.EOF
We need to create a list of order items for this supplier, so we create a SQL string, based on qryReOrder . Once the SQL string is set we create a recordset from it:
strSQL = "SELECT * FROM qryReOrder " & _ "WHERE fkCompanyID = " & recSupps("CompanyID") Set recReOrder = db.OpenRecordset(strSQL)
Now we have a recordset containing ingredients for a particular supplier, and we want a list of these in our mail message, so we create a string containing the item to be ordered and the quantity to order. We simply loop through the recordset adding these details to a string. We use the intrinsic constants vbCrLf and vbTab to provide new lines and tabs in the string. Once the loop is finished we can close the recordset, because all of the details we need are now in the string:
strItems = "Item" & vbTab & " Quantity" While Not recReOrder.EOF strItems = strItems & vbCrLf & recReOrder("Name") & _ vbTab & recReOrder("ReOrderPoint") recReOrder.MoveNext Wend recReOrder.Close
We can't just send a message with a list of the ingredients, so we add the contact name as some more text, again using intrinsic constants to format this text. We use a separate string for the items and for the full order details, because we'll be using the items later in the chapter:
strOrder = "Dear " & recSupps("ContactName") & _ vbCrLf & vbCrLf & _ "Once again we are running short of the following items:" & _ vbCrLf & vbCrLf & _ strItems & _ vbCrLf & vbCrLf & _ "I'd be grateful if you could deliver " & _ "these as soon as possible." & _ vbCrLf & vbCrLf & _ "Many Thanks" & _ vbCrLf & vbCrLf & _ "Dave"
The string is now complete, so the mail message can be created. If the e-mail address field in the database is empty, then obviously we can't send an e-mail order. We didn't omit records that have no e-mail address in the query because we are going to modify this procedure later on, and we want to make sure it copes with all suppliers, and not just those that have e-mail addresses.
If Not IsNull(recSupps("Email")) Then
The variable objOutlook is an instance of the Outlook application. One of the methods this has is CreateItem , which creates an item of a specific type - in this case we use an intrinsic constant to specify a mail item:
Set objMessage = objOutlook.CreateItem(olMailItem)
We now have a mail message item, so we can set some properties. The To property is the e-mail address of the recipient, and the Subject property is the subject line. The Body property is the actual body of the message, so we set this to the string we have created. We then use the Send method to send the message:
With objMessage .To = recSupps("Email") .Subject = "New Order" .Body = strOrder .Send End With End If
That's the end of one supplier, so we move on to the next:
And that's the end of the suppliers, so we close the recordset and clean up any object references, to free the memory they use:
recSupps.Close Set recSupps = Nothing Set recReOrder = Nothing Set objOutlook = Nothing Set objMessage = Nothing
Pretty easy, huh? In fact the section of code that actually sends the message is far simpler than the code that creates the message body.
Another way of achieving the aim of ordering by e-mail would be to create a file, perhaps a saved report or a Word document, containing the order details, and then attach that to the mail message. We'll look at that method in a little while, when we look at Word.
Next we'll look at how to create appointments in Outlook, which will hopefully cure you of some of those horrible yellow notes you've got stuck all around your monitor! What we'll do is add a reminder to our Outlook diary, reminding us when the orders we've just placed are due - they are usually expected within three days.
Open the Email module you've just created.
Create a new Private procedure, as follows :
Private Sub MakeAppointment(objOApp As Outlook.Application, _ strCompany As String, strBody As String) Dim objAppt As AppointmentItem Set objAppt = objOApp.CreateItem(olAppointmentItem) With objAppt .Subject = "Order due from " & strCompany .Body = strBody .Start = Date + 3 & " 8:00" .End = Date + 3 & " 8:00" .ReminderSet = True .Save End With End Sub
Add a new line to the ReOrder procedure, after the mail message has been sent, just before moving to the next supplier which calls the new MakeAppointment sub:
End With End If MakeAppointment objOutlook, recSupps("CompanyName"), strItems recSupps.MoveNext Wend
Save the module, and run it again.
Open Outlook and have a look at the calendar, for three days from now:
Open one of the appointments to look at it in detail:
Notice how the start and end times are the same, and that the body of the appointment mirrors the details from the order?
How It Works
Let's first look at the MakeAppointment procedure, which takes three arguments. The first is the Outlook application object. This could have been declared as a global variable, but since it's only going to be used in two procedures, it seems sensible to pass it as an argument. The second argument is the company name, and the third the body of the appointment.
Private Sub MakeAppointment(objOApp As Outlook.Application, _ strCompany As String, strBody As String)
The first thing to do in the procedure is create an appointment item. This is the same method as we used to create a mail item - all we do is use a different constant:
Dim objAppt As AppointmentItem Set objAppt = objOApp.CreateItem(olAppointmentItem)
Now we can set the appointment details. The Subject of the appointment is a note that the order is due from the company, and the Body contains the details passed in as the third argument. We'll see what these are in a minute:
With objAppt .Subject = "Order due from " & strCompany .Body = strBody
We set the Start and End times of the appointment to be today's date, as returned by the Date function, with three days added to it (remember from our early chapters on data types that adding a number to a date adds that number of days). We also append the time to this string, since without an explicit time, the appointment defaults to midnight:
.Start = Date + 3 & " 8:00" .End = Date + 3 & " 8:00"
We then ensure that a reminder flag is set so that the appointment will pop up a reminder at the appropriate time and date, and then we save the appointment:
.ReminderSet = True .Save End With End Sub
To create the appointment, we simply call this function:
MakeAppointment objOutlook, recSupps("CompanyName"), strItems
We pass in the Outlook application object, the company name, and the list of ingredients.
Once again you can see that using automation to use the facilities of Outlook is extremely simple. With only a few extra lines of code we now have an automatic reminder system. You could also use this sort of thing for adding payment reminders when you send out orders to customers.