Copy workbook to folder created.

u can atleast help me in this.



While mailing a sheet i want to copy that sheet contents to a temporary created workbook with same name as of selected workbook. and store this temp workbook in the folder which i have created.The problem is how will i store the copy of selected workbook to that folder which i have created.





In the following code:



1)list.text --- is name of the sheet.

2)activeworkbook.name--- i tested with f8 dosn't gives name

3)activesheet.copy-- tested with f8 dont give anything

4).Attachments.Add Wb.FullName--givin path of the sheet selected.

5) Kill Wb.FullName--destroys the copy of workbook creted in mydocuments.



I want this copy of workbook to save in the folder created by me.

VB Code:

Application.ScreenUpdating = False
    Set OL = CreateObject("Outlook.Application")
    Set EmailItem = OL.CreateItem(olMailItem)
    fileName = List1.Text '& " - "  & ActiveWorkbook.name
    Debug.Print fileName
   
    For y = 1 To Len(fileName)
        TempChar = Mid(fileName, y, 1)
        Select Case TempChar
        Case Is = "/", "\", "*", "?", """", "<", ">", "|", ":"
        Case Else
            SaveName = SaveName & TempChar
        End Select
    Next y
    ActiveSheet.Copy
    Set Wb = ActiveWorkbook
    Wb.SaveAs SaveName
    Wb.ChangeFileAccess xlReadOnly
    With EmailItem
        .Subject = txtsubject
        .Body = txtmessage
        .To = Txtname
        .Attachments.Add Wb.FullName
        .Send
    End With
    Kill Wb.FullName
    Wb.Close False
     
    Application.ScreenUpdating = True
     
    Set Wb = Nothing
    Set OL = Nothing
    Set EmailItem = Nothing
    
    End If

If there is any unnecess. code plz tell me......

You could try saving the file as normal and then using a one line piece of code that will then copy the file to the temporary location..

VB Code:

FileCopy ActiveWorkbook.FullName, "C:\Temp Folder\" & ActiveWorkbook.Name

Your code can then be changed to the following..

VB Code:

Application.ScreenUpdating = False
  Set OL = CreateObject("Outlook.Application")
  Set EmailItem = OL.CreateItem(olMailItem)
  Set Wb = ActiveWorkbook
  Wb.ChangeFileAccess xlReadOnly
  Wb.Save
  FileCopy Wb.FullName, "C:\Temp Folder\" & Wb.Name
  Wb.ChangeFileAccess xlReadWrite
  Wb.Save
  With EmailItem
    .Subject = txtsubject
    .Body = txtmessage
    .To = Txtname
    .Attachments.Add "C:\Temp Folder\" & Wb.Name
    .Send
  End With
  On Error Resume Next
  Kill "C:\Temp Folder\" & Wb.Name
  Wb.Close False
  Application.ScreenUpdating = True
  Set Wb = Nothing
  Set OL = Nothing
  Set EmailItem = Nothing

the answer is almost in your own code, that is if I'm understanding you correct.

Code:

---

Wb.SaveAs SaveName
if left$(YourPath,1) = "\" then
    Wb.SaveAs YourPath & SaveName
else
    Wb.SaveAs YourPath & "\" & SaveName
end if

---

The var Yourpath is the path to your created directory like "C:\Progam Files\MyApp\ThisMailAdress\SendWorkBooks\"

how can i destroy the folder created?