I'm writing a macro that sends email attachments of Excel files. This has to be run several times a day (3 or 4 times, maybe even more). It runs fine a first time but subsequent times return errors.

The way the macro works is:

1. Create a new workbook
2. Copy a range to the new workbook
3. Save the workbook to the users' desktop - this is where the issue ocurrs
4. Attach said file to an Outlook new message.
5. Kill the newly created workbook.

These users' computers are backed up with OneDrive and even their desktops are. What I think is happening is that, even though the file is deleted, there seems to be some ghost (index, perhaps?) of that file. So, when the macro tries to save the file with the same name, it can't save it because it just won't replace it.

Dim OutApp As Object, OutMail As Object
Dim newwb As Workbook
Dim newsh As Worksheet
Dim path As String
Dim again As Boolean
Dim filecounter As Integer

'create new wb to send out
Set newwb = Workbooks.Add
Set newsh = newwb.Sheets(1)

'do some sorting and formatting of the data
Call PrepWorbook(newsh, rg) 

'Get the path to user desktop
path = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"
path = path & assignee & " Assignments " & Format(Date, "mmddyy")

'check if the file already exists, if it does, then rename it
'by adding a number to the end of the file name
again = True
filecounter = 1
Do While again
    If Len(Dir$(path & ".xlsx")) > 0 Then
        
        filecounter = filecounter   1
        path = path & " " & filecounter
        
    Else
        again = False
    End If
Loop

newwb.SaveAs path, 51

Set OutApp = CreateObject("Outlook.application")
Set OutMail = OutApp.CreateItem(0)

With OutMail
    .to = [distribution list]
    .cc = [CC distribution list]
    .Attachments.Add newwb.FullName
    .Subject = [Subject line]
    .htmlbody = [body]
    .send
    
    Set OutMail = Nothing
    Set OutApp = Nothing

End With

I've tried checking if the file exists, and if it does, then re-name the file by adding a number to the end... but, the macro doesn't see the file (because it literally isn't there) but when it tries to save then it errors out with 1004 at the SaveAs method.

Additionally, I am using displayalerts = FALSE (even though not displayed here, but at the beginning of the code, which is larger).

What has worked - although not ideal, is to wait. If I re-run it after 30mins then it works (haven't tested shorter times, but definitely a wait of 5min isn't enough).

CodePudding user response：

Saving to %TEMP% folder instead solved the problem.

CodePudding user response：

It runs fine a first time but subsequent times return errors.

It seems the file saved to the folder is uploaded to OneDrive and locked until it is done. So, I'd suggest choosing any other folder on the disk not connected to the cloud storage, so you could easily save and remove files where necessary.

The Attachments.Add method takes a string which represents the source of the attachment - it can be a file (represented by the full file system path with a file name) or an Outlook item that constitutes the attachment.