Results 1 to 10 of 10

Thread: [RESOLVED] Save worksheet and send as attachment

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Resolved [RESOLVED] Save worksheet and send as attachment

    Hi,

    I wish to create a new macro that basically performs the below functions.

    1. Save a worksheet (Master) to a particular drive with all the
    same formats as .xlsx

    File path. W:\.Team Documents\Freehold Team\Freeholder Managers\Reporting\Reports\

    File to be saved as (Current month) MMM PM Template.

    2. Attach file - MMM PM Template to email and send to the defined groups on control tab. Again Formats must be captured within the attachment.

    I'm using outlook version 2002
    Attached Images Attached Images    

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save worksheet and send as attachment

    untested
    Code:
    Dim w As Worksheet, b As Workbook, ol As Object, msg As Object
    Dim mypath As String, myfile As String, scc As String, sto As String
    mypath = "W:\.Team Documents\Freehold Team\Freeholder Managers\Reporting\Reports\"
    With Sheets("Control")
        sto = Join(WorksheetFunction.Transpose(Range("h6:h11")), ";")
        scc = Join(WorksheetFunction.Transpose(Range("i6:i11")), ";")
    End With
    Set w = Sheets("master")
    Set b = Workbooks.Add
    w.Copy b.Sheets(1)
    myfile = mypath & Format(Date, "MMM") & "PM Template.xlsx"
    b.SaveAs myfile
    Set ol = CreateObject("outlook.application")
    Set msg = ol.CreateItem(0)
    With msg
        .To = sto
        .CC = scc
        .Subject = "Not sopecified"
        .BodyFormat = olFormatHTML
        .Body = "some text"
        .Attachments.Add myfile
        .Send
    End With
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save worksheet and send as attachment

    ok that's worked brilliantly. Just a few modifications i need to make. Simple ones as well.

    1. The file that I have saved down. Remains open. How do i close (MMM PM Template) after its saved down? WB.close or b.close and where do I add this line?

    2. On the file that I'm saving. I still see Sheet 1. Need this removed and only see the 'Master' tab

    3. I've tried adding the below line for time stamp. but keep getting the below error message.



    End with

    Range("TimeStamp2"). Value = Format(Now, "dd/mm/yy HH:mm:ss")


    Run-time error '1004':

    Method 'Range' of object '_Global' failed

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save worksheet and send as attachment

    b.close and where do I add this line? after b.saveas


    I still see Sheet 1. Need this removed and only see the 'Master' tab
    before saveas
    Code:
    Application.DisplayAlerts = False
    For sh = b.Sheets.Count To 2 Step -1
        sh.Delete
    Next
    Application.DisplayAlerts = True
    but keep getting the below error message.
    maybe trying to find the named range in the wrong workbook or sheet, fully qualify the range
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save worksheet and send as attachment

    Breaks at....

    sh.delete.

    Object required.

    Do I need to add sh As Object at the start???


    I've tried to qualify the range using the below code but its not working.

    b.worksheets("control").activate
    Range("TimeStamp2"). Value = Format(Now, "dd/mm/yy HH:mm:ss")

    Do I need to add DIM somewhere?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save worksheet and send as attachment

    sh.delete.
    my error
    Code:
    b.sheets(sh).delete
    Range("TimeStamp2")
    range timestamp2 is probably a defined name in the original workbook, not in the new workbook, specify the cell address instead
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save worksheet and send as attachment

    B.sheets(sh).delete. Worked. Thank you.


    Yes timestamp2 has been defined in the original workbook which is why the above doesn't work. What's the code so it picks up the defined name in the original workbook? or do you think referencing the cell address is better?

  8. #8
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Save worksheet and send as attachment

    or do you think referencing the cell address is better?
    as you are creating a new workbook and only writing it once i would think it is a lot simpler to just use the cell address
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  9. #9

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save worksheet and send as attachment

    Ok. What's the best way to reference the cell address?

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    May 2019
    Posts
    128

    Re: Save worksheet and send as attachment

    Ignore my previous message. I've solved the problem. Cheers.

Posting Permissions

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



Click Here to Expand Forum to Full Width