Macro to create new sheets and e-mail them.

Billy

New Member
Joined
Feb 28, 2002
Messages
4
I'm on a mission to break up a bunch of data and get it e-mailed to the appropriate people for editing. I have data sorted by username in an Excel workbook. My idea is place each user's range into a new and separate workbook, then save each workbook with the username (cell A1 in each sheet), then e-mail each sheet. Can anyone tell me if this can be done, and if so, get me started in the right direction?
Any thoughts on this are appreciated.
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
This is what I would do.

1)I would recommend using "Named" cell references to ensure the correct data is transferred, especially if it is not in the same range everytime.

2) Record a macro like this...Open the source sheet copy the range(select named range if possible), Open a new sheet, Paste the data, do a "Save as"(whatever you want to call it) then close the sheet. Go back to the source sheet, copy the next range and follow the same steps again. when your finished with the last sheet(closed) stop your recording.

3) it's up to you how you want the macro to run I prefer to close.

4)Now go back and edit the Code, paste this code just before the "ActiveWindow.close" code for each of the different files you are sending.

This Code:


ActiveWorkbook.SendMail Recipients:="Your e-mail", Subject:="Goes Here", ReturnReceipt:=True
MsgBox "Your Request Has Been Sent", , "Title Goes Here"

It will send to each address you put in, the macro will overwrite the files each time

It should work (I tried )but it depends on your preferences (you could probably tie it to abutton on your source sheet)

Ziggy
 
Upvote 0
Hi Billy

Try some code like this to get you started, it's not tested but should work. It assumes that the ranges you want to seperate are named and all begin with an "X". This saves using a lengthy Select Case or If Statement.

It also assumes the name you wish to call each sheet and Workbook created is in cell A1 of the sheet housing the named range.


Sub SeperateAndMail()
Dim nRange As Name
Dim strFileName As String
For Each nRange In ThisWorkbook.Names
If Left(nRange.Name, 1) = "X" Then

Sheets.Add().Name = nRange.Name

strFileName = Sheets(nRange.Parent.Name).Range("A1")

Range(nRange.Name).Copy Destination:= _
Sheets(nRange.Name).Range("A1")

Sheets(nRange.Name).Copy 'Creates one sheet Workbook
ActiveWorkbook.SaveAs strFileName
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close
End If
Next nRange
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top