By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,192 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Jun 8 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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).

Jun 9 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.