need help with query updating excel file

[imintrouble]

Hello!



I have a query which keeps track of some basic information regarding student's college degree audits such as what degree they are pursuing, how many more credit hours are needed in order to graduate, and ending in their e-mails, the column of data which this query was made for, keeping track of e-mails of the students. Well I'm needing to create a mail merge to send e-mails by word, which will use an excel file to get the information from. This excel file will get it's information from the e-mail query.

My query only shows the data where the date sent field has not been filled in yet. Well I would like two things I suppose.

One: I would like for the excel file to only show information reflecting exactly what is in the query. So as before when I was discussing how the query only shows information where the date sent field is not filled in, I would like the excel file to reflect this in that, if i have a student who needs to be e-mailed, then their DATE SENT field would be blank until i fill it in, after having sent the e-mail. This student would not only be seen, along with all of the information in the query about their degree and everything else, in the query but also have all of this information reflected in the excel file. Now when the e-mail is sent, I would fill in the DATE SENT field on original table which I created the query from with the date the e-mail was sent. Filling this information in would remove the student from the e-mail query, which I want to then be reflected in the excel file. This is best because it would allow me to make sure that the same students do not recieve multiple e-mails after having already recieved one.

If this isn't possible, I would appreciate a detailed alternative now that you all have a firm understanding of my problem.

Best,

Imintrouble

[June7]

Why not doing all within Access? Could an Access report serve in place of the Word document? Or why not have Word get data from Access? Why is Excel in the middle?

A query cannot update an Excel sheet.

[imintrouble]

Thanks June, always a help. Well I believe it has to be excel becasue I'll be using the information in the excel file for a mail merge for the e-mails we will be sending (As there are hundreds of people we will be e-mailing simultaneously, at this point probably around 700+ students will be recieving an e-mail.) If this is possible to create a mail merge using the access file instead of the excel file, Great! please tell me how! however this is the only way I know how so that's why I chose it.

Bummer about not being able to update the excel file. Oh well, I guess I'll just have to keep copying the information from the query to the excel file, unless a better idea presents itself to me :P.

Again, Thanks, you've been great as usual June!

[June7]

VBA code can open workbook and populate cells of worksheets by directly addressing cells or use TransferSpreadsheet method. The source of data can be table or query. Also, Excel can link to Access table/query.

I know Word can pull data directly from Access tables, I've just never coded behind Word document. Still wondering why not just an Access report - is the Word document so large?

[imintrouble]

well it's not that the word file is so large, but only that it's not a normal informal e-mail, but an official letter sent via e-mail from a college to students. Not sure an Access report would be able to duplicate that type of officiality.

As for the first part of your post, I have never heard of TransferSpreadsheet method, and i don't remember much VBA from when I took a class on it a couple of years ago. If you believe this to be the right choice, as I'm clearly ignorant of what is, won't you please tell me what I should do?

[June7]

The letter is sent by attachment to email? This can be programmed in VBA behind Access/Word/Excel.

I don't know why a report could not look 'official'. It can have graphics for logos and be structured to look just like letterhead. Or can print onto letterhead stock. An Access report design is limited to 22 inch length, so with a page break, the most 'pages' you can have for a single record are 2. Of course, a multi-record report will produce as many 'pages' as needed to display all records. Also, use of subreports can add 'pages' to a document. I do all of this for very 'official' documents.

I don't know if TransferSpreadsheet method is suited to your needs. You could explore it. Start with http://msdn.microsoft.com/en-us/libr...ffice.12).aspx