Send an email from an record expiry date in access 1

[NickyJay]

Hi,

I have a database that is a simple catalogue of documents, details stored includes the document author, the date it was created and the expiry date/review date.

What i want to do is create an automatic procedure that emails the author once the expiry date has exceeded.

How do i do this?

 

[PHV]

And what have you so far and where are you stuck ?

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

 

[HarleyQuinn]

Something like (pseudocode):

Add a column to the table with a True/False or Yes/No datatype called emailed (for the purpose of the example)

App Opened
Run query to select all records where the expirydate is less than or equal to today and the emailed column doesn't contain a positive value (i.e it has a no or a false in it) into a recordset.
Loop through all of the returned records in the recordset and e-mail each one using Outlook automation.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[NickyJay]

Hi Guys,

Thanks for you replies! I have also read thread 702-396121 for a bit of info on emailing from access but i din't find anything the either applies or that i understand!

PHV,
I don't really know where to start with this!! I haven't anything to post at the mo...

HarleyQuinn,
Not sure what you mean?

After a bit of a rethink and discussion with the user, I want to create a command button that will search for all the records of documents that have an expired review date (at the moment the review date is just displayed in a text box on a form - published date + 365). Will i need to create a quesry first then and run code behind a button to pick out thos to be emailed??

Thanks for your help!!

 

[redapples]

Yes. ;-|

seriously though. How many are you talking about on a given day? The simplest way would be to generate a query or recordset of the email addresses that you need to send to then itterate or loop through these records - extract the email address and then send an email to them.

N.B
sending emails from access is not as easy as it once was due to some security features added after outlook 2000 sp1.

One way is to create an outlook object and use that to send to the email - outlook object. There are countless post on this subject.

Redemption gets around some of the security issues I allude to above.

Try - Outlook Redemption thread705-683140
Send e-mail from access databases Thread702-396121

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[HarleyQuinn]

NickyJay - redapples has illustrated what I said in my post.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[NickyJay]

HI Guys,

I'll only be emailing to one address at the moment - there will only be potentially 15 addresses in the future.

The number of emails in one day will only be a max of maybe 5?

What would be the best way of getting an the code to identify which records have expired - is this best through a query in the database or through an IF.. statement in the code?

Thanks again!

;-)

 

[HarleyQuinn]

I would have it run via a query.

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[HarleyQuinn]

Sorry, that wasn't a very full reply.

I would use a query because then you can return the results into a recordset, have your code loop through it and send an e-mail to the address found for each returned record. As redapples and I mentioned in our earlier posts.

Hope this helps

HarleyQuinn
---------------------------------
Help us to help you,
read FAQ222-2244 before posting.

 

[NickyJay]

OK, I now have a query that pulls out all records older than a year and an email command button that opens Outlook and enters the relevant information from fields into the body of the email,

however,

I'm not sure how to loop it through the records when there's more than one record inthe query!!

any help is greatfully recieved!!

Thanks guys

 

[072055]

NickyJay:
It sounds as if you may be a bit over your head here.

What you need to do is:
create a recordset based on the query
Loop through the recordset sending an email for each record.

Set rs = db.openrecordset("yourquerynameORsqlstringHere")
While not rs.eof
strEmailAddress = rs![YourQuery'sEmailFieldNameHere]
(Send the email message to this email address)
rs.movenext
Wend

Best regards

John

 

[NickyJay]

Hi 072055,

thanks for your help, i've added the loop you described into my code and I now have an error message "object required" and the following line of code is highlighted:

Set recset = db.OpenRecordset("qryFirstNotice")

any ideas???

The full code behind my button is this:

[blue]Private Sub cmdSendEmail1_Click()

Dim sEmail As String
Dim sBody As String
Dim sQMRef As String

Dim recset As DAO.Recordset

'creates variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

sEmail = "janet@*****.com"

sQMRef = Me!Title & Me!StaffName

sBody = sBody & "Hi Janet," & vbCrLf & vbCrLf & "The following document/s need reviewing and updating. Please forward the information to the relevant author " & _
Chr(13) & Chr(13)

Set recset = db.OpenRecordset("qryFirstNotice")
While Not recset.EOF
sBody = sBody & Me!Title & Me!StaffName & Chr(13)
recset.MoveNext
Wend

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

With objEmail
.To = sEmail
.Subject = "Quality Manual Documents Need Reviewing"
.Body = sBody
.Display
End With

Set objEmail = Nothing

Exit Sub

End Sub[/blue]

I really appreciate everyone's help!!

Nicky

 

[PHV]

Try this:
Set recset = CurrentDB.OpenRecordset("qryFirstNotice")

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

 

[NickyJay]

Hi Guys,

Me again - Still stuck i'm afraid!

This time i've got the email to display the number of records, however all it does is duplicate the first record data! For example, the query pulls out 150 records and the first record is a document on DPA - the title is repeated 150 times instead of displaying each individual title!

Have i placed the loop in the correct place?

[blue]Private Sub cmdSendEmail1_Click()
Dim db As DAO.Database
Set db = CurrentDb

Dim sEmail As String
Dim sBody As String
'Dim sQMRef As String

Dim recset As DAO.Recordset
Dim recCount As Integer
'recCount = 0

'creates variables for Outlook
Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

'**gathers information from the form
sEmail = "janet@****.com"
'sQMRef = Me!Title & Me!StaffName

sBody = sBody & "Hi Janet," & vbCrLf & vbCrLf & "The following document/s need reviewing and updating. Please forward the information to the relevant author " & _
vbCrLf & vbCrLf
Set recset = CurrentDb.OpenRecordset("qryFirstNotice")
While Not recset.EOF
'recCount = recCount + 1
sBody = sBody & Me!Title & Me!StaffName & vbCrLf
recset.MoveNext
Wend

'**creates an instance of Outlook
Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'***creates and sends email
With objEmail
.To = sEmail
.Subject = "Quality Manual Documents Needing Reviewing"
.Body = sBody
.Display
End With

Set objEmail = Nothing

Exit Sub
End Sub
[/blue]

I'm assuming the part in bold is the bit that needs work!!

Thanks

 

[PHV]

sBody = sBody & recset!Title & recset!StaffName & vbCrLf

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

 

[NickyJay]

Hi,

PHV, Thank you so much, it worked! Have a star for you efforts

Thanks to the rest of you guys for your help also - i've learned a bit from this, which is always a good thing

Nicola