none
Executing sp_send_dbmail from VBA fails RRS feed

  • Question

  • If someone could help me with this I would greatly appreciate it..

    I recently upgraded from XP to Windows 7, and from Access 2002 to Access 2007, and now I'm having issues executing sp_send_dbmail from VBA. At first I get this error message, then I go to debug, click run, and the code finishes executing no problem. Does anyone have any thoughts?

    'export query to excel
    '
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, queryname, filepath
    '
    'execute command to send exported file
    Dim ADOCmd As ADODB.Command
    Set ADOCmd = New ADODB.Command
    '    
    With ADOCmd
       .CommandText = "exec msdb.dbo.sp_send_dbmail @profile_name = 'MikeD', @recipients = 'MattM', @subject = 'Mysubject', @body_format = 'text', @file_attachments = '" & filepath & "'"
       .ActiveConnection = "DSN=Payroll_IS"   
       .CommandTimeout = 0
       .Execute
    End With
    Thursday, August 16, 2012 8:02 PM

Answers

  • NET HELPMSG 32 (32 is the error number in your error message) tells us:

    The process cannot access the file because it is being used by another process.

    So that is your problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by pr_mike Thursday, August 16, 2012 10:40 PM
    Thursday, August 16, 2012 10:02 PM

All replies

  • NET HELPMSG 32 (32 is the error number in your error message) tells us:

    The process cannot access the file because it is being used by another process.

    So that is your problem.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by pr_mike Thursday, August 16, 2012 10:40 PM
    Thursday, August 16, 2012 10:02 PM
  • It looks like Access just needed an extra second to export the query. I used this function to see if the file is locked before calling sp_send_dbmail, and now it works perfect. Thank You!

    Function FileLocked(strFileName As String) As Boolean
       On Error Resume Next
       ' If the file is already opened by another process,
       ' and the specified type of access is not allowed,
       ' the Open operation fails and an error occurs.
       Open strFileName For Binary Access Read Write Lock Read Write As #1
       Close #1
       ' If an error occurs, the document is currently open.
       If Err.Number <> 0 Then
          ' Display the error number and description.
          MsgBox "Error #" & str(Err.Number) & " - " & Err.Description
          FileLocked = True
          Err.Clear
       End If
    End Function


    Thursday, August 16, 2012 10:52 PM