Batch process

[Transentient]

Not sure If i am posting in right place, so apologies.

Essentially i have a table which contains company name, contact name and email address. I have a form which I can select those contacts to send an email to. I have all the code to construct and send the email.

There maybe multiple contacts and thus email addresses for some companies, and only one contact etc for others. Now rather than send individual emails to each one on the list, i am looking to try and group multiple contacts for a specific company onto one email. I have initially used a query on the table which looks at the company name and whether to send, with the user providing the company name for the query parameter. the email is then contructed for all those contacts selected for that company.

What I would like to do is somehow get the program to automatically group contacts for me.

My ideas thus far are
Each company is intentified with a unique code
The program looks at the first code in the list and automitcally inserts and runs the above mentioned query
It then moves to the next code, if this is the same as the previous code it moves to the next code. If the code is new it runs the query etc.

Essentially how do i extract the company code from record 1?, then move to next record and extract the company code for record2? and then compare against record1?

Everything else i can do.

thanx for any help

 

[DCrake]

Simple Software Solutions

If you are familar with VB as it suggests in your thread, try the following:

In VBA

Dim Company as Int
Dim rsCompany As DAO.Recordset
dim rsContacts As DAO.Recordset
Dim EmailAdrs As String

Create a Company recordset of all the companies you are emailing to Order by Primary Key (PK)

Set rsCompany = CurrentDb.OpenRecordset(TblCompanies)

Do Until rsCompany.EOF

Company = rsCompany(PK)

Set RsContacts = CurrentDb.OpenRecordset("Select * From CompanyContacts Where CompanyID =" & Company & ";"

If Not RsContacts.EOF Then


Do until RsContacts.EOF


...Concat all contacts email addresses into one string delimited with ; and add to the cc object in outlook

EmailAdrs = EmailAdrs & RsContacts("EmailAddress") & ";"

RsContacts.MoveNext

Loop

...Drop the last ;

EmailAdrs = Left(EmailAdrs,Len(EmailAdrs)-1)

'Do your email code here for the company

'Reset the EmailAdrs to ""

RsCompanies.MoveNext

Loop


This will ensure that you create a block send for each company.

This is a brief outline of the task. You could be more elaborate in the way in which you handle the emails and validating each contact has an email address, etc. That is up to you.

Hope you understand the brevity.

CodMaster:

 

[Transentient]

Thanks, code works great, many thanks for your help