Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sending Same Sheets to Several Email Recipients (Excel 2000)

    I have copied a macro to send different sheets to different email recipients according to email address in Cell A1 of each sheet. Below are the macro codes. How can I modify it so I can add additional email address to the same sheet, say in Cell B1, C1, or D1 ... Thanks for your help !

    Sub SendMail()
    Dim sh As Worksheet
    Dim wb As Workbook
    Dim strdate As String
    Application.ScreenUpdating = False
    For Each sh In ThisWorkbook.Worksheets
    If sh.Range("a1").Value Like "*@*" Then
    strdate = Format(Now, "dd-mm-yy h-mm-ss")
    sh.Copy
    Set wb = ActiveWorkbook
    With wb
    .SaveAs "Sheet " & sh.Name & " of " _
    & ThisWorkbook.Name & " " & strdate & ".xls"
    .SendMail ActiveSheet.Range("A1").Value, _
    "This is the Subject line"
    .ChangeFileAccess xlReadOnly
    Kill .FullName
    .Close False
    End With
    End If
    Next sh
    Application.ScreenUpdating = True
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sending Same Sheets to Several Email Recipients (Excel 2000)

    If you want to send the workbook to multiple recipients, you must create an array:

    wb.SendMail Recipients:=Array(ActiveSheet.Range("A1"), ActiveSheet.Range("B1")), Subject:="This is the subject line"

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Sending Same Sheets to Several Email Recipients (Excel 2000)

    Check out this site it has lots of examples of sendmail

    http://www.rondebruin.nl/sendmail.htm

    If the names are in C1: C10 for example (this example is from the link above)

    <pre>Dim MyArr As Variant
    MyArr = Sheets("mysheet").Range("c1:c10")
    ActiveWorkbook.SendMail MyArr, _
    "This is the Subject line"</pre>



    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •