Results 1 to 9 of 9
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Extracting sheet and attaching file

    I have tried to write code to extract a sheet called "NV Dept" and to email this. I cannot get the sheet extracted to be attached to an email

    Your assistance in this regard will be most appreciated

    Sub Email_NV_Budget()
    Application.DisplayAlerts = False

    Dim lCount As Long
    Dim vFilenames As Variant
    Dim sPath As String
    Dim lFilecount As Long
    Dim sFullName As String
    sPath = "C:\Budgets 2012\"
    ChDrive sPath
    ChDir sPath
    vFilenames = Application.GetOpenFilename("Microsoft Excel files (.xls),.xls", , "Please select the file(s) to open", , True)
    If TypeName(vFilenames) = "Boolean" Then Exit Sub
    For lCount = LBound(vFilenames) To UBound(vFilenames)
    Workbooks.Open vFilenames(lCount)
    Sheets(Array("NV Dept")).Copy

    ActiveWorkbook.SaveAs Replace(vFilenames(lCount), "*.xls", ".xls") & ".NV Dept*.xls", FileFormat:=xlExcel8


    vFilenames(lCount) = ActiveWorkbook.FullName
    For Each sht In Sheets("NV Dept")
    Sheets(sht.Name).UsedRange.Copy
    Sheets(sht.Name).Range("a1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Next
    ActiveWorkbook.Close True
    Application.DisplayAlerts = True
    Application.CutCopyMode = True
    Next
    Mailfiles "xp.colins@exprl.co.za", vFilenames
    For lCount = LBound(vFilenames) To UBound(vFilenames)
    Kill vFilenames(lCount)
    Next
    ActiveWorkbook.Close False
    End Sub

    Sub Mailfiles(sMailAddress As String, vFiles As Variant)
    Dim oMailItem As Object
    Dim oOLapp As Object
    Dim lCt As Long

    Set oOLapp = CreateObject("Outlook.application")
    Set oMailItem = oOLapp.CreateItem(0)
    With oMailItem
    .To = sMailAddress
    .Subject = "NV Dept Expenses"
    .body = "Attached please find Average data as at " & Format(Month(Date) - 1 & " " & Year(Date), "mmmm yyyy") & " vs the Prior Year" & vbNewLine & vbNewLine
    .body = .body & A budget column has also been added. Please complete this section and email back to myself " & vbNewLine & vbNewLine
    .body = .body & "Regards" & vbNewLine & vbNewLine
    .body = .body & "Howard"
    For lCt = LBound(vFiles) To UBound(vFiles)
    .attachments.Add CStr(vFiles(lCt))
    Next
    .Display
    Set oOLapp = Nothing
    Set oMailItem = Nothing
    End With


    End Sub

  2. #2
    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
    You don't indicate where your problems are. The things I see:
    1) in the Mailfiles routine you forgot a parentheses:
    .body = .body & "A budget column has also been added. Please complete this section and email back to myself " & vbNewLine & vbNewLine

    With that change your mailfiles routine seems to work as I expect it would. It creates in outlook an email to the recipient with the files attached from vFilenames....

    2) The Email NV budget routine seems to be the issue and I am not sure exactly what you are trying to do.
    Your routine gets a list of files and stores it in vFilenames.
    It works on each of the individual files
    Workbooks.Open vFilenames(lCount)
    Opens a file

    Sheets(Array("NV Dept")).Copy
    Copies the sheet named "NV Dept". [The line could be simplified to Sheets("NV Dept").Copy]

    I am a little confused by your line:
    ActiveWorkbook.SaveAs Replace(vFilenames(lCount), "*.xls", ".xls") & ".NV Dept.xls", FileFormat:=xlExcel8

    In the filename it replaces any of the string "*.xls" with ".xls" and then adds ".NV Dept.xls". But there should be no filenames containing an asterisk so the effect of this is just to add ".NV Dept.xls" to any of the names. [so you would copy the NV Dept sheet from a filenamed "MyFile.xls" and it would be saved as "MyFile.xls.NV Dept.xls"]

    Did you want to just replace ".xls" with ".NV Dept.xls"? Then use the line
    ActiveWorkbook.SaveAs Replace(vFilenames(lCount), ".xls", ".NV Dept.xls"), FileFormat:=xlExcel8

    I would prefer, however, using
    ActiveWorkbook.SaveAs Replace(vFilenames(lCount), ".xls", "-NV Dept.xls"), FileFormat:=xlExcel8
    Since even though multiple periods are now allowed, I prefer them to just be an indication of the extension


    vFilenames(lCount) = ActiveWorkbook.FullName
    substitutes the newly created name for the old name in the list of filenames

    I have no idea of what you want to do with the line:
    For Each sht In Sheets("NV Dept")
    You have not dimensioned the variable sht so I am not sure what you want it to be. It appears you want to do something with sheets (and probably WORKsheets as opposed to any type of sheet) but the worksheet sheets ("NV Dept") does not have any sheets in it, it is a worksheet....

    You could
    Dim wks as Worksheet and then use:
    For each wks in Activeworkbook.Worksheets

    to loop through each worksheet in the activeworkbook...

    Also the segment of code:
    Sheets(sht.Name).

    Can be simplified to just:
    sht.

    Steve
    Last edited by sdckapr; 2011-12-28 at 18:47.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Extracting Sheet & emailing Data

    Hi Steve

    Thanks for the help and sorry for any confusion. I get help on a forum a long time ago & tried to adapt the code, but battled to get it to work

    I have attached a workbook sample. What I would like to do is extract/copy sheet "NV Dept" and attach this to an email.

    It would be appreciated if you would provide me with the code and incorporate the following in the body

    Subject : NV Budgets & current year + 1 i.e NV Budgets 2012

    Body: Attached pleae average data for two months and well as a budget column. Please input your budget data and return to me. Regards Howard


    Your assistance in the regard will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  4. #4
    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
    That is different than the original code was doing. The original code asked about opening files, and working with several. To just do one sheet, the code at: http://www.rondebruin.nl/mail/folder2/mail2.htm can be used.

    Modify as desired, replacing the Subject line with:
    .Subject = "NV Budgets " & year(date)+1

    Steve
    PS Instead of
    ActiveSheet.Copy

    You could be explicit and use:
    Worksheets("NV Dept").Copy

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Extracting Sheet & Attaching File

    Hi Steve

    Thanks for the link it is very useful. I just need one small change. I want the code amended so that The email is attached, but not automatically emailed. I need to check the contents before submitting i.e I must be able to click on the send button before sending

    Your assitance in this regard is most appreciated

    Regards

    Howard

  6. #6
    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
    Change the line of code listed as:
    .Send 'or use .Display

    to just:
    .Display

    or even for future information / adaptation:
    .Display 'or use .Send

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. This is much appreciated.

    In the body of my emai I want to have spaces between the lines

    See my example & code use

    Hi Andrew
    Please complete budget section and return tome
    Regards Peter

    I would like it to appear as

    Hi Andrew

    Please complete budget section and return to me



    Regards Peter







    With OutMail
    .To = andrew.miles@xcplr.com
    .CC = "neil.miren@ecfkr.com"
    .BCC = ""
    .Subject = "NV Budgets " & Year(Date) + 1
    .body = .body & "Hi Andrew"


    .body = .body & "Please complete budget section and return to me"



    .body = .body & "Regards Peter"

    Your assistance will be most appreciated

  8. #8
    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
    I think if you look at the original code you posted, you should be able to figure it out....Steve

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the reply & for all your assistance. I looked at the original code. Its fairly straighforward to have spaces between the lines

    Regards

    Howard

Posting Permissions

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