URGENT...AUTOMATING EMAIL VIA V.B CODE?

HEY GUYS,

need some urgent help here....I am querying my database based on a
DueDate field...and want to send an automated email to anyone that
falls within two months PRIOR to this "DueDate", so far the Code is
querying the database and trying to run a module I created, title
SendMail, however it is having trouble sending the email for some
reason... any suggestions as to why....here is the code:


Private Sub Command13_Click()
Dim rst As Object
Set rst = Me.Recordset.Clone


With rst
.MoveFirst
Do While Not .EOF
If .Fields("DueDate") >= VBA.Date And _
.Fields("DueDate") <= DateAdd("m", 2, VBA.Date) Then
DoCmd.RunCommand SendMail("EmailAddress")
End If
.MoveNext
Loop
End With


THERES THE CODE IAM USING TO CHECK THE DATABASE TO SEE IF THE DUEDATE
FALLS WITHIN THE NEXT TWO MONTHS......HERE IS THE SENDMAIL COMMAND


Option Compare Database


Dim strTo As String
Dim strsubject As String
Dim varbody As Variant
Dim strattachment1 As String
Dim strattachment2 As String


strTo = Me!EmailAddress
strsubject = "Shore Based Maintainance Agreements"
varbody = "Please check the following Agreements, it appears that they
are up for re-newal within the next two months, or they have somehow
expired, Kind Regards Admin."


Dim olApp As Outlook.Application
Set olApp = CreateObject("Outlook.Application")


Dim olNs As Outlook.NameSpace
Set olNs = olApp.GetNamespace("MAPI")
olNs.Logon


Dim olMail As Outlook.MailItem
Set olMail = olApp.CreateItem(olMailItem)


olMail.To = strTo
olMail.Subject = strsubject
olMail.Body = varbody


olMail.Send


Set olNs = Nothing
Set olMail = Nothing
Set olApp = Nothing


End Sub


YOUR HELP WOULD BE MUCH APPRECIATED!


Regards,
Liam

re: URGENT...AUTOMATING EMAIL VIA V.B CODE?

First off, I'd use the code at Danny Lesandrini's site to deal with
sending e-mail. That way, you know that part should not be the
problem. to be sure, test the code with sample data you know will work
(like sending e-mail to your own address).

Danny's code:
http://www.amazecreations.com/datafa...utlookMail.asp

Then why not just create a query to create the recordset instead of
looping through an entire table? That's going to be unnecessarily slow
if you have a big table - just query out the junk you don't want.

Then you could do something like

set rst=dbengine(0)(0).openquerydef("qryDueRecords")
do until rst.eof
SendMessage rst.Fields("email")
rst.movenext
loop

of course, you'll have to change Danny's code a little so you can pass
in the e-mail address, etc. (but that's really trivial).

Oh, and two minor things - all caps is considered shouting... We're not
deaf. And what might be urgent to you is not to anyone else. If
you're really in a hurry, search the NG before posting. There are
dozens of examples of automating outlook here. (I've read a lot of
them).