Forum Discussion
Help with Access VBA code to automate email!!!!!
I have made a new setup for your code. It only shows the main structure. You have to insert the code for actually sending an email.
Private Sub Form_Open(Cancel As Integer) Dim rec As Recordset Set rec = CurrentDb.OpenRecordset("SELECT * FROM AllActiveLicenceExpiryDueIn30DaysQ") Do Until rec.EOF If Not IsNull(rec!AssignedToEmail) And _ Not rec!EmailSent30days Then 'SEND EMAIL REMINDER FOR LICENCE EXPIRY IN 30 DAYS rec.Edit rec!EmailSent30days = True rec.Update End If rec.MoveNext Loop End Sub
It is hard to tell without knowing the error messages.
I see at least two main points.
If you refer to fields in a recordset you do it like this:
.To = rec!AssignedToEmployeeEmail. Only in one line you do it right.
A Do Until should always be accompanied by a Loop (in this case before the End Sub).
A thing that does not cause an error but is wrong any way, is where you decide to send an email or not. If AssignedToEmail is NOT Null, you don't look at the DaysEmailSent any more. So you will always send an email.
Edit: there is more wrong with the if's. As there is no Else, the code will continue after the Movenext. So for the next record (even If there is no next) you are going to send an email (without looking to).
Thanks for that you make some really good points and have clarified some things for me but I now have more questions.
The error that I was getting was a compile error : about needing a Loop which you also state . However now that I have made the changes you have suggested excluding the bit about Else because of the If's (Im still trying to work this out) Do you any links that explains how these work or give me an example of this as I am very confused on what I have read.
Im now got a bigger problem because when I go to open the MainMenu nothing will load just keeps going access not responding. I have tried this on a couple of backup test database and it happens on all on them. Is this because I haven't sorted the Else bit of the code?
My new code is
Private Sub Form_Open(Cancel As Integer)
'DEFINING RECORDSET TO USE
Dim db As Database
Dim rec As Recordset
Set db = CurrentDb
Set rec = db.OpenRecordset("SELECT * From AllActiveLicenceExpiryDueIn30DaysQ")
If rec.RecordCount > 0 Then
rec.MoveFirst
Do Until rec.EOF
If rec!EmailSent30days = True Then
rec.MoveNext
'SEND EMAIL REMINDER FOR LICENCE EXPIRY IN 30 DAYS
Dim O As Outlook.Application
Dim M As Outlook.MailItem
Set O = New Outlook.Application
Set M = O.CreateItem(olMailItem)
With M
.BodyFormat = olFormatHTML
.HTMLBody = "Please be aware that " & FirstName & LastName & " Licence is Due for renewal in the next 30 days. Licence Expiry is -" & LicenceExpiry & ",<P>" & _
"Please update New Licence Expiry date and upload copy of New Drivers Licence" & ",<P>" & _
"Kind Regards" & ",<P>" & _
"Kym Wilson"
.To = rec!AssignedToEmployeeEmail
.CC = rec!PersonalEmail
.Subject = "Licence Expiry Due in 30 Days " & Now()
.Display
End With
rec.Edit
rec!EmailSent30days = True
rec.Update
rec.MoveNext
End If
Loop
End If
End Sub
Also just in regards to the Do until and Loop code is that all I need to do I cant seem to find good examples to help explain it for me?
I have made a new setup for your code. It only shows the main structure. You have to insert the code for actually sending an email.
Private Sub Form_Open(Cancel As Integer) Dim rec As Recordset Set rec = CurrentDb.OpenRecordset("SELECT * FROM AllActiveLicenceExpiryDueIn30DaysQ") Do Until rec.EOF If Not IsNull(rec!AssignedToEmail) And _ Not rec!EmailSent30days Then 'SEND EMAIL REMINDER FOR LICENCE EXPIRY IN 30 DAYS rec.Edit rec!EmailSent30days = True rec.Update End If rec.MoveNext Loop End SubThank you so much this has worked !!!!
Although I am going to one more question of you. Now that this works am I able to do this for multiple recordset.
e.g. we currently only have this working for "AllActiveLicenceExpiryDueIn30DaysQ" what if we need to do it for "AllActiveLicenceExpiryOverdueQ" as well. Or for reminders when vehicle inspections are due ( this requires a different email to be sent (I'm all over the programming of the send email part so dont need help with this) I just dont quiet understand the mechanics behind recordsets on how to define multiple queries. Can you point me in the right direction or provide useful records?
Thanks again for the help!!
- I think you have to write separate bits of code like this one for each situation.
Because you have different query's and you have to refer to specific fields (e.g. EmailSent30days will be different) and the email subject and body will be different, it is hard to make one code that does it all.
You better copy the code each time and adjust what is different for that situation.