You have the code to split your data so you have seperate sheets now named after the customer code.
Now create a table in a new worksheet in your workbook named "LookupTable" with in column A the
customer code and in column B the mail address.
Try this test macro
If it is working OK you can change .Display in the macro to .Send
Sub Mail_Every_Worksheet()
'Working in 2000-2007
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim MailAdress As String
TempFilePath = Environ$("temp") & "\"
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsm": FileFormatNum = 52
End If
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
For Each sh In ThisWorkbook.Worksheets
MailAdress = ""
On Error Resume Next
MailAdress = Application.WorksheetFunction.VLookup(sh.Name, Sheets("LookupTable").Range("A1:B3"), 2, False)
On Error GoTo 0
If MailAdress Like "?*@?*.?*" Then
sh.Copy
Set wb = ActiveWorkbook
TempFileName = "Sheet " & sh.Name & " of " _
& ThisWorkbook.Name & " " & Format(Now, "dd-mmm-yy h-mm-ss")
Set OutMail = OutApp.CreateItem(0)
With wb
.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum
On Error Resume Next
With OutMail
.To = MailAdress
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add wb.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.display 'or use .Display
End With
On Error GoTo 0
.Close SaveChanges:=False
End With
Set OutMail = Nothing
Kill TempFilePath & TempFileName & FileExtStr
End If
Next sh
Set OutApp = Nothing
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
Yes it is possible but if you are not the only user you must add a lot of error checking
If you split the data the sheets will be names after every unique value in the column.
This name must me in the seperate workbook so you can use a Vlookup formula to find the
sheet name and get the mail address.
If you filter on a different column the next time this will not work
So your filter column must be fixed and the unique values in this column must be in the Vlookup table
--
Regards Ron de Bruinhttp://
www.rondebruin.nl/tips.htm
...
read more »- Hide quoted text -
- Show quoted text -
Gr8....this can b done.......
Column A is always for customer code and same we can keep for
permanant.....what is the next step....
How can i send mail through outlook....is there any macro need to be
define in outlook. I already prepare a file of mail address of client
according to their client code. and what if the report if not found
for any given date for particular client..what msg does outlook send
for that
1").Select