Loop through email address list to send e-mails

Hi Every one,

Following is a code that prints out weekly individual task lists from a 
master Critical Path.

The code first creates a list of unique individuals on a temporary page, 
-then filter my critical path in a Column called "Next week" to only show 
action requiring follow-up on following week. 
-then prints-out a list of individuals who will receive task lists, 
-and finaly loops through alll values in "MyUniqueRng" to filter and print 
out the list name by name.

What I would like to do, is instead of Printing-out these individuals task 
lists, 
sending them by e-mail whith outlook
Provided that all names are listed on another separate sheet (Whole list of 
employees) and that I would write their e-mail addresses on a column at the 
right of the "Name" column, I assume that by a loop through the range 
"MyUniqueRng" combined to a V-Lookup these addresses could easily be pasted 
in outlook to send individual e-mails.

It would be great If somebody could assist me in this matter.

Thanks,

Paul



Sub Print_Next_Weeek_Task_Lists()

Application.ScreenUpdating = False
    Dim newWks As Worksheet
    Dim curWks As Worksheet
    Dim myRng As Range
    Dim myRng2 As Range
    Dim myUniqueRng As Range
    Dim myCell As Range
    
    
    Set curWks = Sheets("Critical Path")
    Set newWks = Worksheets.Add
    
    With curWks
        .AutoFilterMode = False
        Set myRng = .Range("A6", .Cells.SpecialCells(xlCellTypeLastCell))
        Set myRng2 = .Range("A5", .Cells.SpecialCells(xlCellTypeLastCell))
        myRng2.AutoFilter Field:=16, Criteria1:="<>"
        myRng.Columns(4).Copy _
            Destination:=newWks.Range("a1")
        With newWks
            .Range("a1", .Cells(.Rows.Count, "a")).AdvancedFilter _
                Action:=xlFilterCopy, CopyToRange:=.Range("b1"), Unique:=True
            .Range("b:b").Sort Key1:=Range("b1"), _
                Order1:=xlAscending, Header:=xlYes, _
                OrderCustom:=1, MatchCase:=False, _
                Orientation:=xlTopToBottom
            Set myUniqueRng = .Range("b1", .Cells(.Rows.Count, "b").End(xlUp))
        End With 
        
        With Sheets("Task List Distribution NW") ' Prints Task List 
Distribution Record
        myUniqueRng.Copy
        Sheets("Task List Distribution NW").Select
        Range("A7").PasteSpecial (xlPasteValues)
        .PrintOut Copies:=1, preview:=False
        Range("A7:A60").ClearContents
        End With
        
    
        .Range("L4").Value = "Next Week"                                    
        For Each myCell In myUniqueRng.Cells                               
<<   L
            myRng2.AutoFilter Field:=4, Criteria1:=myCell.Value       <<  O
            .Range("O3").Value = myCell.Value                                
 << O
            .PrintOut Copies:=1, preview:=False                              
 <<  P
        Next myCell
        
        .Range("O3:P3").ClearContents
        .Range("L4").ClearContents
        If .FilterMode Then
            .ShowAllData
        End If
        
    End With
    
    Application.DisplayAlerts = False
    newWks.Delete
    Application.DisplayAlerts = True
   Application.ScreenUpdating = True

End Sub
0
Paul297 (622)
4/12/2005 7:37:02 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
667 Views

Similar Articles

[PageSpeed] 44

There are people here who are clever enough to help you handle this in Excel, 
but I'll point out that Word XP (maybe earlier versions, too) have an option 
to mailmerge to Outlook emails.  It sounds pretty easy, and may be easier 
than handling it all in Excel.



"Paul." wrote:

> Hi Every one,
> 
> Following is a code that prints out weekly individual task lists from a 
> master Critical Path.
> 
> The code first creates a list of unique individuals on a temporary page, 
> -then filter my critical path in a Column called "Next week" to only show 
> action requiring follow-up on following week. 
> -then prints-out a list of individuals who will receive task lists, 
> -and finaly loops through alll values in "MyUniqueRng" to filter and print 
> out the list name by name.
> 
> What I would like to do, is instead of Printing-out these individuals task 
> lists, 
> sending them by e-mail whith outlook
> Provided that all names are listed on another separate sheet (Whole list of 
> employees) and that I would write their e-mail addresses on a column at the 
> right of the "Name" column, I assume that by a loop through the range 
> "MyUniqueRng" combined to a V-Lookup these addresses could easily be pasted 
> in outlook to send individual e-mails.
> 
> It would be great If somebody could assist me in this matter.
> 
> Thanks,
> 
> Paul
> 
> 
> 
> Sub Print_Next_Weeek_Task_Lists()
> 
> Application.ScreenUpdating = False
>     Dim newWks As Worksheet
>     Dim curWks As Worksheet
>     Dim myRng As Range
>     Dim myRng2 As Range
>     Dim myUniqueRng As Range
>     Dim myCell As Range
>     
>     
>     Set curWks = Sheets("Critical Path")
>     Set newWks = Worksheets.Add
>     
>     With curWks
>         .AutoFilterMode = False
>         Set myRng = .Range("A6", .Cells.SpecialCells(xlCellTypeLastCell))
>         Set myRng2 = .Range("A5", .Cells.SpecialCells(xlCellTypeLastCell))
>         myRng2.AutoFilter Field:=16, Criteria1:="<>"
>         myRng.Columns(4).Copy _
>             Destination:=newWks.Range("a1")
>         With newWks
>             .Range("a1", .Cells(.Rows.Count, "a")).AdvancedFilter _
>                 Action:=xlFilterCopy, CopyToRange:=.Range("b1"), Unique:=True
>             .Range("b:b").Sort Key1:=Range("b1"), _
>                 Order1:=xlAscending, Header:=xlYes, _
>                 OrderCustom:=1, MatchCase:=False, _
>                 Orientation:=xlTopToBottom
>             Set myUniqueRng = .Range("b1", .Cells(.Rows.Count, "b").End(xlUp))
>         End With 
>         
>         With Sheets("Task List Distribution NW") ' Prints Task List 
> Distribution Record
>         myUniqueRng.Copy
>         Sheets("Task List Distribution NW").Select
>         Range("A7").PasteSpecial (xlPasteValues)
>         .PrintOut Copies:=1, preview:=False
>         Range("A7:A60").ClearContents
>         End With
>         
>     
>         .Range("L4").Value = "Next Week"                                    
>         For Each myCell In myUniqueRng.Cells                               
> <<   L
>             myRng2.AutoFilter Field:=4, Criteria1:=myCell.Value       <<  O
>             .Range("O3").Value = myCell.Value                                
>  << O
>             .PrintOut Copies:=1, preview:=False                              
>  <<  P
>         Next myCell
>         
>         .Range("O3:P3").ClearContents
>         .Range("L4").ClearContents
>         If .FilterMode Then
>             .ShowAllData
>         End If
>         
>     End With
>     
>     Application.DisplayAlerts = False
>     newWks.Delete
>     Application.DisplayAlerts = True
>    Application.ScreenUpdating = True
> 
> End Sub
0
DukeCarey (494)
4/12/2005 11:41:55 AM
Reply:

Similar Artilces:

Attaching Contacts to new email
Creating a new email. When contacts folder has "shared" contacts and "personal" contacts how can you set your personal contacts as the default? Example: creating a new email having never addressed the "send to" contact before, you hit the "To" button. Currently my "shared" contacts opens up but I would like my "personal" contacts page to open instead of having to drop down to "contacts" to bring up that list. Is there a solution to this? Thank you!!! On 2/26/2010 10:21 AM, assistantneedshelp wrote: >...

Some Emails Cannot be Delivered
I have a problem that I cannot put a handle on with my exchange server. Some outbound messages do not reach their destination. The problem happens with certain destinations. However the confusing part is that some messages are able to make it through. This would mean that there are no problems with DNS or MX lookup or any connectivity issue since some emails manage to make it through. I'm at a loss and can't figure where to look first I hope that someone might have an idea. You'll have to provide more information. Are you receiving NDrs if so can you share them? Is ther...

Recieving email with nothing in them? Blank??
From time to time, I will recieve email from friends, family, or business...and when I get the email, I open it up to find nothing but white space. I can't find a common thing between emails. I just know it is very frustrating when I have to ask people to resend the email to me or to a new address. When I look at my mail on the server, it is fine. It is when I open Outlook and it downloads it to my desktop. I get nothing. Any ideas? Cheers.. vg Sorry, wanted to add that I am using OL2003 with WINXP and everything is updated SP wise. I am starting to see some articles on this...

Outlook 0x800ccc0d error when Norton e-mail protect enabled: see hosts
This post is made to help others solve this issue, based on my experience. Symptom: - Outlook works perfectly well when Norton Anti-Virus e-mail protection is disabled - Outlook cannot retrieve incoming messages when NAV e-mail protection is enabled, message being: pop3 server not found, error 0x800ccc0d This symptom, and possible solutions, are exactly the object of Symantec support note: http://service1.symantec.com/SUPPORT/nav.nsf/docid/2000020716064206 Please read this note first ! The object of this post is to add another possible solution to this problem. NAV email protection sets up...

invalid email address
In message to Candy FH Muffman wrote of having an invalid email address. I use hotmail but would like to prevent ti being used by spammers. Is there any way I can hide it or restrict it in some way? Can I make it invalid? Thank you You mean when posting to an online service like this? Sure, don't type your (correct) email address. See my address or from many others to see an example. Note: I've removed your valid address in my reply. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add...

Sorting emails by domains, from org to edu (right char is the most significant)
Hello All I need to sort the domains according their emails. For example: Before sorting: john1@abc.edu john3@abc.org john5@abc.com john4@bcd.org john2@bcd.edu john6@bcd.com After sorting: john3@abc.org john4@bcd.org john5@abc.com john6@bcd.com john1@abc.edu john2@bcd.edu That is, how to sort, according to the domain name ( the right is the most significant )? Thanks. Z. D. On Feb 15, 11:09 pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote: > That is, how to sort, according to the domain name ( the right is the > most significant )? you'll probably need ...

Moving incoming mail automatically to specific folders.
I have three separate E-Mail Accounts (1,2, and 3) I have created three subfolders under Inbox (Mail1, Mail2, Mail3) How can I automatically get the mail sent to the correct Mail in location (Mail1,Mail2 or Mail3). I am sure the answer like everything is easy when you know how but I have just spent an hour trying! Thanks Ted On Mon, 2 Jan 2006 00:24:44 -0000, "EddyStone" <teds@screaming.net> wrote: >I have three separate E-Mail Accounts (1,2, and 3) >I have created three subfolders under Inbox (Mail1, Mail2, Mail3) >How can I automatically get the mail sent to ...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

outlook 97 and express email problems
Hi, I am currently on an IBM X21 laptop and is running windows 98 se with office 97 pro. I recently experienced some problems with outlook (illegal operations etc) and reinstalled office to fix the problem but since then I have not been able to send or recieve emails with outlook 97 and outlook express 6. I simply get an error message saying the host can't be found (but does exist and I can ping it successfully). Any suggestions on what I might do? I have tried creating new accounts in windows mail and outlook express, but I still get the same error. Thankyou in advance! Tim D...

Having problem with spoofing email
Our users just received multiple email from different users outside the company. In the To: line, it shows his user name correctly but when he print those email, the To: line was showing somebody else name on the print out. Is there a way to block this behavior? I'm using E2k3. For some reason our spam (postini) didn't pick up these emails. Thank you, Could you please post the message in raw format (including the mail headers) Petch wrote: > Our users just received multiple email from different users outside the > company. In the To: line, it shows his user name cor...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

Outlook 2003 Drag and Drop Emails
I have an issue where there is a SBS 2003 server (newly installed) & when I drag emails to the file system (explorer window) in order to create file records of the emails it generates an error. Dialog Box Name: Error Copying File or Folder Error Msg: Not enough storage is available to process this command. I can't find an error logged anywhere, either on the server event logs or on the local machine event logs... I have searched the MS KB & Office online, but no joy yet... If anyone can help that would be great!!! R ...

Emails #3
Hi, I was wondering if anyone knew of any web based email provder that I could use that wont be bloked but the I.T Filer at my work. I require use of emails during the day for personal use but work emails are monitored. I have tired various sites i.e Hotmail, Yahoo, Gmail, lycos etc but they are all blocked. Does anyone know of any that may not be picked up buy the web filer. Fiona Fiona, It is difficult to answer because it depends on what your filter is and how it is monitored. For instance, if it is actively monitored and somebody found out you were accessing the site, then it coul...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

setup Windows Mail as Word 2003 default emailer
All I can do is setup Outlook. I do not use Outlook. I would like to email Word docs using MS Windows Mail (new version of Express) In the Windows Start area, type Regedit into the search bar and then start the Registry Editor and go to HKEY_CURRENT_USER>Software>Clients>Mail Right Click on the (Default) item and then on Modify and in the Value data: field enter Windows Mail so that after you click OK, you have (Default) REG_SZ WIndows Mail -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a pa...

How do I email a multipage-document from Publisher?
You don't - unless you use a .pdf file. -- JoAnn Paules MVP Microsoft [Publisher] "Ana" <Ana@discussions.microsoft.com> wrote in message news:1B0AD79E-E023-4E70-967A-3812A35F7E07@microsoft.com... > ...

q Emailing Excel Sheet With Outlook
I am trying to send a table from Excel within the body of an Outlook (lastest versions) to be sent as a fax. Am able to do this, BUT when the document prints out on the fax machine the formating is off (too big for the sheet). I am flexible on changing the method I send it to the fax machine, however it must be sent to the fax via macro. Below is the code I am using. Any help would be greatly appreciated. David Public Sub DoIt() 'On Error GoTo Handler Dim EmailAddress(0 To 2) As String Dim Count As Integer Dim N As Integer Dim sRec1(0) As String Dim sRec2(0 To 1) As...

OE autodelete OLD mail
How do I have Outlook Express automatically delete 90-Day old messages from the deleted items folder? Thanks, Matthew This is a group to support Outlook from the Office group of programs. Outlook Express is a part of Internet Explorer and is a quite different program, despite its similar name.. You will probably get a faster and more expert answer if you post this to an Outlook Express news group. Try posting in one of these newsgroups: microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress for OE 5.5x microsoft.public...

ERROR: 5.1.2 or other punctuation after the recipient's email address.
I am trying to send a email to a group address I created with 58 addresses in it. When I try to send the email to this group I get the following Error: "5.1.2 or other punctuation after the recipient's email address. " I have gone back to the original contact cards and made sure the addresses were all legal. I have re-created the list, same problem I have re-booted, same thing. Any thoughts out there? Thanks Dunc My wife and I have MBP's and are both running MS Office for Mac's, Entourage 12.2.3. Both of us have messages cued up in the out boxes th...

outlook 2007 e-mail
how do you stop e-mails from automatically downloading when you open outlook 2007. i would like to be able to open outlook 2007 and have it wait for me to select 'receive' before downloading e-mails to my system. i know there is a way to do it in outlook 2003 but i do not remember how to do it there and i cannot find how to do it in outlook 2007. -- emerson Uncheck the option to send/receive at a specific interval. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted w...

Reply To Email after installing CRM Outlook Client
After installing the CRM Outlook Client, when opening an email and selecting REPLY, the current windows looses focus. I know this sounds minor but many of our power users are in the habit of hitting REPLY and typing without even looking at the screen. It is not until they look up do they realize that the window no longer has focus thus everything they thought they typed now has to be retyped. Has anyone seen this and know of a quick fix? Thanks in Advance -- Kenneth Clebak Kenneth Have you resolved this? I have the same problem, and it is VERY annoying. Saira "Kenneth Clebak&...

mail queues for long on Exchange 2000 server time before being sent
I have a situation where all outgoing mail sits in a queues on the Exchange 2000 server (one per domain) for about 45 minutes before being sent. The queues in question have a status of ready. There are also other queues of that are retries (mostly NDRs from junk mail). We are only a small company and the amount of mail is not great. What might Exchange be waiting for before sending this mail? Is there something I can tweak to make it send mail sooner? Thanks for any help, Angus. Angus, Where does the mail go after it leaves your Exchange server, does it go to a smarthost/isp or d...

How to get to email from Word
Sorry to break the thread, but replying to the original thread now produces an error message :( The suggested changes don't make any difference to the essential premise that it all works as long as Outlook is running in the background. Stepping though the code has provided inconsistent results. It usually crashes at the line strEMail = Application.GetAddress("", strEMail, False, 1, , , True, True) when the address is selected from the dialog and you OK out of the dialog; but I have noticed that this call has produced crashes in other routines where Outlook is not running. Th...

Sent emails not logged in Sent Items (Outlook 2010)
I am using the Beta version of Outlook 2010 with Windows 7 Pro (64 bit). Sent emails are not logged in the Sent Items folder nor do saved drafts appear in the Drafts folder. I have confirmed that the relevant settings are checked in the Mail Settings. Any ideas what I can do to solve this problem? -- Stephen Newton "snewton" <snewton@discussions.microsoft.com> wrote in message news:F7345EA6-9E42-4DF7-AFA5-AD2DF2CA840D@microsoft.com... >I am using the Beta version of Outlook 2010 with Windows 7 Pro (64 bit). > Sent emails are not logged in the Sent I...

I cannot get Outlook to send/receive. Error says object not found
I just installed Outlook2007. I cannot get the send/receive to work. That is when the error says Object not found. If I go to accounts and properties, I run the two tests and they complete fine. What should I try next? Outlook 2007 is the only new program, all the rest are from Office 2003 IMK <IMK@discussions.microsoft.com> wrote: > I just installed Outlook2007. I cannot get the send/receive to work. > That is when the error says Object not found. If I go to accounts and > properties, I run the two tests and they complete fine. What should I > try next? Outlook 2007 is ...