Email Merge

[RussG]

Can't find the answer to this so here goes:
I am putting together a DB to register volunterers on an event using Access 2003.

The event will run for a number of days and volunteers may take up a number of different roles.

I have a volunteers details table
I have a Volunteersroles table ( these are linked using VolunteerID) and this has lookups for Days and Roles.

I would like to be able to send a bulk email so each individual will get a list of their allocated roles for each day.

I have no problem with basic email mail merge but can't see how to merge the roles data.

I see this much like have a report and sub report , the report being the volunteer details and the sub report the linked Volunteer roles.

How do I pass the Volunteer roles part to the merge?

Any help would be much appreciated.

 

[DavidAtWork]

How do you want to display the roles data, if you want to display it in the body of the email, this would require some extensive vba that would basically cylce a recordset of your volunteers, running some vba sql against each and using the output in the body of the email. Another method would be to attach the roles data as an Excel file for example and then you could output the same vba sql to Excel and attach the file to the email.
The whole thing would work as a loop going through your entire list of volunteers, you can create an email template which you can base each email on
Certainly doable but requires some thought about how you want the end result to appear.
David

 

[RussG]

Thanks for your promt reply David. I had an idea it was going to be complicated, still no harm in trying and I might learn something.
Russ

 

[NeutronFlux]

I guess the use of the term 'merge' is confusing me a bit. I'm not sure whether you want each volunteer to get an email with their personal timetable or if you want every volunteer to get the same schedule that covers everyone's roles.

Assuming the first scenario, you can actually just make a report and subreport linked with VolunteerID. From there, just loop through the volunteer details table. For each volunteer, get their email address, open the report you created with either a filter or where clause with the VolunteerID, send as a PDF, close report.

 

[RussG]

Hi NeutronFlux - yes option one , I have found a bit of code to do just that. I have very basic VBA knowledge so its a learning curve but tweaking the code a bit I have part of this working sort of. No PDF output in Access 2003 so using rtf which works ok.
Cheers