concatenating emails into a string via SQL

[kit_sune]

Hello all,

There are so many threads on here for emailing a PDF version of a report. I've put together a nearly complete bit of VBA that will do what I want - except for one thing.

Basically I have a personnel table with an email field. I Also have a field that details if a person is available or not ("Home" means they are available)

I need to create a string that is comprised of all the email addresses of the personnel who are "Home". In another thread I read that this can be done using a SQL statement.


So this is the VBA that I have so far - I commented the SQL for now because I don't quite know how to use it here.

Also, I have created the string, called "emailsList"

Private Sub Email_Button_Click()
 
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
 
Dim emailsList As String
 
    DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report.", True
End Sub

Do I need to do a loop that concatenates?

Thanks for any assistance.

~Kit

 

[pbaldy]

Yes, you'd need to open a recordset on that SQL and loop the results, building your "To" string.

 

[kit_sune]

Yes, you'd need to open a recordset on that SQL and loop the results, building your "To" string.

I have never actually created a loop before so I'm a little lost - also, what do you mean by "open a recordset" ?

 

[pbaldy]

This is doing some things you don't need, but it should get you started.

http://www.granite.ab.ca/access/email/recordsetloop.htm

Rather than email within the loop, sounds like you want to build a string and then send a single email, so your code within the loop would look more like:

strTo = strTo & RS!cEmailAddress & ";"

 

[kit_sune]

This is doing some things you don't need, but it should get you started.

http://www.granite.ab.ca/access/email/recordsetloop.htm

Rather than email within the loop, sounds like you want to build a string and then send a single email, so your code within the loop would look more like:

strTo = strTo & RS!cEmailAddress & ";"

That's a lot to digest, I'll have to come back to this tomorrow.

 

[pbaldy]

It is, but it will certainly enhance your knowledge of VBA. Post back if you get stuck.

 

[kit_sune]

It is, but it will certainly enhance your knowledge of VBA. Post back if you get stuck.

I actually stayed behind a bit and tried to analyze things. However when I went to test things out I find I can't get past this point:

Dim MyDB As Database, RS As Recordset
Dim emailsList As String

I get "Compile error: User-defined type not defined" and it highlights "Database"

Which is confusing because looking around I see that this is the standard...

Here's what I have:

Option Compare Database
Option Explicit
 
Private Sub Email_Button_Click()
 
Dim MyDB As Database, RS As Recordset
Dim emailsList As String
 
Set MyDB = CurrentDb
Set RS = db.OpenRecordset("Personnel_Table")
 
Do While Not RS.EOF
  emailsList = emailsList & RS!Email & ";"
  RS.MoveNext
 
Loop
RS.Close
Set RS = Nothing
emailsList = Left(emailsList, Len(emailsList) - 1)
    DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report from the Global Reach.", True
 
End Sub

 

[pbaldy]

Some Access versions require the declarations be disambiguated (I always do it anyway):

Dim MyDB As DAO.Database, RS As DAO.Recordset

If that doesn't fix it, some versions also require the MS DAO 3.6 library be checked in Tools/References.

 

[kit_sune]

some versions also require the MS DAO 3.6 library be checked in Tools/References.

That was it. I'm down to this point - the VBA works great, except now I need to use the SQL to pull only the addresses I need, which I can't figure out how to do that.

This time I really am going home because it's way past time. I'll come back to it in the morning.
Thanks for all your help,

Here's where I am now.

 Option Compare Database
Option Explicit
Private Sub Email_Button_Click()
'SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)="Home"));
Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim emailsList As String
Set MyDB = CurrentDb
Set RS = MyDB.OpenRecordset("Personnel_Table")

Do While Not RS.EOF
  emailsList = emailsList & RS!Email & ";"
  RS.MoveNext
Loop
RS.Close
Set RS = Nothing
emailsList = Left(emailsList, Len(emailsList) - 1)
    DoCmd.SendObject acReport, "AWACT_Report", acFormatPDF, emailsList, , , "Training Update", "Attached is the newest Training Report from the Global Reach.", True
End Sub

~Kit

 

[pbaldy]

You would replace the table name in the OpenRecorset line with your SQL.

 

[kit_sune]

Great - I tried inputing the SQL right in the statement but I ran into an issue (Probably made a mistake) so I turned it into a string and went from there. At least it works.

Dim MyDB As DAO.Database, RS As DAO.Recordset
Dim emailsList As String
Dim strSQL As String
 
strSQL = "SELECT Personnel_Table.Email FROM Personnel_Table WHERE (((Personnel_Table.Status)='Home'));"

Set MyDB = CurrentDb
Set RS = MyDB.OpenRecordset(strSQL)

Thanks again Paul you never fail to help me out.

 

[pbaldy]

Happy to help Kit. I'll be deleting the spammer-to-be in a minute.