Macro to Email specific fields in a worksheet


T

talonega149

Hello,

I am stuck working on a new macro. Currently, I have a workbook that
emails each worksheet to a specific user (based upon an email address
referenced on each worksheet in field B2. This works great, however,
the new project I am working on has one worksheet (200 entries) that I
need to email each user but only data on the sheet that relates to the
specific user.

For example, the size (23 Columns x 13 Rows) are exactly the same for
each user.

A1:W13 (with B3 containing the email address)
A14:W26 (with B16 containing the email address)
A27:W39 (with B28 containing the email address)

I could setup 200 worksheets and have the block of data (23 Columns x
13 Rows) on each worksheet and use the code I have to email each
sheet, however, it is much too hard to manage that many sheets.

Basically, code should look for an email address, if found, select
rows -2 (up two from email address found) through 11 (total of 13
rows) copy into email and send to email address specified.

Does that make sense? Any assistance would be helpful.
 
Ad

Advertisements

J

Joel

Set up a master sheet with three columns

1) column A the email address
2) Column B the sheet name
2) Column C the address range of the data to be emailed

A B C
(e-mail address removed) Sheet1 A1:D10 (no equal sign, text data)


the your code should look something like this

with sheets("Master")
RowCount = 1
Do While Range("A" & RowCount) <> ""
Set MailRange = _
Sheets(Range("B" & RowCount)).Range(Range("B" & RowCount))
EmaiAddress = Range("A" & RowCount)
'enter you code to send the range of addresses
RowCount = RowCount + 1
loop
end with
 

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

Top