Required to add attachment to .Send

Driving me NUTS :banghead::banghead::banghead::banghead::banghead::banghead:


In the simple little macro below, if I do NOT include an attachment, I get an error 287 on .Send

With an attached file, the message is generated and sent as expected

Is there any way that I would not have to .Add an attachment

Win7 64bit, MS Office 2010

Thanks

Code:

---

Option Explicit
Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
   
    Dim sFile As String, sBody As String, sRecipiant
   
    sBody = "This is an email message"
    sFile = "C:\Users\Me\Scripts\Logs\bu2000-2015-01-20.log"
    sRecipiant = "me@here.net"
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = sRecipiant
        .CC = vbNullString
        .BCC = vbNullString
        .Subject = "email test -- " & Format(Now, "General Date")
        .Body = sBody
        'without attachment I get an error 287 on the .Send
        .Attachments.Add sFile
        .ReadReceiptRequested = False
        'err 287 - application-defined or object-defined error
        .Send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
       
End Sub

---

With OutMail
If Len(sFile) >0 Then .Attachments.Add sFile
End With

Sorry - I wasn't clear

If I comment out the line that adds an attachment I get the error.

Code:

---

'    .Attachments.Add sFile

---

So unless I have something attached to the mail item, I get the error. So the intuitive thought would be a simple little macro like this would send a email (text only)

Code:

---

Option Explicit
Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
   
    Dim sBody As String, sRecipiant as string
   
    sBody = "This is an email message"
    sRecipiant = "me@here.net"
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = sRecipiant
        .CC = vbNullString
        .BCC = vbNullString
        .Subject = "email test -- " & Format(Now, "General Date")
        .Body = sBody
        .ReadReceiptRequested = False
        .Send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
       
End Sub

---

Quote:

---

Code:

---

Option Explicit
Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
   
    Dim sBody As String, sRecipiant as string
   
    sBody = "This is an email message"
    sRecipiant = "me@here.net"
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = sRecipiant
        .CC = vbNullString
        .BCC = vbNullString
        .Subject = "email test -- " & Format(Now, "General Date")
        .Body = sBody
        .ReadReceiptRequested = False
        .Send
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
       
End Sub

---

---

Are you saying that raises a 287 Error?

Yes, if I comment out the .Attachments.Add line, the .Send generates an error

When I add any attachment, email functions normally (or at least the mail and the attachment get sent

OK, I think I have it ... finally

I don't understand the reason to add the .GetNamespace and a few other lines, but at least I am not forced to always include a dummy attachment. Found the hint at another forum

http://www.experts-exchange.com/Soft....html#view-all

Code:

---


Option Explicit

 
Sub SendEmail3()
    Const olFolderInbox As Long = 6
   
    Dim oApp As Object, oMail As Object, oFld As Object
   
    Dim sFile As String, sBody As String, sRecipiant
   
    sBody = "This is an email message"
    sRecipiant = "abc@def.net"
    Set oApp = CreateObject("Outlook.Application")
    Set oFld = oApp.GetNamespace("MAPI").GetDefaultFolder(olFolderInbox)
    Set oMail = oFld.Items.Add
   
    sFile = vbNullString
    With oMail
        .To = sRecipiant
        .CC = vbNullString
        .Subject = "email test 3 -- " & Format(Now, "General Date")
        .Body = sBody
        If Len(sFile) > 0 Then .Attachments.Add sFile
        .send
    End With

    sFile = "C:\Users\ABC\Scripts\Logs\bu2000-2015-01-20.log"
    Set oMail = oFld.Items.Add
    With oMail
        .To = sRecipiant
        .CC = vbNullString
        .Subject = "email test 3 with attachment-- " & Format(Now, "General Date")
        .Body = sBody
        If Len(sFile) > 0 Then .Attachments.Add sFile
        .send
    End With

    Set oMail = Nothing
    Set oFld = Nothing
    Set oApp = Nothing
       
End Sub

---

(and I thought PowerPoint was confusing - doesn't even hold a candle to Outlook)

Is it possible that this is a Microsoft Update caused Error?

Code:

---

Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object
   
    Dim sBody As String, sRecipiant as string
   
    sBody = "This is an email message"
    sRecipiant = "me@here.net"
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = sRecipiant
        .CC = vbNullString
        .BCC = vbNullString
        .Subject = "email test -- " & Format(Now, "General Date")
        .Body = sBody
        .ReadReceiptRequested = False
        '.Send
'with and without attachments.Add
Debug.PRint(.Attachments.Count)
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
       
End Sub

---

Outlook 2002 help says

Quote:

---

To ensure consistent results, always save an item before adding or removing objects in the Attachments collection of the item.

The following Visual Basic for Applications example creates a new mail message, attaches a Q496.xls as an attachment (not a link), and gives the attachment a descriptive caption.

Code:

---

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
myItem.Save
Set myAttachments = myItem.Attachments
myAttachments.Add "C:\My Documents\Q496.xls", _
    olByValue, 1, "4th Quarter 1996 Results Chart"

---

---

Quote:

---

Is it possible that this is a Microsoft Update caused Error?

---

More likely a Paul caused error by not understanding the Outlook object model.

The Outlook object model is very confusing to me and I tend to just 'cookbook' the few tasks where I need to interface with Outlook

If I can interject a variation on a theme, the following will create a message, with or without a named attachment (if the attachment file exists) and it uses the Outlook editor to edit the message body, in this case for no better reason than it retains the default signature, though you can use it to format the range as required.

Code:

---

Option Explicit
Sub SendMessageWithAttachment()
    SendEmail "c:\path\example.txt" 'the attachment is optional
End Sub

Sub SendEmail(Optional sFile As String)
Dim OutApp As Object
Dim OutMail As Object
Dim olInsp As Object
Dim wdDoc As Object
Dim oRng As Object
Dim sBody As String
Dim sRecipient As String
    sBody = "This is an email message"
    sRecipient = "me@here.net"
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
        .To = sRecipient
        .Subject = "email test -- " & Format(Now, "General Date")
        .BodyFormat = 2
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range(0, 0)
        oRng.Text = sBody
        If FileExists(sFile) Then
            .Attachments.Add sFile
        End If
        .ReadReceiptRequested = False
        .Display 'This line is required
        '.sEnd 'This line can be restored after testing
    End With
    Set OutMail = Nothing
    Set OutApp = Nothing
lbl_Exit:
    Exit Sub
End Sub

Private Function FileExists(filespec) As Boolean
Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(filespec) Then
        FileExists = True
    Else
        FileExists = False
    End If
lbl_Exit:
    Exit Function
End Function

---

Graham -- thanks,

In the particular case I was trying to include the email sending with a non-interactive task so I wanted to to try it w/o the .Display

However, I get an Object not set on the Set oRng = line because wdDoc is Nothing

1. Are you running the macro from Outlook itself? Though the same code works here from Excel and Word also.
2. Which Office version? Your header suggests 2010 64 bit. though you also mention Office 2002. I don't think I included anything incompatible with the 64 bit version.
3. .Display is essential to be able to view the message. If you add .Send the message only briefly flashes on screen. I left that commented out so that you could check the message. However, with this particular macro, the .Display option is not required. The message will still be produced.

1. Running under Excel 2010 (32 bit MS Office) using 64 bit Win7
2. I think SamT mentioned 2002
3. I intend to port the code to a VBS script to send completion email out after nightly backup so it would be going direct to the .Send w/o the .Display.

However, I still have wdDoc = Nothing using VBA in Excel

Thoughts?

1. I just copied and pasted the code into Excel from my earlier message into a new module in Personal.xlsb and it worked as intended (see below).
2. Yes - you're right. Sorry about that.
3. You can use Send without Display is you are simply writing a text variable to a range as in this example. Display here is merely used to avoid sending unwanted test messages.
wdDoc and oRng are both declared as objects in the code before you set them to their values, so I am not sure what is going on there. Sorry.

Attachment 12745

Quote:

---

so I am not sure what is going on there. Sorry.

---

Not a problem at all -- it's cold and snowing a little here so it'll give me something to do besides shoveling :thumb

this is very nice post