How to automate an email process –ideas and or advice

[mml]

Hello
I have a process that I would like to automate . I am seeking advice on what can be automated if anything
My process is :
I receive a spreadsheet with Students and their assigned clients . The spreadsheet is run each month off site and the numbers of clients assigned to each student will vary from month to month.
The sheet will contain up to 2700 lines / client names in total but split into student group by provider. Instead of printing the spreadsheet I would like to email the client names to the student that they are assigned to.
I have attached a cut down copy if the spreadsheet that I receive. There is a macro to do a “page break” , which groups the client list by each student.
The provider (number) is unique for each student .

The first problems I can see is:
1. I don’t have the student email included in the original spreadsheet. I have an external list for this.
Question would I be able to match the name with the email for that name found in an external list .
The list is very long not sure what can be automated.
I would like to be able send a generic email with client assignments for each separate student attached .
I am open to any advice . I am stumped ,there must be a smarter way to manage this process

[Kenneth Hobs]

I would use autofilter for each unique ID. The visible used range would be sent to RangeToHTML(). https://www.rondebruin.nl/win/s1/outlook/mail.htm

As for the ID match to email address, that can be done but has to be known.

This will get you close. http://www.vbaexpress.com/forum/show...-outlook-email

[mml]

Hi Ken
Thanks for the reply . I'll have a look at the links you have provided . i am sure that there will be questions

[Kenneth Hobs]

Ron's RangeToHTML() routine are here too.

OfficeTalk: Using the Excel Object Model to Send Workbooks and Ranges through E-Mail with Outlook (Part 2 of 2)
and
http://www.rondebruin.nl/win/s1/outlook/bmail2.htm

[mml]

i will look at these also . Thanks for the reply ken

[mml]

I have had rethink and thought if I could get the process running with a smaller book that would be great
I receive a workbook with a list of providers and their assigned client s. At the moment I print each of these out using the page break macro to split them into the different providers for printing .See workbook one
I need to do some simple formatting to remove personal names etc and add a column for the providers comments .
I though that the formatting could be done with a simple macro recording see work book two .( i have added a sheet with the providers ID and email addresses)
I have found some code that would match ID from one sheet to another but as the work sheet is renamed it comes up with an error
If I got to this stage then I could use Kens suggestion of auto filter and emailing workbook based on current range shown

Looking at this request its getting very complicated is there a way to simplify ? Files attached

Dim i As Long, j As Long, lastrow1 As Long, lastrow2 As Long
Dim myname As String
lastrow1 = Sheets(“sheet1”).Range(“A” & Rows.Count).End(xlUp).RowFor i = 2To lastrow1
myname = Sheets(“sheet1”).Cells(i, “A”).ValueSheets(“sheet2”).Activate
lastrow2 = Sheets(“Sheet2”).Range(“A” & Rows.Count).End(xlUp).RowFor j = 2To lastrow2If Sheets(“sheet2”).Cells(j, “A”).Value = myname Then
Sheets(“sheet1”).Activate
Sheets(“sheet1”).Range(Cells(i, “B”), Cells(i, “F”)).Copy
Sheets(“sheet2”).Activate
Sheets(“sheet2”).Range(Cells(j, “D”), Cells(j, “H”)).Select
ActiveSheet.Paste
End If

Application.CutCopyMode = False
Next i
Sheets(“sheet1”).Activate
Sheets(“sheet1”).Range(“A1”).Select
End Sub

Workbook one.xlsmWorkbook Two.xlsm