Automated Emailing

[Dale]

Hello all, My Names Dale.



I'm rather new here. I would like to ask the following.

Is it possible to setup an automated emailing method within Access?
This would be done through (If a date is close then i would need it to send an email to someone if lets say *A record is nearly up, so a License*)

I hope one of you can suggest a method of doing this or help me with this as im pritty much stuck.

Kind Regards

Dale Golder

[Robeen]

What exactly will be in the email that you automatically send?
Is it data that is in a Table?
Who will the EMail go TO?
Is it going to be to the same email address every time?

What will determine when the EMail has to be sent?
How will you identify what information needs to be emailed?

I just looked through the Macro Actions and saw one called 'EMailDatabaseObject'.
Can you use that?

[Dale]

Thanks for the Quick response Robeen.

Currently i'm building a Database for an Espestos company which my "Dad" works for.
He asked me to add an Email section for it which would send emails to people he works with about license going out of date.

(The email persay) Would send an email from a table from the "Expiry date of the license"
In which it would send an email to the person within a certain amount of days BEFORE the time is up.

Telling that person when they need to get a renewal sorted

Im not really good with Admin side of Access. They dont really teach this stuff in college. (Im currently studying Level3 Diploma in IT here in England so.

If you need any more info just ask, thanks

Kind Regards

Dale Golder.

[Robeen]

I'm still having to do a little guesswork because I' m not sure I have all the details I need.

Still - I'll go ahead and tell you how I might approach this.
I'll be making some assumptions about what data is in your database etc.

First of all - let me warn you that if you are using Outlook, you might run into the problem of Outlook not wanting to allow Access to send the email [security in Outlook]. You might get a message from Outlook saying something like, " . . . a program is attempting to send an email in your name . . .".
If you click OK - Outlook will send the email. But that defeats the whole 'automation' thing. But if you're ok with that then there's no problem.

There is software you can purchase to override/bypass Outlook Security - or wriggle through the gaps.

What I ended up doing was writing my data to folders on our network that the respective people regularly check anyway for other work they have out there.

That being said . . .

If I wanted to automatically email people when their licenses are up for renewal here's a gist of what I'd do:

(I'm going to assume for this post that the person's email address and license expiration date are in the same Table [Table1]).

1. Create a macro named 'AutoExec'.
2. Put a 'RunCode' Action in the Macro - to run a Function that has code that will:
. . . i. Find all records where license needs to be renewed.
. . . ii. Get the email addresses for all those people.
. . . iii. Run Code that will email each person on the list.

I'm putting two functions in here.

The first one will show you how to get data [like your email addresses] out of your table and store it in a variable.
The second one will show you how to send an email [with attachments If you want to attach anything ever].

Run each of these in your own functions till you're comfortable with how they work.
Then you can put the code from 'EmailAttach()' into the 'Get_DB_Values()' function -
[into the Do While Loop]
so that every time an email address is found - an email is sent to it.

Both segments of code will be in a single function.
This single function will be called from your AutoExec Macro that will run automatically whenever you open your database.
[IF you want to open the DB in Design Mode when you have an AutoExec Macro - hold down the shift key].

When you do that - your 'Get_DB_Values' code will retrieve email addresses one at a time and and you will send the email address across to the 'EmailAttach' portion of the code to send the email to that email address.

1.

 
Function Get_DB_Values()
'Assumes that you have a Table1 and that Field1 is Text and Field2 is a Number.
' . . . just for testing purposes.
' . . . You should use your actual Table & field names & set your variables to match the data types of your table fields.
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strField1 As String
Dim intField2 as Integer
On Error GoTo Error_Handle
 
Set db = CurrentDb
'Your strSQL should only return rows of data that meet your date requirements - 
'so that you only get email addresses for people whose licenses are almost expired. 
'Eg something like this:
'strSQL = "Select * From [Table1] Where [ExpiryDate]> # " & ExpDate & "#;"
strSQL = "Select * From [Table1]"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
With rs
'This Do While loop goes through all the records in strSQL.
Do While Not rs.EOF
strField1 = rs![Field1]
intField2 = rs![Field2]
'I put this little If Then here so you can look at one value.
'substitute these with your own values.
If intField2 = 5 Then
    MsgBox strBranch
End If
.MoveNext                               'Move to next record in recordset.
Loop                                    'Back to 'Do While' to check if we are at the end of the file.
 
Exit_Get_DB_Values:
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    Set db = Nothing
    Exit Function
 
Error_Handle:
    Resume Exit_Get_DB_Values
End With
End Function

2.

 
Function EmailAttach() As Integer
Dim strRecipient, strBody, strSubject, astAttach(2) As String
Dim i, intCount, intSend As Integer
Dim oLook, oMail As Object
'You can put the email address that you pull out of Table1 in the previous code example into 
' . . . strRecipient - below.
strRecipient = "name@domain.net" 'Put your own email address from your Table here.
 
strSubject = "Put your subject text here."
strBody = "Put your Body text here."
'I Used the following to create an array of two files to attach.
intSend = True
intAcount = 2
astAttach(0) = "c:\FileName.doc"
astAttach(1) = "c:\FileName.xls"
On Error GoTo errEmailAttach
Set oLook = CreateObject("Outlook.Application")
Set oMail = oLook.CreateItem(0)
With oMail
.To = strRecipient
.Body = strBody
.Subject = strSubject
.ReadReceiptRequested = True 'You can set this to False if you want.
If intCount <> 0 Then
For i = 1 To intCount
.Attachments.Add (astAttach(i - 1))
Next
End If
If intSend = True Then
.Send
Else
.Display
End If
End With
Set oMail = Nothing
Set oLook = Nothing
EmailAttach = True
Exit Function
errEmailAttach:
MsgBox "Error Message: " & Err.Description & Chr$(10) & Chr$(13) & _
"Your email may not have been sent.", vbCritical, "Error"
On Error Resume Next
Set oMail = Nothing
Set oLook = Nothing
EmailAttach = False
End Function

I hope this makes sense. It seems like a really long post!!

Let me know how it goes.

[Dale]

Hi there Reboon.
Im currently attempting to look into what you have said and reattempt it in this "BASIC test Database"

For me this would be harder since i dont tend to understand every bit of what someone says but i can/will attempt what you have currently said.

If only there was a video for this sort of thing hehe :P

Kind Regards

Dale Golder.

[Robeen]

lol . . . you changed my name to 'Reboon'???


Yes - just paste the code into a Module in your test database.
Create a Table1 with a Field1 that has email addresses and a Field2 that has numbers.

See if you can get the functions to run:
Select the function name in the Module and click the little green 'Run' button on top.

Have you used VBA code much?

[Dale]

Sorry about the name "Roboon from Robeen" hehe :P I havent really used Visual basic much, the most i have ever done is made a space invaders game at college :P hehe..

Thats why i came here in-need of help from people like your self

Kind Regards
Dale Golder

[hertfordkc]

http://www.peterssoftware.com/c_emfa.htm

Google MS Access email and you will see a number of links. I believe there were several with video.

[A S MANN]

Mr. Robeen
Sorry to come in between,
I had checked your fuctions
Get_DB_Values() work correctly showing E mail ID
Function EmailAttach() shows error
Error Massage:Application-Define or Object-Define Error. Your E mail May not have neen send.
The E mail Account is properly configured and working Properly.
Can you pl tell where is the error.
Thanks

[Robeen]

Hi A S MANN!

I'm not sure why you are getting the error you got.

BUT . . .

I DID have a typo in my 2nd code sample [Function EmailAttach()].
This:

intAcount = 2

should be:
intCount = 2

When I first ran the EmailAttach function, I got an error saying Variable not defined. But when I corrected the code, the function ran fine and sent the email with the two attachments . . .

I am using Access 2010 - I wonder if that is what the issue is.

[A S MANN]

The Fuctions Do send the mail as checked after correction in "intCount = 2" variable,
Thanks.

[Dale]

Hello Robeen, would it possible for me to ask for alittle assistance with the Function coding.
Unless you know a guide which will show step to step functions as to what i need to do?

Im not that quick of a learner and i mis-Read alot of what Access has.
I have gone through checking the net for extras but im still getting stuck attaching this "Automatic Emailing system" to a Table. Which will then allow me to AUTOMATICALY alert someone about their License being out of date.


I do use Teamviewer if that is any use to you.

I hope you can give me alittle bit more of an insight to this as i can also send you the file if its needed.

Thankyou so much for your help sofar.

Kind Regards

Dale Golder.

[A S MANN]

I am preparing a small DB with Modified Codes of Mr Robeen. I am trying to combine Two Fuction in to a single function to automatically send e mails as per data in table and subject and Massage body of of a text box in a form. I will Give DB as soon it will be ready.

[Dale]

Dear A S Mann.
I am ever so greatful for your help in this aswell.

You and Robeen have been rather kind to lend a hand.
Thanks again both of you.

Kind Regards
Dale Golder.

[Robeen]

Hi Dale!
It looks like A S Mann is working on a solution for you.
Let me know how it goes!!
All the best.
Robeen