Outlook file attachment

[jlroeder]

Can someone give me a hand with this. I'm trying to attach a new text file that is generated everyday and send it via an email. I keep getting a Run-time error '9': Subscript out of range when I try to debug it.

Sub Attachtxt()
Dim folder As String, file As String
folder = "X:\test"
With CreateObject("Outlook.Application").CreateItem(0)
.To = "any.mouse@gmail.com"
.Subject = "Test"
file = Split(CreateObject("wscript.shell").exec("cmd /c Dir /b /o-d """ & folder & """*.txt").stdout.readall, vbCrLf)(0)
.Attachments.Add folder & file
.Send
End With
End Sub

 

[DjangMan]

What line generates the error?

Looking at the command line I think you might need a trailing \ in your folder variable. But that won't be what is causing the run-time error - it will affect you later on, however.

Break out the lines of code so you can debug the output of your CreateObject("wscript... line to make sure it is working before you turn it back into a single line.

 

[jlroeder]

The \ did fix that issue so now it runs all the way but now I'm getting a Run-time error '287': Application-defined or object-defined error. I'm watching it go through the debug and it happens as soon as the script runs .Send and goes to End With

Sub AttachE911()
Dim folder As String, file As String
folder = "X:\test\"
With CreateObject("Outlook.Application").CreateItem(0)
.To = "any.mouse@gmail.com"
.Subject = "Test"
file = Split(CreateObject("wscript.shell").exec("cmd /c Dir /b /o-d """ & folder & """*.txt").stdout.readall, vbCrLf)(0)
.Attachments.Add folder & file
.Send
[highlight #FCE94F] End With
[/highlight]End Sub

 

[strongm]

>But that won't be what is causing the run-time error

Yes, it will. Since we are trying to Split the content of the all the text files in the folder (weirdly indirected through stdout), and with an incorrect path wrong there will be NO content to Split, so Split is empty. Hence the described error..

 

[DjangMan]

@strongm
I was suggesting that the CreateObject(...).exec().stdout.readall would still run, just return unexpected results. But, given that it is all within the same line of code, you are correct.

@jlroeder
Can you run your test with no attachments?

 

[Andrzejek]

It would be good to see:

[blue]  Debug.Print "File to Attach: " & folder & file[/blue]
  .Attachments.Add folder & file
  .Send

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[jlroeder]

No luck. I still get the same error when I run it without the attachments.

 

[DjangMan]

Try this code taken from (

https://www.rondebruin.nl/win/s1/outlook/amail1.htm)

and slightly modified. Where does that get you?

Sub Mail_workbook_Outlook_1()
    Dim OutApp As Object
    Dim OutMail As Object

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

    On Error Resume Next
    With OutMail
        .to = "any.mouse@gmail.com"
        .CC = ""
        .BCC = ""
        .Subject = "This is the Subject line"
        .Body = "Hi there"
        .Display 'or use .Send        
    End With
    On Error GoTo 0

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

 

[jlroeder]

Progress. It made a blank email with TO, Subject, and Body filled out but it wont send.

 

[Andrzejek]

but it wont send"

Did you have:[tt]
.Display[/tt]
or[tt]
.Send[/tt]
at the end of your code?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[jlroeder]

I did both .Display and .Send. .Display created a new email text box waiting to be sent and .Send did nothing.

 

[DjangMan]

What's in your sent items folder? Anything?