OutputTo automation (1 Viewer)

[CBrighton]

As part of my work to automate a set of daily processes currently manually run across a few dozen databases I am working on removing any prompts form emailing reports / queries.

The code I am using will be at the end of this post. It consists of 2 functions, SendEmail (which works fine) and ExportEmailFile which is the one I am still working on.

As far as I can see I am passing all the parameters to OutputTo but I get a type mismatch error when I try to use it.

I need to be able to pass it the object & the file name and either pass it the object type or have it automatically capture it so it exports the correct format (currently I pass the type to the function too).

Any thoughts?

[B][I]'This is the procedure that calls the Outlook VBA function...[/I][/B]
[I][B]Public Function SendEmail(strTo As String, _[/B][/I]
[I][B]                   strSubject As String, _[/B][/I]
[I][B]                   strMessageBody As String, _[/B][/I]
[I][B]                   Optional strAttachmentPaths As String, _[/B][/I]
[I][B]                   Optional strCC As String, _[/B][/I]
[I][B]                   Optional strBCC As String) As Boolean[/B][/I]
 
[I][B]   Dim objOutlook As Object[/B][/I]
[I][B]   Dim objNameSpace As Object[/B][/I]
[I][B]   Dim objExplorer As Object[/B][/I]
[I][B]   Dim blnSuccessful As Boolean[/B][/I]
[I][B]   Dim blnNewInstance As Boolean[/B][/I]
 
[I][B]   'Is an instance of Outlook already open that we can bind to?[/B][/I]
[I][B]   On Error Resume Next[/B][/I]
[I][B]   Set objOutlook = GetObject(, "Outlook.Application")[/B][/I]
[I][B]   On Error GoTo 0[/B][/I]
 
[I][B]   If objOutlook Is Nothing Then[/B][/I]
 
[I][B]       'Outlook isn't already running - create a new instance...[/B][/I]
[I][B]       Set objOutlook = CreateObject("Outlook.Application")[/B][/I]
[I][B]       blnNewInstance = True[/B][/I]
[I][B]       'We need to instantiate the Visual Basic environment... (messy)[/B][/I]
[I][B]       Set objNameSpace = objOutlook.GetNamespace("MAPI")[/B][/I]
[I][B]       Set objExplorer = objOutlook.Explorers.Add(objNameSpace.Folders(1), 0)[/B][/I]
[I][B]       objExplorer.CommandBars.FindControl(, 1695).Execute[/B][/I]
 
[I][B]       objExplorer.Close[/B][/I]
 
[I][B]       Set objNameSpace = Nothing[/B][/I]
[I][B]       Set objExplorer = Nothing[/B][/I]
 
[I][B]   End If[/B][/I]
 
[I][B]   blnSuccessful = objOutlook.FnSendMailSafe(strTo, strCC, strBCC, _[/B][/I]
[I][B]                                               strSubject, strMessageBody, _[/B][/I]
[I][B]                                               strAttachmentPaths)[/B][/I]
 
[I][B]   If blnNewInstance = True Then objOutlook.Quit[/B][/I]
[I][B]   Set objOutlook = Nothing[/B][/I]
 
[I][B]   SendEmail = blnSuccessful[/B][/I]
 
[I][B]End Function[/B][/I]
 
[B][I]'This procedure checks for existing output file & deletes, then exports the latest file...[/I][/B]
[I][B]Public Function ExportEmailFile(strOutputFile As String, _[/B][/I]
[I][B]           strObjectName As String, _[/B][/I]
[I][B]           strOutputType As String)[/B][/I]
 
[I][B]   Dim strFileType As String[/B][/I]
 
[I][B]   If Len(Dir(strOutputFile)) Then[/B][/I]
[I][B]       Kill strOutputFile[/B][/I]
[I][B]   End If[/B][/I]
 
[I][B]   If strOutputType = "acOutputReport" Then[/B][/I]
[I][B]       strFileType = "acFormatRTF"[/B][/I]
[I][B]   ElseIf strOutputType = "acOutputQuery" Then[/B][/I]
[I][B]       strFileType = "acFormatXLS"[/B][/I]
[I][B]   End If[/B][/I]
 
[I][B]   DoCmd.OutputTo strOutputType, strObjectName, strFileType, strOutputFile, False[/B][/I]
 
[I][B]End Function[/B][/I]

:edit:

Just realised how poor the explaination was.

I'm sure you can all see from the function but the purpose is to avoid the prompt for overwriting files with OutputTo by checking for the file & deleting it if required before using the OutputTo command.

 

[CBrighton]

I ended up deciding it was because the 1st & 3rd arguements of OutputTo don't accept strings, so I changed the code to this and it works:

Public Function ExportEmailFile(strOutputFile As String, _
           strObjectName As String, _
           strOutputType As String)
 
   Dim strFileType As String
 
   If Len(Dir(strOutputFile)) Then
       Kill strOutputFile
   End If
 
   If strOutputType = "Report" Then
       DoCmd.OutputTo acOutputReport, strObjectName, acFormatRTF, strOutputFile, False
   ElseIf strOutputType = "Query" Then
       DoCmd.OutputTo acOutputQuery, strObjectName, acFormatXLS, strOutputFile, False
   End If
 
End Function