Excel VB Looping and e-mailinh

[cnugent]

I have a file that I need to send to a group of individuals, however each person only sees certain parts of the file. I need help with two parts of this macro.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
I created macros that will hide the sheets that aren't needed for the individuals so that when it is finished the only thing visible is what pertains to that individual. The one part of the macro I am missing is how to email it to this individual when the tweaking part of the macro is done. The email is based on a temporary tab containing their emails and based on how I set up the tweaking of the files it will always be in H2.<o></o>
<o></o>
The second part I am missing is a statement that says when the email is sent delete that person from the temp list and go back to the top of the script and do all the filters over again with the second person on the list. I know this is a loop, but I am unfamiliar with loops and heard they can be dangerous t create so I thought I'd ask. When the temporary tab of emails is empty (except for the header on top) I want the loop to stop.<o></o>
<o></o>
I'm sure to you experts this should be a breeze, but I don't know where to begin.<o></o>
<o></o>
Thanks in Advance for your help<o></o>
<o></o>

 

[Comfy]

The Loop would look something like:

Option Explicit
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long

Set wb = Activeworkbook
Set ws = wb.Sheets("Email Tab Name")

'Assuming that Your emails start in A2 (A1 being the header)-

For i = 2 To ws.Columns(1).Find("*", SearchDirection:=x;Previous).Row
'Info Sheet Formatting Code Here
'Emailing Code Here
'Send Email to
ws.Cells(i,1).Value
Next i

There's no need to worry about deleting the emails unless you really want to.

What programme will you be using for the emailing? Outlook, groupwise......?

 

[cnugent]

Re: Excel VB Looping and e-mailing

Thanks for the quick reply.

I hope this info will give the information we need to finzlize this macro.

I am using outlook to send the email.

The subject line is going to be called "weekly comparison"

The body of the messsage is "here is your weekly comparison"

The email address is actually is H2, Not A2

the reason i want to delete it is that once it deletes the row the way I have the macro is that it will than take the next value on the list and run all the calculations on it...theres a lot of code above that I know could have been written better, but as you can tell I'm not that good at this. So as I fix I just need to delete that row (since it's on a temporary sheet just for that purpose) and than go back to the top of the code once the emai is sent- which i don't know how to do that either

I think I'm in over my head

Thanks for your help

 

[Comfy]

In that case the loop will be updated ever so slightly:

Option Explicit
Dim wb As Workbook
Dim ws As Worksheet
Dim i As Long

Set wb = Activeworkbook
Set ws = wb.Sheets("Email Tab Name")

'Assuming that Your emails start in A2 on the Temporary Tab(A1 being the header)-

For i = ws.Columns(1).Find("*", SearchDirection:=x;Previous).Row To 2 Step -1 
'Info Sheet Formatting Code Here
'Emailing Code Here
'After Sending email Delete Address from List
ws.Cells(i,1).ClearContents
Next i

When deleting items from a Loop it's best to Loop in reverse so start at the last item in the list and finish on the first.

This link contains lots of info about coding the email part: http://www.rondebruin.nl/sendmail.htm

 

[cnugent]

Thanks I think I got it working (at least the email part)

Now on to the looping...