Macro to E-mail a sheet

Lewis

Try this code - I have added explanations to show what is happening.

You may need to amend it to suit.

Sub SendSheet()
Dim myFile As String, s As String
'Copy the sheet to a new workbook ready to mail
ThisWorkbook.ActiveSheet.Copy
'Identify the new book
myFile = ActiveWorkbook.Name
ThisWorkbook.Activate
'Open mail session if not already open
If Not IsNull(Application.MailSession) Then Application.MailLogon
'Identify the sheet containing the mail addresses
With ThisWorkbook.Worksheets("Sheet1").Cells
'Target the cells containing the addresses
For Each c In .Range("B1:B4").Cells
'Ensure the addresses are read as text
s = c.Value
'Mail the document
Workbooks(myFile).SendMail Recipients:=s, Subject:="Here is the sheet", ReturnReceipt:=True
Next c
End With
'Close the temp file without saving
Workbooks(myFile).Close savechanges:=False
End Sub

Any help?

Regards

Robb

Thanks

is it necessary to open a new workbook to do this?

: Hi, : Alpologies for reasking this question. I'm sure it has been answered before. I have not been able to findit. : What I want to do is set up a macro that will E-mail the acive sheet to a list of addresses in a cell or cells on another sheet. : For convenience I want to call this macro to run from sevral others. : Any help gratefully received : Lewis

Lewis

As far as I know, it is necessary to copy the sheet to a new workbook.

Why? Is there some reason you do not wish to do it that way?

Regards

Robb

Thanks is it necessary to open a new workbook to do this? : Lewis : Try this code - I have added explanations to show what is happening. : You may need to amend it to suit. : Sub SendSheet() : Dim myFile As String, s As String : 'Copy the sheet to a new workbook ready to mail : ThisWorkbook.ActiveSheet.Copy : 'Identify the new book : myFile = ActiveWorkbook.Name : ThisWorkbook.Activate : 'Open mail session if not already open : If Not IsNull(Application.MailSession) Then Application.MailLogon : 'Identify the sheet containing the mail addresses : With ThisWorkbook.Worksheets("Sheet1").Cells : 'Target the cells containing the addresses : For Each c In .Range("B1:B4").Cells : 'Ensure the addresses are read as text : s = c.Value : 'Mail the document : Workbooks(myFile).SendMail Recipients:=s, Subject:="Here is the sheet", ReturnReceipt:=True : Next c : End With : 'Close the temp file without saving : Workbooks(myFile).Close savechanges:=False : End Sub : : Any help? : Regards : Robb :

Rob

Just curious it seemed unduly complicated.
I might try without and let you know.

Lewis As far as I know, it is necessary to copy the sheet to a new workbook. Why? Is there some reason you do not wish to do it that way? Regards Robb

: Thanks : is it necessary to open a new workbook to do this? :

Lewis

Code I gave does require the sheet to be copied to a new workbook, As I indicated,
I am not aware of any code to send a sheet without first copying.

Let me know how you get on.

Regards

Robb

Rob Just curious it seemed unduly complicated. I might try without and let you know. : Lewis : As far as I know, it is necessary to copy the sheet to a new workbook. : Why? Is there some reason you do not wish to do it that way? : Regards : Robb

Robb,

I tried various things without sucess. I will repost with a number of other queries and see if anyone else can help

Lewis Code I gave does require the sheet to be copied to a new workbook, As I indicated, I am not aware of any code to send a sheet without first copying. Let me know how you get on. Regards Robb

: Rob : Just curious it seemed unduly complicated. : I might try without and let you know. : Lewis