Send me the workbook with the data and your sheet with mail addresses private.
Be sure that the code you have try is in that workbook.

I will look at it tomorrow then for you after work

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Amol" wrote in message oups.com...
I have alredy added one sheet in the workbook where i have other
sheets of customer details

like first sheet is "lookup table" with client code in A column and
email address in B
Second sheet is of one of client code same for second and third

I am running macro in look up table sheet and nothing happens

No error No Result
i have also given name for column A as "customer code" and for B "mail
address"

What else i have to do.........

Can u mail me the macro enabled macro on


Thanxs






On Jul 29, 3:20 am, "Ron de Bruin" wrote:
If nothing happens then this is not correct I think in your test workbook

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.

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in oglegroups.com...

nothing is happening

macro just run and thats all....no result nothing......

what msg i suppose to get after i run this macro

On Jul 27, 1:04 am, "Ron de Bruin" wrote:



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 Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in ooglegroups.com...

On Jul 27, 12:12 am, "Ron de Bruin" wrote:

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

"Amol" wrote in oglegroups.com...

On Jul 26, 9:32 pm, Amol wrote:

On Jul 26, 9:27 pm, "Ron de Bruin" wrote:

Where are the mail addresses ?
Also in the data ?

We must know a mail address if we want to send the data

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in ooglegroups.com...
On Jul 23, 2:18 pm, "Ron de Bruin" wrote:
I see if I have time to create this macro for you

This is not a simple thing

a) want one file which contains client code in one column & e mail
address of that particular clinet on another

How do you want to do this
Do you have this information ?

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in oglegroups.com...
On Jul 22, 1:56 am, "Ron de Bruin" wrote:
Oops

then use a macro that select every sheet with a mail address as a seperate workbook.

then use a macro that copy every sheet with a mail address into a new workbook and mail it to that address.

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Ron de Bruin" wrote in . gbl...

Hi Amol

Btw my name Ron

1. To make more user friendly, is it possible to make it default to
copy page without defining column range, like u have define as column D

2. For that need one button as "Open file" to open main data sheet &
function like "Ctrl + K " which open box & ask user to enter the
column from which he want to slipt all data"

Do you want to let the user fill in the last column of the range or the Filter column ?

& last

3 can macro send the splited & saved files to respective e mail id's
through Outlook 2003

There is a lot of mail code on my site
http://www.rondebruin.nl/sendmail.htm
You can use for example the sheet example instead of the workbook example and
then use a macro that select every sheet with a mail address as a seperate workbook.

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

"Amol" wrote in oglegroups.com...
On Jul 21, 1:27 am, "Ron de Bruin" wrote:
If you read the code you see

'Fill in the path\folder where you want the new folder with the files
'you can use also this "C:\Users\Ron\test"
MyPath = Application.DefaultFilePath

For the file name

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Value = " _
& cell.Value, ws1.Parent.FileFormat

You can change it to this

'Save the file in the newfolder and close it
WSNew.Parent.SaveAs foldername & " Daily Credit Report " _
& cell.Value, ws1.Parent.FileFormat

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in ooglegroups.com...
On Jul 21, 12:28 am, "Ron de Bruin" wrote:
Then Change the D to your column in this code line

Set rng = ws1.Range("A1:D" & Rows.Count)

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in
ooglegroups.com...
On Jul 20, 11:48 pm, "Ron de Bruin" wrote:
Please read the information on the site and use the correct example

Check if the information in these lines is correct before you run the macro.

Name of the worksheet with your data table
Set ws1 = Sheets("Sheet1") '<<< Change

Data range: A1 is the top left cell of your filter range and the header of the first column,
and D is the last column in the filter range.
Set rng = ws1.Range("A1:D" & Rows.Count)

You can change the filter column to another column, my range starts in A so the 1
in my example is the A column(2 = B, 3 =C,...............)
rng.Columns(1).AdvancedFilter _

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Amol" wrote in
oglegroups.com...
On Jul 20, 11:16 pm, "Ron de

...

read more »- Hide quoted text -

- Show quoted text -