G
Guest
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
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