none
File in use error when cdo.message with attatchment in excel RRS feed

  • Question

  • I have an excel file where a macro will send a copy of some of the data in the worksheet as an attachment.  This is working when calling Outlook.  the problem has been converting it to CDO as the computers where this will run are shop-floor systems and don't have outlook installed or configured.  I have it working with CDO the .addattachment line is commented out... but that's the part I need to work.  If I remove the comment, it fails at .addattachment saying the file is in use.  I checked and the file is locked by the excel.exe process.  I need to figure out how to remove the excel file lock.

    Please look through this code give me any suggestions you can. 

    thanks!!

    Sub Mail_workbook_Outlook_2()
    
        ActiveSheet.Unprotect
        Range("A18:AB44").Select
        Selection.Copy
        Range("A18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("H43:S44").Select
    
       Dim CDO_Mail As Object
        Dim CDO_Config As Object
        Dim SMTP_Config As Variant
        Dim LWorkbook As Workbook
        Dim LFileName As String
        Dim AddAttachment As String
    
       'Turn off screen updating
       Application.ScreenUpdating = False
       
       'Copy the active worksheet and save to a temporary workbook
       ActiveSheet.Copy    'this is where the code is creating the new book
       Set LWorkbook = ActiveWorkbook
       
       'Create a temporary file in your current directory that uses the name
       ' of the sheet as the filename
       LFileName = LWorkbook.Worksheets(1).Name
       On Error Resume Next
       'Delete the file if it already exists
       Kill LFileName
       On Error GoTo 0
       'Save temporary file
       LWorkbook.SaveAs Filename:=LFileName   ' this is where it is actually SAVED as the
       ' new worksheet tag-form.xlsx
        ' need some code in here to allow excel to let go of the file so it can
        ' be added to the e-mail message.
        
       
       'Create a CDO object and new mail message
    
        Set CDO_Mail = CreateObject("CDO.Message")
        Set CDO_Config = CreateObject("CDO.Configuration")
         CDO_Config.Load -1
        Set SMTP_Config = CDO_Config.Fields
    
    With SMTP_Config
      .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "server IP"
      .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
      .Update
    End With
    
    With CDO_Mail
        Set .Configuration = CDO_Config
        .To = "user@user.com"
        .From = "user@user.com"
        .Subject = "Manual Inventory Tag Request"
        .TextBody = "Attached is a Manual Inventory Tag Request Form."
        .AddAttachment LWorkbook.FullName
        .Send
    End With
    
       'Delete the temporary file and close temporary Workbook
       LWorkbook.ChangeFileAccess Mode:=xlReadOnly
       Kill LWorkbook.FullName
       LWorkbook.Close SaveChanges:=False
       
       'Turn back on screen updating
       Application.ScreenUpdating = True
       Set oApp = Nothing
       Set CDO_Mail = Nothing
       
       Application.DisplayAlerts = False
     Application.Quit
    
    End Sub
    
    

    Wednesday, November 4, 2015 10:42 PM

All replies

  • Have you tried to clear temp? or delete the owner file? It begins with a tilde ~, followed by a dollar sign $
    Wednesday, November 4, 2015 11:48 PM
  • When it fails the specific error is:  run-time error '-2147024864 (80070020)': The process cannot access the file because it is being used by another process.  (thought I would add that )

    The temp file (~$title.xlsx) is created in my documents as is the main file  title.xlsx   and when it does fail, the newly created workbook is open.  When i the new file, the ~$ files is removed but the title.xlsx doc is still in my documents. 

    If I leave the title.xlsx file in place and try to run it again, I will first get a message that the file exists and do I want to over write it.  I say yes, and then it fails as before.

    thanks

    Thursday, November 5, 2015 2:33 PM
  • Take a look on my article about CDO: http://www.outlook.pl/CDO-Mail-wysylany-inaczej/507/

    When you unexpected close Excel, then you will see temporary file with $. If you do not using this file you can save erase this files from hard drive, but if you're using, that 'll not be possible till you close file. If yoy want to close all excels process look at API function to close this instance.

    anyway you can use commend:

    .SaveAsCopy

    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Thursday, November 5, 2015 10:44 PM
    Answerer
  • Thanks for the suggestions...

    I tried using workbooks (name).Close  but Excel was still holding onto the file so that addAttachment .lworkbook.fullname would still fail.

    So I went a different way with it and attached the file using the %userprofile%.  Its working great now. 

    Sub Mail_workbook_Outlook_2()
    
    Set objShell = CreateObject("Wscript.Shell")
     strUserProfile = objShell.ExpandEnvironmentStrings("%USERPROFILE%")
        
        ' delete existing tag file if it exists
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.fileExists(strUserProfile & "\documents\filename.xlsx") Then
    Set aFile = fso.GetFile(strUserProfile & "\documents\filename.xlsx")
    aFile.Delete
        End If
        
        ActiveSheet.Unprotect
        Range("A18:AB44").Select
        Selection.Copy
        Range("A18").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("H43:S44").Select
    
    
    Dim CDO_Mail As Object
        Dim CDO_Config As Object
        Dim SMTP_Config As Variant
        Dim LWorkbook As Workbook
        Dim LFileName As String
        Dim AddAttachment As String
       
        'Turn off screen updating
       Application.ScreenUpdating = False
       
       'Copy the active worksheet and save to a temporary workbook
      ActiveSheet.Copy
       Set LWorkbook = ActiveWorkbook
       
       'Create a temporary file in your current directory that uses the name
       'of the sheet as the filename
       LFileName = LWorkbook.Worksheets(1).Name
       On Error Resume Next
       'Delete the file if it already exists
        Kill LFileName
       On Error GoTo 0
       'Save temporary file
       LWorkbook.SaveAs Filename:=LFileName
       Workbooks("filename.xlsx").Close
          
       Set CDO_Mail = CreateObject("CDO.Message")
       Set CDO_Config = CreateObject("CDO.Configuration")
     CDO_Config.Load -1
    
    Set SMTP_Config = CDO_Config.Fields
    
    With SMTP_Config
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "server IP"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    .Update
    End With
    
     With CDO_Mail
        Set .Configuration = CDO_Config
        .To = ""
        .From = ""
        .Subject = "subject"
        .TextBody = "Attached is a Manual Inventory Tag Request Form."
        .AddAttachment (strUserProfile & "\documents\filename.xlsx")
        .Send
     End With
       
       'Turn back on screen updating
       Application.ScreenUpdating = True
    
    Set CDO_Mail = Nothing
    Set oApp = Nothing
    Set excelapp = Nothing
    Set ExcelSheet = Nothing
    Set excelworkbook = Nothing
    
       Application.DisplayAlerts = False
     Application.Quit
    
    End Sub
    
    

    Friday, November 6, 2015 3:45 PM