Tried it today and it came up with an error. I will report back what it was in a minute
Tried it today and it came up with an error. I will report back what it was in a minute
I thought it might, which is why I did it this way, rather than inserting it in to your code.
The error message is
Microsoft access can’t find the field “|” referee to in your expression
Fixed the error it was my mistake. The form is now looking at my table and pulling the relevant email
OK, did the Form that you imported open and display the records in your Sales Area table?
I could add all the fields to the form so that you can check it displays the data.
Have you checked how the Field names in your table compare to the field names in my table?
Did it highlight a line of code when it displayed the error message?
OK, you posted while I was checking the database and writing my response.
Does it populate the email message?
The email is generated it pulls the email address from the table and just puts the generic message in at the moment. This all relies on me selecting a area code from your form.
Yes, so the code works with your Table, have you read the notes on my code, it tells you what is not needed in your code as they are duplicates of what you have?
Will there be more than one recipient per email?
Yes I’ve read through your notes I’m just uncertain at which point I would put your code would it be after the saving the excel file?
What about the number of recipients?
Yes there will be more than 1 recipient but i Was thinking I could just put in the email field classmaz@whatever.com;classmaz2@whatever.com etc
I would try it after this line
CurrentDb.CreateQueryDef QueryDefName, Base_SQL & "'" & LAF_Code & "'"
If the recipients are in the table the code can put them in.
I am not sure if you will need to split the code up, placing the Dim statements at the start of your code.
I have to go now, I will talk to you again tomorrow.
I have looked at the code and this part
Dim db As Database 'not needed in your database
Dim LAF_Code As Integer 'not needed in your database
Dim rsArea As Object, emailto As String, Subject As String, Body As String, reccount As Integer, count As Integer
On Error GoTo errorcatch
Set db = CurrentDb 'not needed in your database
Set rsArea = db.OpenRecordset("Sales Area")
rsArea.MoveLast
rsArea.MoveFirst
reccount = rsArea.RecordCount
LAF_Code = Me.[Area Selected] ' not needed in your database
Subject = "Your Area Report for area " & LAF_Code & " is ready"
Body = "Please access your Area Report as soon as possible"
Should be at the start of your code, note you can change the Subject and Body there.
This part of of the code
For count = 1 To reccount
If rsArea.[Area code] = LAF_Code Then
emailto = rsArea.Email 'emailto & rsArea.email
End If
rsArea.MoveNext
Next count
DoCmd.SendObject , , , emailto, , , Subject, Body, True
should be after
CurrentDb.CreateQueryDef QueryDefName, Base_SQL & "'" & LAF_Code & "'"
To have more Recipients added from the table you need to add a line of code before the loop
emailto = "" ' reset the email to nothing
You then need to change this line
emailto = rsArea.Email 'emailto & rsArea.email
to
emailto = 'emailto & "; " & rsArea.Email 'add email to the variable emailto
and finally after
Next count
add
emailto = right(emailto, len(emailto)-1) ' strip the leading ";"
You should also add this after the Exit Sub
errorcatch:
MsgBox Err.Description
to match the earlier goto, as your error trap is disabled.
Posting Permissions
Reply With Quote