Looping Macros that change

[Dar]

I have 14 worksheets. Each worksheet has a different email address in A2
The macro assoicated with that worksheet references a particular file that
will be emailed, ie.On worksheet 1 C:\Myfiles\FA2E.snp, but on worksheet2 the
file will be C:\Myfiles\FA3E.snp. etc. I need to loop through each worksheet
and have it run a different macro for each sheet.[ I think] unless someone
knows how to enter all 14 files, separate email addresses and loop through
them from one macro.

It's been a bear finding code that would send an email via Lotus Notes with
a file attached without having to hit the send button, but I found one a
forum. That was the first hurdle, now I need to call 14 different macros or
at least loop through 14 different variables.
Any help out ther?

 

[JLatham]

You didn't say where on the worksheets the filename is stored at. But it
really shouldn't matter much, what I'm giving you is a bare-bones skeleton of
the program logic that should work

Sub CreateEmails()
Dim anySheet As Worksheet
Dim eMailAddress as String
Dim File2Email as String

For Each anySheet In ThisWorkbook.Worksheets
eMailAddress = anySheet.Range("A2").Value
'just using X15 as a placeholder - provide current cell reference
FileToEmail = anySheet.Range("X15").Value
'
' your code here to send email via Lotus Notes
'
Next ' go on to next sheet
End Sub