having access send an email, email automation

[trixxnixon]

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.

[NeoPa]

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.

[trixxnixon]

@NeoPa

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


thanks!

[NeoPa]

You visit other boards! Shock! Horror!

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

[ADezii]

@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.

[trixxnixon]

@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

[NeoPa]

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.

[ADezii]

@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:
   

   [Forms]![frmINcidentReports]![IR_ID]        'in the [IR_ID] Field

2. The Command Button Click() Event code that initiates the entire process:
   

    
   Private Sub cmdEMailThruSMTPServer_Click()
   On Error GoTo Err_cmdEMailThruSMTPServer_Click
   Dim lstrFrom As String
   Dim lstrTo As String
   Dim lstrSubject As String
   Dim lstrBody As String
   Dim lstrAttachment As String
   Dim lstrServerIPAddr As String
   DoCmd.Hourglass True    'indicates Connection Activity
   'Outputs Report to an .snp Format in the same Folder as the Database
   DoCmd.OutputTo acOutputReport, "rptIRs", acFormatSNP, CurrentProject.Path & "\IR.SNP", False
   '****************************** FROM ******************************
   lstrFrom = "Some E-Mail Address.com"
   '******************************************************************
   '******************************* TO *******************************
   lstrTo = "Some Recipient.com"
   '******************************************************************
   'Subject of E-Mail
   lstrSubject = "Incident Report: " & Now()
   '
   'Body of E-Mail
   lstrBody = "Attached please find Incident Report for your review"
   '
   'Absolute Path to Attachment, in this case a report in the *.snp Format
   lstrAttachment = CurrentProject.Path & "\IR.SNP"
   '
   'IP Address of SMTP Server
   lstrServerIPAddr = "10.2.0.32"
   '
   'Call the Function supplying  Arguments
   Call Mail_SMTP("", "", lstrFrom, lstrTo, lstrSubject, lstrBody, , , lstrAttachment, , lstrServerIPAddr)
   DoCmd.Hourglass False   'End of Connection attempt whether Success or Failure
   '
   MsgBox "Incident Report has been sent to [" & lstrTo & "]", vbExclamation, "E-Mail Confirmation"
   '
   Exit_cmdEMailThruSMTPServer_Click:
     Exit Sub
   Err_cmdEMailThruSMTPServer_Click:
     DoCmd.Hourglass False     'Added 11/17/08 - must restore if Error condition exists
     MsgBox "Hey Ezra," & vbCrLf & vbCrLf & Err.Description & vbCrLf & vbCrLf & "OPERATION ABORTED!", _
             vbExclamation, "Error in cmdEMailThruSMTPServer_Click()"
     Resume Exit_cmdEMailThruSMTPServer_Click
   End Sub
    

3. The actual Function itself:
   

    
   Public Function Mail_SMTP(strNTUserName As String, strNTUserPwd As String, _
            strFrom As String, strTo As String, Optional strSubject As String, _
            Optional strBody As String, Optional strBCC As String, _
            Optional strCC As String, Optional strAttachment As String, _
            Optional strHTMLBody As String, Optional strMailServer As String = "10.2.0.32")
   On Error GoTo ErrHandler
   'Must Declare a Reference to the Microsoft CDO for Windows 2000 Library
   Dim email As New CDO.Message
    
   With email
     .From = strFrom
     .To = strTo
       If (Len(strAttachment) > 0) Then .AddAttachment strAttachment
       If (Len(strHTMLBody) > 0) Then .HTMLBody = strHTMLBody                '
       If (Len(strBCC) > 0) Then .BCC = strBCC
       If (Len(strCC) > 0) Then .CC = strCC
       If (Len(strSubject) > 0) Then .Subject = strSubject
       If (Len(strBody) > 0) Then .TextBody = strBody
    
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...tion/sendusing") = 2
    
      'Name or IP of Remote SMTP Server
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpserver") = strMailServer
    
      'Type of authentication, NONE, Basic (Base64 encoded), NTLM
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...n/authenticate") = 0
    
      'Your UserID on the SMTP server
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...n/sendusername") = strNTUserName
    
      'Your password on the SMTP server
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...n/sendpassword") = strNTUserPwd
    
      'Server port (typically 25)
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...smtpserverport") = 25
    
      'Use SSL for the connection (False or True)
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...ion/smtpusessl") = False
    
      'Connection Timeout in seconds (the maximum time CDO will try to establish a connection to the SMTP server)
      'Works quite well for me
     .Configuration.Fields.Item("http://schemas.microsoft.com/cdo/con...nectiontimeout") = 20
       .Configuration.Fields.Update
         .Send
   End With
    
   ExitProcedure:
     Exit Function
   ErrHandler:
     Err.Raise Err.Number, "Mail_SMTP", "An the following error occurred while attempting " & _
                           "to send mail via Mail_SMTP." & vbCrLf & "Error Number: " & Err.Number & _
                           vbCrLf & vbCrLf & "Error Description: " & vbCrLf & Err.Description
     Resume ExitProcedure
   End Function
    
    

[trixxnixon]

Thank you so much for the code!!!

[ADezii]

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

[trixxnixon]

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.

[NeoPa]

Welcome back Trixx.

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

[trixxnixon]

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

[ADezii]

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.

[trixxnixon]

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?

Public Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long
 
Function fOSUserName() As String
On Error GoTo fOSUserName_Err
 
Dim lngLen As Long, lngX As Long
Dim strUserName As String
 
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
 
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
 
 
fOSUserName_Exit:
Exit Function
 
fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit
End Function
 
 

[ADezii]

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.

[ADezii]

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:

Public Function fReturnADInfo() As String
Dim sysInfo As Object
Dim oUser As Object
Dim intUser As Integer
Dim strLName As String
Dim strFName As String
Dim strUserName As String
'Dim strEMail As String
 
Set sysInfo = CreateObject("ADSystemInfo")
Set oUser = GetObject("LDAP://" & sysInfo.UserName & "")
 
strUserName = sysInfo.UserName
 
intUser = InStr(strUserName, ",")
 
strLName = Mid(strUserName, 4, (intUser - 4))
strFName = Right(strUserName, ((Len(strUserName) - intUser) - 3))
intUser = InStr(strFName, ",")
 
strFName = Left(strFName, intUser - 1)
 
fReturnADInfo = strFName & "," & strLName & "," & oUser.TelephoneNumber & _
                           "," & oUser.EmailAddress
 
Set sysInfo = Nothing
Set oUser = Nothing
End Function

Dim varADInfo As Variant
 
varADInfo = Split(fReturnADInfo(), ",")
 
Debug.Print "First Name: " & varADInfo(0)
Debug.Print "Last Name: " & varADInfo(1)
Debug.Print "Telephone Number: " & varADInfo(2)
Debug.Print "E-Mail Address: " & varADInfo(3)

[trixxnixon]

I guess I understand how this all works, but im not sure about a few things.
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?

[ADezii]

@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.

[mshmyob]

You did your famous disappearing attachment trick again ADezzi.

cheers,

@ADezii

[ADezii]

@mshmyob

Attach a Test Database, which I will create hopefully 'tomorrow'

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

[ADezii]

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.

[mshmyob]

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

cheers,

@ADezii

[NeoPa]

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.

[trixxnixon]

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.

[ADezii]

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

[NeoPa]

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.

[ADezii]

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:

[trixxnixon]

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.

[ADezii]

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

[trixxnixon]

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!

[ADezii]

@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.

[mshmyob]

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

cheers,

@ADezii

[ADezii]

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

[NeoPa]

@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.

[trixxnixon]

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

[ADezii]

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