use sendobject to email selected recipients in listbox

[BigAnthony]

Hi

I am helping my sister with an Access database someone wrote for her. I was wondering if someone a lot more knowledgeable than me, could provide some help and advice - I don't have that much experience with Accesss.

I helped her write (with assistance from others) the code to print the reports for her employees when they are selected from a list box. She has asked me if she can do the same so that instead of printing, it emails the reports as Access Snapshot attachments to the selected employees.

I have helped her email a report (using sendobject) for the Employee on screen, but she does not want to do that individually for her 25 employees, which is understandable.

For the printing of reports for selected employees from the list box, this is the code I have:

For Each EmployeeName In Me.EmployeeList.ItemsSelected
CurrentDb.Execute "INSERT INTO EmployeeTemp (EmployeeCode) VALUES (" & """" & Me.EmployeeList.ItemData(EmployeeN* ame) & """" & ")"
Next EmployeeName

DoCmd.OpenReport "ReportName", acNormal

Now, how can I email those reports for the employees selected in the list box?

I would appreciate any advice from anyone who has done this in Access.

Thanks - it's much appreciated

[pks00]

You have code that goes thru the selected items in the listbox
You then insert them into a temporary table, why is this. Does the report u run use this table?

Does your listbox store the email addresses? If so then that could make life easier for u. U can always hide that column

U have used SendObject so why not use your existing code and just expand on it a bit

What does your email use to split email addresses, do u use a semi colon or colon. No matter, what u can do is modify the code accordingly

U can add all email addresses in the one SendObject line

[BigAnthony]

[quote=pks00]You have code that goes thru the selected items in the listbox
You then insert them into a temporary table, why is this. Does the report u run use this table? Does your listbox store the email addresses?
[/END QUOTE]

Hello,

Thanks for replying.

The report I run uses this temp table.

How can I change the following line so that it inserts both the employeecode and the employeeemail into the temporary table assuming the email address is in the listbox?

CurrentDb.Execute "INSERT INTO EmployeeTemp (EmployeeCode) VALUES (" & """" & Me.EmployeeList.ItemData(EmployeeN*ame) & """" & ")"

Thanks
Anthony

[pks00]

Where is your email address currently held? if u store it in the listbox, then u can simply reference that
e.g.
your listbox contains

Employee Name in column 2 and Email Address in column 3 (amongst others)

Then in your loop, u go thru the selected items and reference that particular column

For Each iRow In Me.EmployeeList.ItemsSelected
CurrentDB.Execute "INSERT INTO EmployeeTemp (EmployeeCode, EmployeeEmail) VALUES ('" & Me.EmployeeList.Column(1, iRow) & "','" & Me.EmployeeList.Column(2, iRow) & "')"
Next iRow


Note, i used the variable iRow instead as it makes more sense
I reference

Me.EmployeeList.Column(1, iRow)

this refers the the 2nd column, - remember, column numbering starts from 0

1st col of data is column 0
2nd col of data is column 1

etc

do note, u must include the email address in the listbox in order to get at it, this means changing the rowsource

[NeoPa]

How can I change the following line so that it inserts both the employeecode and the employeeemail into the temporary table assuming the email address is in the listbox?

Try something like :-

CurrentDb.Execute "INSERT INTO EmployeeTemp
(EmployeeCode, EmployeeEmail)
VALUES ('" & Me.EmployeeList.ItemData(EmployeeName) & _
    "','" & Me.EmployeeList.ItemData(EmployeeEmail) & "')"

INSERT INTO target [(field1[, field2[, ...]])]
VALUES (value1[, value2[, ...])