having access send an email, email automation

is it possible to have access generate an automatic email based on a condition?
say for example, once a record reaches a particular age, have an email automatically populate and send to the email of the recipient.

re: having access send an email, email automation

Two in one.

Q1. Can Access send e-mails on your behalf?
A1. Yes, but a very much qualified Yes. Due to the number of restrictions placed on email systems to provide a level of security, many attempts at sending emails automatically fail, or require operator intervention (Not convenient).

Using Outlook is handled in various threads so I won't repeat any of it here. Suffice to say there are also restrictions that may be placed on this over and above those applied by Outlook (IP restrictions on SMTP for instance).


Q2. Is it possible to schedule within Access?
A2. Technically no. However, it is possible for Access to poll for a particular condition, upon finding which it can trigger some code. It is also possible to schedule the running of Access (or even a particular database) from the operating system and for that database to run startup code automatically.

This can only be used as a polling method though, as the scheduler will not recognise any Access specific conditions upon which to trigger execution.


Hope this helps.

re: having access send an email, email automation

yes it does help very much.
ive learned more from this board that any of the others


thanks!

re: having access send an email, email automation

You visit other boards! Shock! Horror!

Just kidding. We're very pleased to help where we can Trixx.

re: having access send an email, email automation

Hello Trixx, I frequently use code that:

1. Dynamically modifies the Record Source of a Report.
2. Outputs the Report to a High Resolution Snapshot (*.snp) Format.
3. E-Mails the Report to a single or multiple Recipients as an Attachment, by routing the E-Mail directly through a SMTP Server.
4. Only if you are seriously interested, will I post the code.

re: having access send an email, email automation

honestly, yeah i would like to see this code. im learning all of this by the seat of my pants. . . and every time they ask me to do something crazy... or at least something that seems crazy, i come here. because the people on this board are truly masters of the discipline and have yet to let me down, not only by having the answer, but giving me what I need to continue learning.
not only would i like to see the code for automating the email, I’m very curious about the code you have that automates the report

re: having access send an email, email automation

I'd say "Yes" anyway Trixx. You'll get a whole bunch of clever example code whichever way you look at it.

PS. Ooops - Should have refreshed before posting.

re: having access send an email, email automation

Hello again, Trixx. I've documented the code as well as I can without being annoying, any questions please feel free to ask:

1. The Report's (rptIRs) Record Source retrieves a Unique ID (IR_ID) via a Criteria set in the Query that is the Record Source for the Report, as in:
   
   Expand|Select|Wrap|Line Numbers

   1. [Forms]![frmINcidentReports]![IR_ID]        'in the [IR_ID] Field
2. The Command Button Click() Event code that initiates the entire process:
   
   Expand|Select|Wrap|Line Numbers

   1.  
   2. Private Sub cmdEMailThruSMTPServer_Click()
   3. On Error GoTo Err_cmdEMailThruSMTPServer_Click
   4. Dim lstrFrom As String
   5. Dim lstrTo As String
   6. Dim lstrSubject As String
   7. Dim lstrBody As String
   8. Dim lstrAttachment As String
   9. Dim lstrServerIPAddr As String
   10. DoCmd.Hourglass True    'indicates Connection Activity
   11. 'Outputs Report to an .snp Format in the same Folder as the Database
   12. DoCmd.OutputTo acOutputReport, "rptIRs", acFormatSNP, CurrentProject.Path & "\IR.SNP", False
   13. '****************************** FROM ******************************
   14. lstrFrom = "Some E-Mail Address.com"
   15. '******************************************************************
   16. '******************************* TO *******************************
   17. lstrTo = "Some Recipient.com"
   18. '******************************************************************
   19. 'Subject of E-Mail
   20. lstrSubject = "Incident Report: " & Now()
   21. '
   22. 'Body of E-Mail
   23. lstrBody = "Attached please find Incident Report for your review"
   24. '
   25. 'Absolute Path to Attachment, in this case a report in the *.snp Format
   26. lstrAttachment = CurrentProject.Path & "\IR.SNP"
   27. '
   28. 'IP Address of SMTP Server
   29. lstrServerIPAddr = "10.2.0.32"
   30. '
   31. 'Call the Function supplying  Arguments
   32. Call Mail_SMTP("", "", lstrFrom, lstrTo, lstrSubject, lstrBody, , , lstrAttachment, , lstrServerIPAddr)
   33. DoCmd.Hourglass False   'End of Connection attempt whether Success or Failure
   34. '
   35. MsgBox "Incident Report has been sent to [" & lstrTo & "]", vbExclamation, "E-Mail Confirmation"
   36. '
   37. Exit_cmdEMailThruSMTPServer_Click:
   38.   Exit Sub
   39. Err_cmdEMailThruSMTPServer_Click:
   40.   DoCmd.Hourglass False     'Added 11/17/08 - must restore if Error condition exists
   41.   MsgBox "Hey Ezra," & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "OPERATION ABORTED!", _
   42.           vbExclamation, "Error in cmdEMailThruSMTPServer_Click()"
   43.   Resume Exit_cmdEMailThruSMTPServer_Click
   44. End Sub
   45.  
3. The actual Function itself:
   
   Expand|Select|Wrap|Line Numbers

   1.  
   2. Public Function Mail_SMTP(strNTUserName As String, strNTUserPwd As String, _
   3.          strFrom As String, strTo As String, Optional strSubject As String, _
   4.          Optional strBody As String, Optional strBCC As String, _
   5.          Optional strCC As String, Optional strAttachment As String, _
   6.          Optional strHTMLBody As String, Optional strMailServer As String = "10.2.0.32")
   7. On Error GoTo ErrHandler
   8. 'Must Declare a Reference to the Microsoft CDO for Windows 2000 Library
   9. Dim email As New CDO.Message
   10.  
   11. With email
   12.   .From = strFrom
   13.   .To = strTo
   14.     If (Len(strAttachment) > 0) Then .AddAttachment strAttachment
   15.     If (Len(strHTMLBody) > 0) Then .HTMLBody = strHTMLBody                '
   16.     If (Len(strBCC) > 0) Then .BCC = strBCC
   17.     If (Len(strCC) > 0) Then .CC = strCC
   18.     If (Len(strSubject) > 0) Then .Subject = strSubject
   19.     If (Len(strBody) > 0) Then .TextBody = strBody
   20.  
   21.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
   22.  
   23.    'Name or IP of Remote SMTP Server
   24.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = strMailServer
   25.  
   26.    'Type of authentication, NONE, Basic (Base64 encoded), NTLM
   27.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...n/authenticate") = 0
   28.  
   29.    'Your UserID on the SMTP server
   30.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = strNTUserName
   31.  
   32.    'Your password on the SMTP server
   33.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = strNTUserPwd
   34.  
   35.    'Server port (typically 25)
   36.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
   37.  
   38.    'Use SSL for the connection (False or True)
   39.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = False
   40.  
   41.    'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
   42.    'Works quite well for me
   43.   .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 20
   44.     .Configuration.Fields.Update
   45.       .Send
   46. End With
   47.  
   48. ExitProcedure:
   49.   Exit Function
   50. ErrHandler:
   51.   Err.Raise Err.Number, "Mail_SMTP", "An the following error occurred while attempting " & _
   52.                         "to send mail via Mail_SMTP." & vbCrLf & "Error Number: " & Err.Number & _
   53.                         vbCrLf & vbCrLf & "Error Description: " & vbCrLf & Err.Description
   54.   Resume ExitProcedure
   55. End Function
   56.  
   57.  

re: having access send an email, email automation

Thank you so much for the code!!!

re: having access send an email, email automation

Not a problem Trixy, did it work out for you?

re: having access send an email, email automation

i havent tried it yet,
i was removed from working on access, long enough to forget how to do everything ive learned.
now the request has been re requested.
im going to struggle through it and see if i can get it to work.

Thanks again,
i will let you know how it goes.

re: having access send an email, email automation

Welcome back Trixx.

We're here anyway if you have questions to help you get back into the swing of things.

re: having access send an email, email automation

ha!

im a little more than lost when it comes to applying this code to my application.
what i have been tasked to do. i need a command button on a form, to send an automated email with the info that has been entered into the form, to the person who completed the form.

The form pulls the user id from windows, which is also associated to their name and email address in active directory.

do I need to contact IT to get the information regarding the email server or could i get this information from outlook?
also,
will this code open outlook?
could access pull the email address from active directory using the user ID?
And...
would i be able to have the email be sent from a specified outlook inbox?
i ask that because of the user name and password variables benig sent to the server via the function, and im not sure if an inbox would actually have a username and password like an actual user would have.


As always, thank you for never making a novice feel like a moron. im very proud to be a member of this board.

the experts here are the best ive ever talked too.


Thank you

re: having access send an email, email automation

Hello trixxnixon. FRisrt things first:
How exactly, is the Form 'pulling' the User ID from Windows and can The User Name and EMail Address be 'pulled' likewise? Kindly post any relevant code.

re: having access send an email, email automation

here is the module i call to populate the user name field.
=fOSUserName() is used as the default value in the field to display and save the user id. im not really sure if the name and email address can be pulled from windows, but it is saved within Active Directory and can be brought up using the user ID. are email and name typically saved into windows on login?

re: having access send an email, email automation

I'm pretty sure that all this info can be pulled from Active Directory directly but the problem is that I am not running AD. Let me see if I can get to a system that uses it, and if so, I'll get back to you. In the meantime, perhaps some of the other Members may be able to assist you until then.

re: having access send an email, email automation

I've created a Function that will Query the Active Directory System and return the Logged-in User's First Name, Last Name, Telephone Number, and E-Mail Address. It accomplishes this by setting the Return Value of the Function equal to a Comma-Delimited String containing these Values. The Calling Routine then places these Values into a Variant Array using the Split() Function from which they are easily retrieved for reference. Below, please find the Function and its Call:

re: having access send an email, email automation

I guess I understand how this all works, but im not sure about a few things.
1. should I replace all of this with some sort of server address or is this line of code complete?

2How would I use the call in terms of pulling this information into a fields in a form?

re: having access send an email, email automation

It is complete in and of itself.
The Attachment will show this to you.

************************************************** ***********
In this case a Picture is worth a Thousand Words, so what I have decided to do is to Attach a Test Database, which I will create hopefully tomorrow, to a Post. Download the Attachment, Open the Database, then Click on the Command Button to see exactly what is going on as far as retrieving the Values from AD then displaying them on a Form. I feel as though this is the best approach to take by far. The Code makes the major assumption that Active Directory is operational on your PC.

re: having access send an email, email automation

You did your famous disappearing attachment trick again ADezzi.

cheers,

re: having access send an email, email automation

No disappearing Attachment, mshmyob, just a matter of timing (LOL)!

re: having access send an email, email automation

Hello trixxnixon, here is the Attached Database that I promised. If you have any further questions, please feel free to ask. Either myself or another Member will be more than happy to assist you.

re: having access send an email, email automation

Didn't see the 'tomorrow' sentence. :)

cheers,

re: having access send an email, email automation

I have asked a question in Active Directory Usage in VBA. I don't wish to hijack this thread, but it 's sort of related.

re: having access send an email, email automation

im using Access 97, and can not open the file. i have 07 and 97 at my house. would i be able to open and convert the file that 97 could open?

sorry for being so needy.

re: having access send an email, email automation

I'll attempt a Conversion when I get a chance, but there are no guarantees.

re: having access send an email, email automation

I don't know where this comes from but I'm sure I've heard conversions are best done from one version to the next, by the next (97 to 2000 by 2000, etc).

I believe 2007 has the facility to upgrade, but I wouldn't recommend using this approach unless it were unavoidable (or you just test it & it works fine).

Keep copies whatever approach you may take.

re: having access send an email, email automation

Sorry trixxnixon, but it just dawned on me that I never made the Converted Database available to you. I had to make some cahnges that I knew would not work in Access 97 like the Split() Function, but here goes:

re: having access send an email, email automation

i think i am just going to open it in a newer version, and learn how the code works, then apply the code into the application i will be using.

i would worry about attempting a conversion, i have never been able to get good results from anything i have converted.

what version was the example created in? i can just use that version of access to open the DB to see how things work and go from there.

thank you so much for all of your time an attention.

re: having access send an email, email automation

Download the Attachment in Post #22, you should have no problems with the Conversion Process.

re: having access send an email, email automation

and as always, as a result of using this board, ive learned a new trick(email via an access database), which i can now apply elsewhere if needed, and my leadership is pleased. . . . . thank you all so very much. ADezii, NeoPa one day i will treat both you gentlemen, to fine cigars and brandy(or beer and wings)

Thanks again!

re: having access send an email, email automation

I can't speak for NeoPa, trixxnixon, but I'm in! (LOL)! BTW, I think that NeoPa is more 'refined' that I am, so count on me for the beer and wings, while leaving the fine cigars and brandy for NeoPa.

re: having access send an email, email automation

What if you dipped your wings in the brandy? That might be refined enough.

cheers,

re: having access send an email, email automation

You really think that NeoPa will allow me to dip 'my' wings in 'his' brandy?(LOL)!

re: having access send an email, email automation

Thank you Trixx :)

I have to say being referred to as a gentleman does it for me :D

This is lucky as I'm a non-smoking, teetotal vegetarian (Don't tell anyone about the teetotal part - I often play the part a whisky connoisseur among my Bytes colleagues).

For ADezii, I've also been a bit of a beer connoisseur in my time (no snob in that respect), although mainly interested in English non-lager beer. I certainly appreciate the charms of other beers around the world too.

re: having access send an email, email automation

but seriously guys, thank you, from the bottom of my soul, time and time again.

re: having access send an email, email automation

Yopu are quite welcome. I'm sure that I can speak for all of us in that the pleasure was all ours!