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
[TABLE="width: 500"]
<tbody>[TR]
[TD]Tel No[/TD]
[TD]Name[/TD]
[TD]Dept[/TD]
[TD]Contract Type[/TD]
[TD]Expiry[/TD]
[TD]Model[/TD]
[TD]Line Rental[/TD]
[TD]Email Address[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]XYZ[/TD]
[TD]XXX[/TD]
[TD]Blackberry[/TD]
[TD]23/09/2014[/TD]
[TD]9790[/TD]
[TD]10[/TD]
[TD]xyz@gmail.com[/TD]
[/TR]
[TR]
[TD]09088888[/TD]
[TD]YYY[/TD]
[TD]YYY[/TD]
[TD]Iphone[/TD]
[TD]23/08/2015[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]yyy@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]
Spreadsheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Tel No[/TD]
[TD]Number Called[/TD]
[TD]Duration[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]6565625256[/TD]
[TD]00:01:54[/TD]
[TD]0.23[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]6871268618[/TD]
[TD]00:03:32[/TD]
[TD]0.34[/TD]
[/TR]
[TR]
[TD]09088888[/TD]
[TD]7681276817[/TD]
[TD]00:04:01[/TD]
[TD]0.20[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]8767378783[/TD]
[TD]00:01:03[/TD]
[TD]0.30[/TD]
[/TR]
[TR]
[TD]09088888[/TD]
[TD]87289798279[/TD]
[TD]00:08:03[/TD]
[TD]0.67[/TD]
[/TR]
</tbody>[/TABLE]
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.
Spreadsheet 1 Fields
[TABLE="width: 500"]
<tbody>[TR]
[TD]Tel No[/TD]
[TD]Name[/TD]
[TD]Dept[/TD]
[TD]Contract Type[/TD]
[TD]Expiry[/TD]
[TD]Model[/TD]
[TD]Line Rental[/TD]
[TD]Email Address[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]XYZ[/TD]
[TD]XXX[/TD]
[TD]Blackberry[/TD]
[TD]23/09/2014[/TD]
[TD]9790[/TD]
[TD]10[/TD]
[TD]xyz@gmail.com[/TD]
[/TR]
[TR]
[TD]09088888[/TD]
[TD]YYY[/TD]
[TD]YYY[/TD]
[TD]Iphone[/TD]
[TD]23/08/2015[/TD]
[TD]4[/TD]
[TD]15[/TD]
[TD]yyy@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]
Spreadsheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Tel No[/TD]
[TD]Number Called[/TD]
[TD]Duration[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]6565625256[/TD]
[TD]00:01:54[/TD]
[TD]0.23[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]6871268618[/TD]
[TD]00:03:32[/TD]
[TD]0.34[/TD]
[/TR]
[TR]
[TD]09088888[/TD]
[TD]7681276817[/TD]
[TD]00:04:01[/TD]
[TD]0.20[/TD]
[/TR]
[TR]
[TD]09092222[/TD]
[TD]8767378783[/TD]
[TD]00:01:03[/TD]
[TD]0.30[/TD]
[/TR]
[TR]
[TD]09088888[/TD]
[TD]87289798279[/TD]
[TD]00:08:03[/TD]
[TD]0.67[/TD]
[/TR]
</tbody>[/TABLE]
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.