Say I have an Excel 2003 Workbook full of 40 sheets. And I want to sent each sheet to a different email address. I want each recipient to receive a workbook with just their sheet included, not their worksheet converted to html and sent as the message body (which is all I can figure out how to do).

Anyone know how to do this?

7/18/2006 2:19:04 PM

well the hard way would be to copy and paste each sheet into it's own document.

There's probably an easier way, but I don't use excel enough to know it.

7/18/2006 2:20:49 PM

^ lol, why even bother to post that

7/18/2006 2:22:58 PM

because I'm almost as dumb as you.

7/18/2006 2:24:55 PM

^^^ thats what I'm trying to avoid by this post.

I did find this http://www.dicks-clicks.com/excel/olSending.htm#Sending_One_Sheet_as_an_Attachment

but I was hoping to avoid since it involved code (though maybe this would be a good time to start playing with VBA ). Does this code even look like it would work? It was written for Excel 2000...will it work in 2003 too?

Are there any non-code solutions?

7/18/2006 2:29:40 PM

By the time you find the answer, you could have already finished the brute force method. Good to know for the future, I suppose.

7/18/2006 3:54:07 PM

There's proabably a method involving turning the workbook into a database and using Access to do what you want. I have no details, it's just an idea.

7/18/2006 4:31:36 PM

^^^

there's probably no way to do this without code. but don't be scared of VBA - it's your friend.

And i'm your friend too for modifying the code found in that post to work for your needs:

Sub SendAllSheets()

    ' initialize Outlook and mail objects
    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    If Err.Number = 429 Then
        Set olApp = CreateObject("Outlook.application")
    End If
    On Error GoTo 0
    
        
    Dim recipientsArray() As String
    Dim recipientsRange As Range    ' store the email
    Set recipientsRange = Range("addresslist") ' named range in Worksheet1
    
    ReDim recipientsArray(1 To recipientsRange.Rows.Count) ' set array to length of list, starting with index = 1
        
    Dim counter As Integer
    counter = 1
    For Each oneName In recipientsRange     ' SHOULD be able to do something like recipientsArray = Array(recipientsRange)
        recipientsArray(counter) = oneName  '   recipientsArray = recipientsRange.... or something?
        counter = counter + 1
    Next oneName
        
    counter = 2  ' the worksheets to send start with worksheet #2
    For Each Recipient In recipientsArray   ' loop through each recipient in array
        
        ThisWorkbook.Sheets(counter).Copy
        ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & "sheet.xls"
        
        Dim olMail As Object '   MailItem
        Set olMail = olApp.CreateItem(olMailItem)
    
        With olMail
            .Recipients.Add Recipient
            .Subject = "One worksheet"      ' could put different subjects in a named range too, if you want customization
            .Body = "Here is the worksheet " & vbCrLf  ' same with body - can be customized
            .Attachments.Add ActiveWorkbook.FullName
            .Display
            .Send   ' may cause an alert that creates a 5 second delay! Might be better to remove .Send and hit Send manually in Outlook
        End With
    
        ActiveWorkbook.Close False
        Kill ThisWorkbook.Path & "\" & "sheet.xls"
    
        Set olMail = Nothing
        
        counter = counter + 1
    Next Recipient
    
    Set olApp = Nothing

End Sub

7/18/2006 4:52:23 PM

Quote :

"By the time you find the answer, you could have already finished the brute force method. Good to know for the future, I suppose."

If this is a one-time or infrequent thing, I'm going to have to agree. If it's not, you should probably dabble in VBA (or VB.NET if available) to do it.

Outlook 2003 uses a different object model than does Outlook 2000. I haven't messed around with VBA, but I know VB.NET required me to add some references + some other stuff to create/send emails.

7/18/2006 5:49:46 PM

Quote :

"By the time you find the answer, you could have already finished the brute force method. Good to know for the future, I suppose."

thats the idea. I have to send out groups of worksheets pretty often.

^^ thanks for the code, I'll begin playing

7/18/2006 6:04:58 PM