emailing notification from access database 1

[nat1967]

hi group.

i have a database that my user get books/videos out of. right now, i have them click a checkbox that enables a textbox for them to enter their email address into. i have to manually follow this up after 10 days to email them if they have not "checked" back in their book/video. is there a way to have access calculate 10 days past the day they "check" out their book/video and send them an email reminding them to "return" their book/video?

i have seen several things on sending email from access but have not been able to get any of the codes from the books to work. if someone could help with me with the vba code that is required, i would GREATLY appreciate it!

thanks in advance

nathan

 

[kathryn]

First of all you need to have a way to kick off the code. Is the database opened every day? Does it open to a form? If so, you can put the code in the Open event of the form.

The code is not difficult, but it assumes that you have MS Outlook installed and that you have set a reference to the Outlook object model in your database.

Here is sample code:

****BEGIN CODE****
Function GL1XX_Driver()

On Error GoTo DriverErr

Dim outApp As Outlook.Application
Dim outMessage As Outlook.MailItem
Dim dtmDelay As Date
Dim strName As String
Dim blnErrors As Boolean
Dim strErrStr As String

GL1XX_Driver = RC_ABEND


Set outApp = CreateObject("Outlook.application&quot

Set outMessage = outApp.CreateItem(olMailItem)

outMessage.To = "Jane Whitney"
outMessage.Subject = "Out of Balances are ready at the printer"
outMessage.Send

GL1XX_Driver = RC_COMPLETE



DriverExit:
On Error Resume Next
Set outMessage = Nothing
outApp.Quit
Set outApp = Nothing
Exit Function

DriverErr:
blnErrors = True
strErrStr = "Driver: " & strName & "did not print"
Call Write_Err("GL1XX", strErrStr)

Resume DriverExit
End Function
****END CODE****

Try that and if it doesn't work, post and tell us exactly what happens.

Kathryn

 

[nat1967]

hi kathryn,

the form is opened everyday so i took your advice and put the code in an "onopen" event. when i did, i got an error when the code tried to run. also, how does this code know to wait 10 days? i dont understand.

error info:

"compile error. expected end of sub" the line that was highlighted was the first. here is how i posted the code.

Private Sub Form_Open(Cancel As Integer) (<--this is the error line that was yellow)

Function GL1XX_Driver()

On Error GoTo DriverErr

Dim outApp As Outlook.Application
Dim outMessage As Outlook.MailItem
Dim dtmDelay As Date
Dim strName As String
Dim blnErrors As Boolean
Dim strErrStr As String

GL1XX_Driver = RC_ABEND


Set outApp = CreateObject(&quot;Outlook.application&quot

Set outMessage = outApp.CreateItem(olMailItem)

outMessage.To = &quot;Jane Whitney&quot;
outMessage.Subject = &quot;Out of Balances are ready at the printer&quot;
outMessage.Send

GL1XX_Driver = RC_COMPLETE



DriverExit:
On Error Resume Next
Set outMessage = Nothing
outApp.Quit
Set outApp = Nothing
Exit Function

DriverErr:
blnErrors = True
strErrStr = &quot;Driver: &quot; &amp; strName &amp; &quot;did not print&quot;
Call Write_Err(&quot;GL1XX&quot;, strErrStr)

Resume DriverExit
End Function

End Sub

as you can tell, i am new to this and greatly appreciate your time and patience.

nathan

 

[kathryn]

Nathan,

You have both an End Function and an End Sub in you code. Since the code begins with the word Sub, you only want the End Sub.

As regards having this run every 10 days, the code I posted only does the email.

You need to add code which runs a query that selects all records that meet your criteria. Then you use the code I gave you to send the emails to those people.

Kathryn

 

[nat1967]

thank you kathryn.

nathan

 

[RSGB]

Hi nathan1967,

As Kathryn says if you have some code behind the on open event of a start-up form, which runs a query on the data such as:

SELECT BookVideoID, BorrowerEmailAddress, DateHired, DateReturned FROM YourTable
WHERE ((DateHired)>Now()-10) AND (DateReturned Is Null));

This would give a list of items and email addresses based on hired over 10 days ago and not yet returned, which you can then use for the email sending above.

 

[MichaelRed]

Kathryn,

even w/o the confusion of the sub, the [red]red[/red] lines generate errors in Ms. Access. The [blue]BLUE[/blue] one obviously needs to replaced for the individual name.

These are, perhaps, obvious and I can get past them. However, when these are corrected, this routine prompts for the exchange server. I am not familiar w/ this. Could you please exlpain? It is NOT an Error, I did include Ms Outlook in the references.


[red]GL1XX_Driver = RC_ABEND[/red]


Set outApp = CreateObject(&quot;Outlook.application&quot

Set outMessage = outApp.CreateItem(olMailItem)



outMessage.To = &quot;[blue]Jane Whitney[/blue]&quot;
outMessage.Subject = &quot;Out of Balances are ready at the printer&quot;
outMessage.Send

[red]GL1XX_Driver = RC_COMPLETE[/red]



DriverExit:
On Error Resume Next
Set outMessage = Nothing
outApp.Quit
Set outApp = Nothing
Exit Function

DriverErr:
blnErrors = True
strErrStr = &quot;Driver: &quot; &amp; strName &amp; &quot;did not print&quot;
[red] Call Write_Err(&quot;GL1XX&quot;, strErrStr)[/red]



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[kathryn]

The first two red error lines refer to Constants set up elseware in my database:

RC_COMPLETE = 0, RC_PENDING = 1, RC_PROCESSING = 2, RC_ABEND = 99

They are used by the calling procedure to find out if this procedure worked.

The Write_Err refers to a procedure that writes errors to an error table. You can replace that with any standard error handling.

I have no idea what the Exchange server error is. I assume that you use Outlook, but you might want to check with your email administrator to see if there is some reason why Access can't see the exchange server.
Kathryn

 

[mattpearcey]

I am also having problems trying to set up a simple email application. I have a databse that holds various contact details, one of whihc is email address. Through viewing it in the form, i want to be able to to click (as you do with a simple hyperlink) on the email address, and it will come up with a blank email with the recipients name in the to box. I am using access 97 and outlook 2000. I have tried some code, but it comes up with the same error as nathan - complie error expected end sub

can anyone help?

Thanks in advance - matt