+ Reply to Thread
Results 1 to 4 of 4

Email question Please.

  1. #1
    Steved
    Guest

    Email question Please.

    Hello from Steved

    Please I would to know where to put the below code into the Sub Mail_test()
    code which is also below.

    The reason is the worksheet which I call summary has 8 other worksheets
    which read formulas from the summary sheet. I want to please have the other
    sheets
    paste special value. as I tried but not finding any success as the other
    sheets that are emailed still have the formulas hence when opened at the
    destination they see REF!. Thanks for taking time on my important issue.


    Worksheets.Select
    Cells.Copy
    Cells.PasteSpecial xlPasteValues
    Cells(1).Select
    Worksheets(1).Select
    Application.CutCopyMode = False


    Sub Mail_test()
    Dim wb As Workbook
    Dim strdate As String
    Dim Shname As Variant
    Dim Addr As Variant
    Dim N As Integer

    strdate = Format(Now, "dd-mm-yy")
    Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
    "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
    "Swanson Depot (7)", "Panmure Depot (8)")
    Addr = Array("sjdee@clear.net.nz", "sjdee@clear.net.nz",
    "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    "sjdee@clear.net.nz")

    Application.ScreenUpdating = False

    For N = LBound(Shname) To UBound(Shname)
    Sheets(Shname(N)).Copy
    Set wb = ActiveWorkbook

    With wb
    .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
    ".xls"
    .SendMail Addr(N), _
    "Audit Summary Report"
    .Close False
    End With
    Next N
    Application.ScreenUpdating = True
    End Sub




  2. #2
    Ron de Bruin
    Guest

    Re: Email question Please.

    Hi Steve

    You must copy this
    Cells.Copy
    Cells.PasteSpecial xlPasteValues
    Cells(1).Select
    Application.CutCopyMode = False
    after
    Sheets(Shname(N)).Copy

    If this is not working for you send me your test workbook private and i look at
    it on sunday evening.


    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Steved" <Steved@discussions.microsoft.com> wrote in message news:7C22FB6B-9BAB-437D-A919-46A152749E82@microsoft.com...
    > Hello from Steved
    >
    > Please I would to know where to put the below code into the Sub Mail_test()
    > code which is also below.
    >
    > The reason is the worksheet which I call summary has 8 other worksheets
    > which read formulas from the summary sheet. I want to please have the other
    > sheets
    > paste special value. as I tried but not finding any success as the other
    > sheets that are emailed still have the formulas hence when opened at the
    > destination they see REF!. Thanks for taking time on my important issue.
    >
    >
    > Worksheets.Select
    > Cells.Copy
    > Cells.PasteSpecial xlPasteValues
    > Cells(1).Select
    > Worksheets(1).Select
    > Application.CutCopyMode = False
    >
    >
    > Sub Mail_test()
    > Dim wb As Workbook
    > Dim strdate As String
    > Dim Shname As Variant
    > Dim Addr As Variant
    > Dim N As Integer
    >
    > strdate = Format(Now, "dd-mm-yy")
    > Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
    > "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
    > "Swanson Depot (7)", "Panmure Depot (8)")
    > Addr = Array("sjdee@clear.net.nz", "sjdee@clear.net.nz",
    > "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    > "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    > "sjdee@clear.net.nz")
    >
    > Application.ScreenUpdating = False
    >
    > For N = LBound(Shname) To UBound(Shname)
    > Sheets(Shname(N)).Copy
    > Set wb = ActiveWorkbook
    >
    > With wb
    > .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
    > ".xls"
    > .SendMail Addr(N), _
    > "Audit Summary Report"
    > .Close False
    > End With
    > Next N
    > Application.ScreenUpdating = True
    > End Sub
    >
    >
    >




  3. #3
    Steved
    Guest

    Re: Email question Please.

    Thanks Ron sorry about the delay yesterday I could read but the reply part
    was not functioning.

    "Ron de Bruin" wrote:

    > Hi Steve
    >
    > You must copy this
    > Cells.Copy
    > Cells.PasteSpecial xlPasteValues
    > Cells(1).Select
    > Application.CutCopyMode = False
    > after
    > Sheets(Shname(N)).Copy
    >
    > If this is not working for you send me your test workbook private and i look at
    > it on sunday evening.
    >
    >
    > --
    > Regards Ron de Bruin
    > http://www.rondebruin.nl
    >
    >
    > "Steved" <Steved@discussions.microsoft.com> wrote in message news:7C22FB6B-9BAB-437D-A919-46A152749E82@microsoft.com...
    > > Hello from Steved
    > >
    > > Please I would to know where to put the below code into the Sub Mail_test()
    > > code which is also below.
    > >
    > > The reason is the worksheet which I call summary has 8 other worksheets
    > > which read formulas from the summary sheet. I want to please have the other
    > > sheets
    > > paste special value. as I tried but not finding any success as the other
    > > sheets that are emailed still have the formulas hence when opened at the
    > > destination they see REF!. Thanks for taking time on my important issue.
    > >
    > >
    > > Worksheets.Select
    > > Cells.Copy
    > > Cells.PasteSpecial xlPasteValues
    > > Cells(1).Select
    > > Worksheets(1).Select
    > > Application.CutCopyMode = False
    > >
    > >
    > > Sub Mail_test()
    > > Dim wb As Workbook
    > > Dim strdate As String
    > > Dim Shname As Variant
    > > Dim Addr As Variant
    > > Dim N As Integer
    > >
    > > strdate = Format(Now, "dd-mm-yy")
    > > Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
    > > "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
    > > "Swanson Depot (7)", "Panmure Depot (8)")
    > > Addr = Array("sjdee@clear.net.nz", "sjdee@clear.net.nz",
    > > "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    > > "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    > > "sjdee@clear.net.nz")
    > >
    > > Application.ScreenUpdating = False
    > >
    > > For N = LBound(Shname) To UBound(Shname)
    > > Sheets(Shname(N)).Copy
    > > Set wb = ActiveWorkbook
    > >
    > > With wb
    > > .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
    > > ".xls"
    > > .SendMail Addr(N), _
    > > "Audit Summary Report"
    > > .Close False
    > > End With
    > > Next N
    > > Application.ScreenUpdating = True
    > > End Sub
    > >
    > >
    > >

    >
    >
    >


  4. #4
    Ron de Bruin
    Guest

    Re: Email question Please.

    Hi Steve

    The problem is that you use Indirect in your formulas to another worksheet in the workbook

    I have send you a workaround

    --
    Regards Ron de Bruin
    http://www.rondebruin.nl


    "Steved" <Steved@discussions.microsoft.com> wrote in message news:9AE6D6D5-5174-4FD8-88EB-1DD383652F6F@microsoft.com...
    > Thanks Ron sorry about the delay yesterday I could read but the reply part
    > was not functioning.
    >
    > "Ron de Bruin" wrote:
    >
    >> Hi Steve
    >>
    >> You must copy this
    >> Cells.Copy
    >> Cells.PasteSpecial xlPasteValues
    >> Cells(1).Select
    >> Application.CutCopyMode = False
    >> after
    >> Sheets(Shname(N)).Copy
    >>
    >> If this is not working for you send me your test workbook private and i look at
    >> it on sunday evening.
    >>
    >>
    >> --
    >> Regards Ron de Bruin
    >> http://www.rondebruin.nl
    >>
    >>
    >> "Steved" <Steved@discussions.microsoft.com> wrote in message news:7C22FB6B-9BAB-437D-A919-46A152749E82@microsoft.com...
    >> > Hello from Steved
    >> >
    >> > Please I would to know where to put the below code into the Sub Mail_test()
    >> > code which is also below.
    >> >
    >> > The reason is the worksheet which I call summary has 8 other worksheets
    >> > which read formulas from the summary sheet. I want to please have the other
    >> > sheets
    >> > paste special value. as I tried but not finding any success as the other
    >> > sheets that are emailed still have the formulas hence when opened at the
    >> > destination they see REF!. Thanks for taking time on my important issue.
    >> >
    >> >
    >> > Worksheets.Select
    >> > Cells.Copy
    >> > Cells.PasteSpecial xlPasteValues
    >> > Cells(1).Select
    >> > Worksheets(1).Select
    >> > Application.CutCopyMode = False
    >> >
    >> >
    >> > Sub Mail_test()
    >> > Dim wb As Workbook
    >> > Dim strdate As String
    >> > Dim Shname As Variant
    >> > Dim Addr As Variant
    >> > Dim N As Integer
    >> >
    >> > strdate = Format(Now, "dd-mm-yy")
    >> > Shname = Array("Summary", "City Depot (1)", "Roskill Depot (2)",
    >> > "Papakura Depot (3)", "Wiri Depot (4)", "Shore Depot (5)", "Orewa Depot (6)",
    >> > "Swanson Depot (7)", "Panmure Depot (8)")
    >> > Addr = Array("sjdee@clear.net.nz", "sjdee@clear.net.nz",
    >> > "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    >> > "sjdee@clear.net.nz", "sjdee@clear.net.nz", "sjdee@clear.net.nz",
    >> > "sjdee@clear.net.nz")
    >> >
    >> > Application.ScreenUpdating = False
    >> >
    >> > For N = LBound(Shname) To UBound(Shname)
    >> > Sheets(Shname(N)).Copy
    >> > Set wb = ActiveWorkbook
    >> >
    >> > With wb
    >> > .SaveAs "C:/Audit Reports/" & Format(Now, "dd-mm-yy hh-mm-ss") &
    >> > ".xls"
    >> > .SendMail Addr(N), _
    >> > "Audit Summary Report"
    >> > .Close False
    >> > End With
    >> > Next N
    >> > Application.ScreenUpdating = True
    >> > End Sub
    >> >
    >> >
    >> >

    >>
    >>
    >>




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1