How to email personalised spreadsheet to list of addresses?

  • Thread starter Thread starter vic1
  • Start date Start date
V

vic1

I have studied Ron de Bruin's pages (excellent) but, unless I have missed
something, I cannot find a solution to the following:

To conduct an audit of equipment existing on multiple sites, I need to
create a template for a 'blank' spreasheet which I wish to email to each
site, for them to fill in and return to me.

Ideally, I would like this template to be 'personalised' for each email
address so that the site reference and name is already completed (ie. so they
can't forget to fill it in themselves!).

I envisage that it should be possible to have one worksheet with the basic
template and a second worksheet listing all the recipients' email addresses,
together with their site names and reference numbers and then create a macro
which completes the individual site name and reference number on the template
at the same time as sending the email.

Is there a way to do this?

Many thanks for your efforts, which I have been very grateful for in the past!
 
You hit the nail on the head in your descrition above...

set up 2 tabs int eh workbook... 1 as the template 1 as the "database"

CReate a macro to loop through the database tab and for each record,
copy and paste the row to the template tab. then copy the template tab
to a new workbook, name it and save it. Finally, clear the template
tab and get the next record.


You can write most of the macro above by just recording the steps for
the 1st record.

the go back and add the code to loop through the database (i.e.)

for i = 1 to
sheets("database").Range("a1").SpecialCells(xlCellTypeLastCell).Row
if cells(i,1).value = "" then
msgbox "no more rows"
exit sub
end if

'code here to copy row from this tab to template tab for example
Sheets("Template").Range("A" & i).value = cells(i,1).value
Sheets("Template").Range("B" & i).value = cells(i,2).value
'...

next
 
Thanks Tim. It makes sense. I'm not very proficient at vb code and I will
probably not have chance to do it until Monday - but I'll let you know how I
get on!
 
I'm sorry! I'll have to admit defeat. I got as far as recording the first
part of the macro - but then I got lost, specifically on your line: "for i =
1". I really am green at this could you be a bit more specific please?

Thank you in advance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top