Forumula / Macro required to create automated billing and then Mail merge

[citibond]

I have two spreadsheet one is my main spreadsheet which contains user details and email addresses, the other is a monthly downloaded spreadsheet which contains itemised listing for calls. I need to send each user an email each month with their itemised call list and total. We have been using a manual process until now and would like to automate it.

Spreadsheet 1 Fields

Tel No	Name	Dept	Contract Type	Expiry	Model	Line Rental	Email Address
09092222	XYZ	XXX	Blackberry	23/09/2014	9790	10	xyz@gmail.com
09088888	YYY	YYY	Iphone	23/08/2015	4	15	yyy@gmail.com

<tbody>
</tbody>

Spreadsheet 2

Tel No	Number Called	Duration	Cost
09092222	6565625256	00:01:54	0.23
09092222	6871268618	00:03:32	0.34
09088888	7681276817	00:04:01	0.20
09092222	8767378783	00:01:03	0.30
09088888	87289798279	00:08:03	0.67

<tbody>
</tbody>

We use a vlookup to transfer the details from spreadsheet 1 to a excel invoice, but can't work out how to do a vlookup for spreadhseet 2 based on the number and also how to email automatically.

 

[oldbrewer]

for spreadsheet2 make a pivot table that will list every unique number and in the central box put sum of cost

then use your vlookup directly onto the pivot table

take out all subtotals and grand totals for clarity

 

[citibond]

I have tried the above but it only returns the top value in the pivot table. I need all the column results from spreadsheet 2 to be included in the invoice

 

[oldbrewer]

Tel No	Number Called	Duration	Cost		Tel No	Name	Dept	Contract Type	Expiry	Model	Line Rental	Email Address
9092222	6565625256	00:01:54	0.23		1234567	fred	dept1	Iberry	16/09/2015	999	22	fred@gmail.com
9092222	6871268618	00:03:32	0.34		9088888	YYY	YYY	Iphone	23/08/2015	4	15	yyy@gmail.com
9088888	7681276817	00:04:01	0.2		9092222	XYZ	XXX	Blackberry	23/09/2014	9790	10	xyz@gmail.com
9092222	8767378783	00:01:03	0.3
9088888	8.729E+10	00:08:03	0.67
1234567	9876543	00:06:53	1.23
	Sum of Cost
	Tel No	Total			############
	1234567	1.23			fred@gmail.com
	9088888	0.87			yyy@gmail.com
	9092222	0.87			xyz@gmail.com
	Grand Total	2.97
	formula in cellbelow #########
	=IF(B16="","",IF(B16="Grand Total","",VLOOKUP(B16,mytable,8)))

<colgroup><col><col><col><col span="2"><col><col span="3"><col><col span="4"></colgroup><tbody>
</tbody>

 

[citibond]

Thanks for the reply but I also need the complete itemised list of calls and then need the Total