Required to add attachment to .Send

[Paul_Hossler]

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



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

[SamT]

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

[Paul_Hossler]

Sorry - I wasn't clear

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



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




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

[SamT]

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 (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 SubAre you saying that raises a 287 Error?

[Paul_Hossler]

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

[Paul_Hossler]

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/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27485041.html#view-all





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

[SamT]

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


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

[SamT]

Outlook 2002 help says


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.

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"

[Paul_Hossler]

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

[gmayor]

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.


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

[Paul_Hossler]

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

[gmayor]

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.

[Paul_Hossler]

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?

[gmayor]

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.

12745

[Paul_Hossler]

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

[Rosswood]

this is very nice post