Prev: Can you name a group of cells?
Next: Compatibility Checker
From: Dar on 25 Feb 2010 12:47
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?
From: JLatham on 25 Feb 2010 17:09
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

"Dar" wrote:

> 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?
 | 
Pages: 1
Prev: Can you name a group of cells?
Next: Compatibility Checker