Below is Macro that I created with the help of this site along with some
reference books. The Macro below Prints a specific range in my worksheet,
Copies a Worksheet to a new Work Book, Saves that Workbook with a specific
cell located within that workbook. I would now like the Macro to be able to
e-mail that newly created workbook file on to an e-mail recipient and I am
not sure what the code would be. I thought about embedding the e-mail address
somewhere on the Worksheet and use that cells location for the macro to
identify the e-mail address. Does anyone have any suggestions and or help.

Sub Print_sheets_Click()
Dim position, max As Integer
Dim CurrentWorkbook As Workbook
Dim NewWorkbook As Workbook
Dim Rng As Range

'setting the print area
ActiveSheet.PageSetup.PrintArea = "$AB$2:$am$58"

'initialize beginning provider
position = Range("s3")

'get maximum number of providers from excel sheet
max = Range("t3")

MsgBox position & "----" & max

Do Until position max 'check if max was reached yet

'change number sequentially in Cell n3
Range("n3") = position

'sending out put to the printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' Saves Individual Provider Spreadsheets
Set CurrentWorkbook = ActiveWorkbook
Set NewWorkbook = Workbooks.Open(Filename:="Test.xls")
CurrentWorkbook.Sheets(Array("E-Mail Sheet")).Copy
after:=NewWorkbook.Worksheets(1)
Set Rng = Sheets("E-Mail Sheet").Range("g1")
ActiveWorkbook.SaveAs _
Filename:=Rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal
NewWorkbook.Close SaveChanges:=True

'get next provider
position = position + 1

Loop

End Sub


Thanks
Theo

Hi Theo

See my site for a few examples to send one sheet
http://www.rondebruin.nl/sendmail.htm

--

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


"Theo Degr" wrote in message ...
Below is Macro that I created with the help of this site along with some
reference books. The Macro below Prints a specific range in my worksheet,
Copies a Worksheet to a new Work Book, Saves that Workbook with a specific
cell located within that workbook. I would now like the Macro to be able to
e-mail that newly created workbook file on to an e-mail recipient and I am
not sure what the code would be. I thought about embedding the e-mail address
somewhere on the Worksheet and use that cells location for the macro to
identify the e-mail address. Does anyone have any suggestions and or help.

Sub Print_sheets_Click()
Dim position, max As Integer
Dim CurrentWorkbook As Workbook
Dim NewWorkbook As Workbook
Dim Rng As Range

'setting the print area
ActiveSheet.PageSetup.PrintArea = "$AB$2:$am$58"

'initialize beginning provider
position = Range("s3")

'get maximum number of providers from excel sheet
max = Range("t3")

MsgBox position & "----" & max

Do Until position max 'check if max was reached yet

'change number sequentially in Cell n3
Range("n3") = position

'sending out put to the printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' Saves Individual Provider Spreadsheets
Set CurrentWorkbook = ActiveWorkbook
Set NewWorkbook = Workbooks.Open(Filename:="Test.xls")
CurrentWorkbook.Sheets(Array("E-Mail Sheet")).Copy
after:=NewWorkbook.Worksheets(1)
Set Rng = Sheets("E-Mail Sheet").Range("g1")
ActiveWorkbook.SaveAs _
Filename:=Rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal
NewWorkbook.Close SaveChanges:=True

'get next provider
position = position + 1

Loop

End Sub


Thanks
Theo

Ron de Bruin,

I looked at the sight and thanks. Where would I put the code that identifies
the cell where the e-mail address is?

Thanks
Theo

"Ron de Bruin" wrote:

Hi Theo

See my site for a few examples to send one sheet
http://www.rondebruin.nl/sendmail.htm

--

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


"Theo Degr" wrote in message ...
Below is Macro that I created with the help of this site along with some
reference books. The Macro below Prints a specific range in my worksheet,
Copies a Worksheet to a new Work Book, Saves that Workbook with a specific
cell located within that workbook. I would now like the Macro to be able to
e-mail that newly created workbook file on to an e-mail recipient and I am
not sure what the code would be. I thought about embedding the e-mail address
somewhere on the Worksheet and use that cells location for the macro to
identify the e-mail address. Does anyone have any suggestions and or help.

Sub Print_sheets_Click()
Dim position, max As Integer
Dim CurrentWorkbook As Workbook
Dim NewWorkbook As Workbook
Dim Rng As Range

'setting the print area
ActiveSheet.PageSetup.PrintArea = "$AB$2:$am$58"

'initialize beginning provider
position = Range("s3")

'get maximum number of providers from excel sheet
max = Range("t3")

MsgBox position & "----" & max

Do Until position max 'check if max was reached yet

'change number sequentially in Cell n3
Range("n3") = position

'sending out put to the printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' Saves Individual Provider Spreadsheets
Set CurrentWorkbook = ActiveWorkbook
Set NewWorkbook = Workbooks.Open(Filename:="Test.xls")
CurrentWorkbook.Sheets(Array("E-Mail Sheet")).Copy
after:=NewWorkbook.Worksheets(1)
Set Rng = Sheets("E-Mail Sheet").Range("g1")
ActiveWorkbook.SaveAs _
Filename:=Rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal
NewWorkbook.Close SaveChanges:=True

'get next provider
position = position + 1

Loop

End Sub


Thanks
Theo

Click on the Tip link that is on every page
For example
http://www.rondebruin.nl/mail/tips1.htm

Or if you use the Outlook examples
http://www.rondebruin.nl/mail/tips2.htm


--

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


"Theo Degr" wrote in message ...
Ron de Bruin,

I looked at the sight and thanks. Where would I put the code that identifies
the cell where the e-mail address is?

Thanks
Theo

"Ron de Bruin" wrote:

Hi Theo

See my site for a few examples to send one sheet
http://www.rondebruin.nl/sendmail.htm

--

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


"Theo Degr" wrote in message ...
Below is Macro that I created with the help of this site along with some
reference books. The Macro below Prints a specific range in my worksheet,
Copies a Worksheet to a new Work Book, Saves that Workbook with a specific
cell located within that workbook. I would now like the Macro to be able to
e-mail that newly created workbook file on to an e-mail recipient and I am
not sure what the code would be. I thought about embedding the e-mail address
somewhere on the Worksheet and use that cells location for the macro to
identify the e-mail address. Does anyone have any suggestions and or help.

Sub Print_sheets_Click()
Dim position, max As Integer
Dim CurrentWorkbook As Workbook
Dim NewWorkbook As Workbook
Dim Rng As Range

'setting the print area
ActiveSheet.PageSetup.PrintArea = "$AB$2:$am$58"

'initialize beginning provider
position = Range("s3")

'get maximum number of providers from excel sheet
max = Range("t3")

MsgBox position & "----" & max

Do Until position max 'check if max was reached yet

'change number sequentially in Cell n3
Range("n3") = position

'sending out put to the printer
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

' Saves Individual Provider Spreadsheets
Set CurrentWorkbook = ActiveWorkbook
Set NewWorkbook = Workbooks.Open(Filename:="Test.xls")
CurrentWorkbook.Sheets(Array("E-Mail Sheet")).Copy
after:=NewWorkbook.Worksheets(1)
Set Rng = Sheets("E-Mail Sheet").Range("g1")
ActiveWorkbook.SaveAs _
Filename:=Rng.Value & ".xls", _
FileFormat:=xlWorkbookNormal
NewWorkbook.Close SaveChanges:=True

'get next provider
position = position + 1

Loop

End Sub


Thanks
Theo