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]

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.

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]

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.

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]

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.

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]

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

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("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
    
      'Name or IP of Remote SMTP Server
     .Configuration.Fields.Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = strMailServer
    
      'Type of authentication, NONE, Basic (Base64 encoded), NTLM
     .Configuration.Fields.Item("[URL]http://schemas.microsoft.com/cdo/configuration/authenticate[/URL]") = 0
    
      'Your UserID on the SMTP server
     .Configuration.Fields.Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = strNTUserName
    
      'Your password on the SMTP server
     .Configuration.Fields.Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = strNTUserPwd
    
      'Server port (typically 25)
     .Configuration.Fields.Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
    
      'Use SSL for the connection (False or True)
     .Configuration.Fields.Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpusessl[/URL]") = 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("[URL]http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout[/URL]") = 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]

Thank you so much for the code!!!

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

[trixxnixon]

ha!

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