Hi Folks

I am currently trying to send a report via e-mail to about 25 team leaders and cc it to another 6 people. I tried the usual sendobject macro for quickness but told me the string was too long and can only hold about 6 e-mail addresses.

I have since then written the code into vba using the DoCmd.SendObject instruction but due to the number of e-mail addresses I have to enter which is 25 i run out of space to enter anymore as I am at the end of screen, is there an easier way to do this or is there something you can add at the end of the code to allow you to continue typing on a new line, or are I trying to send this to too many people.

Your help is much appreciated.

Don't hard code the addresses in. Use a table to store them and iterate through a recordset to add them to the list.

cheers will give it a go

hi there

I have attached the code that I'm using but not sure what to put in the section "To". I have a table setup called EMailAddress and the Email Address is saved under a field called "Address" can anyone tell me how to put this info into the code so it looks up the table.

DoCmd.SendObject acSendReport, "Health Improvement Activity", acFormatSNP, (Don't Know what to put in here), , , "Health Improvement Report", "Test", No

Sorry it has taken so long to get back to you. I've been real busy today and I'm just now getting to the forum.


Dim rst As DAO.Recordset
Dim strEmailAddresses As String

Set rst = CurrentDb.OpenRecordset("YourTableNameWithTheEmailAddresses")

Do Until rst.EOF
strEmailAddresses = strEmailAddresses & rst("EmailAddyFieldName") & ";"
rst.MoveNext
Loop

DoCmd.SendObject acSendReport, "Health Improvement Activity" acFormatSNP, strEmailAddresses,,,"Health Improvement Report", "Test", False


I didn't check your syntax for the SendObject method, so just be sure it is sound but that should help you as you can create a table and just use it to send to the addresses you choose to have in the table.

Depending on your version of Access you may need to set a reference to Microsoft DAO 3.x so that the recordset object will work.

Also, you should be aware that the snapshot format is not available in Access 2007, so if you are going to be moving to that anytime you just need to be aware that you will need to change that part.

Thanks very much again and no problem about delay i appreciate the help just about to try and conker it now.