×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Outlook file attachment

Outlook file attachment

Outlook file attachment

(OP)
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

RE: Outlook file attachment

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.

RE: Outlook file attachment

(OP)
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
End With
End Sub

RE: Outlook file attachment

>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..

RE: Outlook file attachment

@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?

RE: Outlook file attachment

It would be good to see:

CODE

  Debug.Print "File to Attach: " & folder & file
  .Attachments.Add folder & file
  .Send 

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Outlook file attachment

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

RE: Outlook file attachment

Try this code taken from (https://www.rondebruin.nl/win/s1/outlook/amail1.ht...) and slightly modified. Where does that get you?

CODE --> VBA

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 

RE: Outlook file attachment

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

RE: Outlook file attachment

"but it wont send"

Did you have:
.Display

or
.Send

at the end of your code?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Outlook file attachment

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

RE: Outlook file attachment

What's in your sent items folder? Anything?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close