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

having access send an email, email automation

P: 98
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.
Jan 12 '09 #1
Share this Question
Share on Google+
36 Replies


NeoPa
Expert Mod 15k+
P: 31,487
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.
Jan 13 '09 #2

P: 98
@NeoPa

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


thanks!
Jan 14 '09 #3

NeoPa
Expert Mod 15k+
P: 31,487
You visit other boards! Shock! Horror!

Just kidding. We're very pleased to help where we can Trixx.
Jan 14 '09 #4

ADezii
Expert 5K+
P: 8,634
@trixxnixon


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.
Jan 15 '09 #5

P: 98
@ADezii
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
Jan 15 '09 #6

NeoPa
Expert Mod 15k+
P: 31,487
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.
Jan 15 '09 #7

ADezii
Expert 5K+
P: 8,634
@trixxnixon

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.  
Jan 15 '09 #8

P: 98
Thank you so much for the code!!!
Jan 26 '09 #9

ADezii
Expert 5K+
P: 8,634
@trixxnixon
Not a problem Trixy, did it work out for you?
Jan 26 '09 #10

P: 98
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.
Jun 11 '09 #11

NeoPa
Expert Mod 15k+
P: 31,487
Welcome back Trixx.

We're here anyway if you have questions to help you get back into the swing of things.
Jun 11 '09 #12

P: 98
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
Jun 17 '09 #13

ADezii
Expert 5K+
P: 8,634
Hello trixxnixon. FRisrt things first:
The form pulls the user id from windows, which is also associated to their name and email address in active directory.
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.
Jun 17 '09 #14

P: 98
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?


Expand|Select|Wrap|Line Numbers
  1. Public Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
  2. (ByVal lpBuffer As String, nSize As Long) As Long
  3.  
  4. Function fOSUserName() As String
  5. On Error GoTo fOSUserName_Err
  6.  
  7. Dim lngLen As Long, lngX As Long
  8. Dim strUserName As String
  9.  
  10. strUserName = String$(254, 0)
  11. lngLen = 255
  12. lngX = apiGetUserName(strUserName, lngLen)
  13.  
  14. If lngX <> 0 Then
  15. fOSUserName = Left$(strUserName, lngLen - 1)
  16. Else
  17. fOSUserName = ""
  18. End If
  19.  
  20.  
  21. fOSUserName_Exit:
  22. Exit Function
  23.  
  24. fOSUserName_Err:
  25. MsgBox Error$
  26. Resume fOSUserName_Exit
  27. End Function
  28.  
  29.  
Jun 17 '09 #15

ADezii
Expert 5K+
P: 8,634
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.
Jun 17 '09 #16

ADezii
Expert 5K+
P: 8,634
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fReturnADInfo() As String
  2. Dim sysInfo As Object
  3. Dim oUser As Object
  4. Dim intUser As Integer
  5. Dim strLName As String
  6. Dim strFName As String
  7. Dim strUserName As String
  8. 'Dim strEMail As String
  9.  
  10. Set sysInfo = CreateObject("ADSystemInfo")
  11. Set oUser = GetObject("LDAP://" & sysInfo.UserName & "")
  12.  
  13. strUserName = sysInfo.UserName
  14.  
  15. intUser = InStr(strUserName, ",")
  16.  
  17. strLName = Mid(strUserName, 4, (intUser - 4))
  18. strFName = Right(strUserName, ((Len(strUserName) - intUser) - 3))
  19. intUser = InStr(strFName, ",")
  20.  
  21. strFName = Left(strFName, intUser - 1)
  22.  
  23. fReturnADInfo = strFName & "," & strLName & "," & oUser.TelephoneNumber & _
  24.                            "," & oUser.EmailAddress
  25.  
  26. Set sysInfo = Nothing
  27. Set oUser = Nothing
  28. End Function
Expand|Select|Wrap|Line Numbers
  1. Dim varADInfo As Variant
  2.  
  3. varADInfo = Split(fReturnADInfo(), ",")
  4.  
  5. Debug.Print "First Name: " & varADInfo(0)
  6. Debug.Print "Last Name: " & varADInfo(1)
  7. Debug.Print "Telephone Number: " & varADInfo(2)
  8. Debug.Print "E-Mail Address: " & varADInfo(3)
Jun 18 '09 #17

P: 98
I guess I understand how this all works, but im not sure about a few things.
1.
Expand|Select|Wrap|Line Numbers
  1. ("LDAP://" & sysInfo.UserName & "")
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?
Jun 18 '09 #18

ADezii
Expert 5K+
P: 8,634
@trixxnixon
should I replace all of this with some sort of server address or is this line of code complete?
It is complete in and of itself.
How would I use the call in terms of pulling this information into a fields in a form?
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.
Jun 18 '09 #19

mshmyob
Expert 100+
P: 903
You did your famous disappearing attachment trick again ADezzi.

cheers,

@ADezii
Jun 19 '09 #20

ADezii
Expert 5K+
P: 8,634
@mshmyob
Attach a Test Database, which I will create hopefully 'tomorrow'
No disappearing Attachment, mshmyob, just a matter of timing (LOL)!
Jun 19 '09 #21

ADezii
Expert 5K+
P: 8,634
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.
Jun 19 '09 #22

mshmyob
Expert 100+
P: 903
Didn't see the 'tomorrow' sentence. :)

cheers,

@ADezii
Jun 19 '09 #23

NeoPa
Expert Mod 15k+
P: 31,487
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.
Jun 19 '09 #24

P: 98
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.
Jun 19 '09 #25

ADezii
Expert 5K+
P: 8,634
@trixxnixon
I'll attempt a Conversion when I get a chance, but there are no guarantees.
Jun 19 '09 #26

NeoPa
Expert Mod 15k+
P: 31,487
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.
Jun 22 '09 #27

ADezii
Expert 5K+
P: 8,634
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:
Jun 22 '09 #28

P: 98
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.
Jun 22 '09 #29

ADezii
Expert 5K+
P: 8,634
@trixxnixon
Download the Attachment in Post #22, you should have no problems with the Conversion Process.
Jun 23 '09 #30

P: 98
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!
Jun 27 '09 #31

ADezii
Expert 5K+
P: 8,634
@trixxnixon
ADezii, NeoPa one day i will treat both you gentlemen, to fine cigars and brandy(or beer and wings)
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.
Jun 27 '09 #32

mshmyob
Expert 100+
P: 903
What if you dipped your wings in the brandy? That might be refined enough.

cheers,

@ADezii
Jun 28 '09 #33

ADezii
Expert 5K+
P: 8,634
@mshmyob
You really think that NeoPa will allow me to dip 'my' wings in 'his' brandy?(LOL)!
Jun 28 '09 #34

NeoPa
Expert Mod 15k+
P: 31,487
@trixxnixon
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.
Jun 28 '09 #35

P: 98
but seriously guys, thank you, from the bottom of my soul, time and time again.
Jun 29 '09 #36

ADezii
Expert 5K+
P: 8,634
@trixxnixon
Yopu are quite welcome. I'm sure that I can speak for all of us in that the pleasure was all ours!
Jun 29 '09 #37

Post your reply

Sign in to post your reply or Sign up for a free account.