I am using the code below, and it all seems to work fine until it gets to
the '.send' line, and then I get an 'Application or Object defined Error'.
Obviously sending is the key thing here, and I'm pulling my hair out.

I can't see anything wrong with the code unless the numeric equivalent for
olMailItem isn't 0

I really want to get this to work, it's the last peice in a big puzzle for
me.

Thanks

M

===
Sub MailSend() 'SchoolAddress As String)
Dim oLook As Object
Dim oMessage As Object

Dim mTitle As String, mBody As String

' manually set the value of olMail item for late-binding
Const olMailItem As Integer = 0

On Error Resume Next
' Get a reference to the Outlook object model
Set oLook = GetObject(, "Outlook.Application")
If Err.Number = 429 Then 'if it's not already running
Set oLook = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set oMessage = oLook.CreateItem(olMailItem)

' SchoolMail is declared elsewhere
If SchoolMail = "" Then SchoolMail = "
mTitle = ThisWorkbook.Sheets("Message").Range("B2").Formula
mBody = ThisWorkbook.Sheets("Message").Range("B4").Formula

' Fill and send the message
With oMessage
.To = SchoolMail
.Subject = mTitle
.Body = mBody
.Send ' it just doesn't like this line!
End With

Set oMessage = Nothing

Set oLook = Nothing

End Sub

Oh, I forgot to say, it works if Outlook is running!?

M


"Michelle" wrote in message
...
I am using the code below, and it all seems to work fine until it gets to
the '.send' line, and then I get an 'Application or Object defined Error'.
Obviously sending is the key thing here, and I'm pulling my hair out.

I can't see anything wrong with the code unless the numeric equivalent for
olMailItem isn't 0

I really want to get this to work, it's the last peice in a big puzzle for
me.

Thanks

M

===
Sub MailSend() 'SchoolAddress As String)
Dim oLook As Object
Dim oMessage As Object

Dim mTitle As String, mBody As String

' manually set the value of olMail item for late-binding
Const olMailItem As Integer = 0

On Error Resume Next
' Get a reference to the Outlook object model
Set oLook = GetObject(, "Outlook.Application")
If Err.Number = 429 Then 'if it's not already running
Set oLook = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set oMessage = oLook.CreateItem(olMailItem)

' SchoolMail is declared elsewhere
If SchoolMail = "" Then SchoolMail = "
mTitle = ThisWorkbook.Sheets("Message").Range("B2").Formula
mBody = ThisWorkbook.Sheets("Message").Range("B4").Formula

' Fill and send the message
With oMessage
.To = SchoolMail
.Subject = mTitle
.Body = mBody
.Send ' it just doesn't like this line!
End With

Set oMessage = Nothing

Set oLook = Nothing

End Sub

Hi Michelle,

See Ron de Bruin's suggested code at:

Mail a small message
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

Adapting your data to Ron's code, try
something like:

'============
Public Sub MailSend()
Dim OutApp As Object
Dim OutMail As Object
Dim strSchoolMail As String
Dim strSubject As String
Dim strBody As String

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

If strSchoolMail = vbNullString Then
strSchoolMail = "
End If

With ThisWorkbook.Sheets("Message")
strSubject = .Range("B2").Value
strBody = .Range("B4").Value
End With

On Error Resume Next
With OutMail
.To = strSchoolMail
.CC = ""
.BCC = ""
.Subject = strSubject
.Body = strBody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
'<<============



---
Regards.
Norman


"Michelle" wrote in message
...
Oh, I forgot to say, it works if Outlook is running!?

M


"Michelle" wrote in message
...
I am using the code below, and it all seems to work fine until it gets to
the '.send' line, and then I get an 'Application or Object defined Error'.
Obviously sending is the key thing here, and I'm pulling my hair out.

I can't see anything wrong with the code unless the numeric equivalent
for olMailItem isn't 0

I really want to get this to work, it's the last peice in a big puzzle
for me.

Thanks

M

===
Sub MailSend() 'SchoolAddress As String)
Dim oLook As Object
Dim oMessage As Object

Dim mTitle As String, mBody As String

' manually set the value of olMail item for late-binding
Const olMailItem As Integer = 0

On Error Resume Next
' Get a reference to the Outlook object model
Set oLook = GetObject(, "Outlook.Application")
If Err.Number = 429 Then 'if it's not already running
Set oLook = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set oMessage = oLook.CreateItem(olMailItem)

' SchoolMail is declared elsewhere
If SchoolMail = "" Then SchoolMail = "
mTitle = ThisWorkbook.Sheets("Message").Range("B2").Formula
mBody = ThisWorkbook.Sheets("Message").Range("B4").Formula

' Fill and send the message
With oMessage
.To = SchoolMail
.Subject = mTitle
.Body = mBody
.Send ' it just doesn't like this line!
End With

Set oMessage = Nothing

Set oLook = Nothing

End Sub

Thanks Norman - it seems that the only difference is this line
"[ApplicationObjectReference].Session.Logon" - and sure enough when I add
this line it all works fine. Do you know what this line does?

Thanks

M

"Norman Jones" wrote in message
...
Hi Michelle,

See Ron de Bruin's suggested code at:

Mail a small message
http://www.rondebruin.nl/mail/folder3/smallmessage.htm

Adapting your data to Ron's code, try
something like:

'============
Public Sub MailSend()
Dim OutApp As Object
Dim OutMail As Object
Dim strSchoolMail As String
Dim strSubject As String
Dim strBody As String

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

If strSchoolMail = vbNullString Then
strSchoolMail = "
End If

With ThisWorkbook.Sheets("Message")
strSubject = .Range("B2").Value
strBody = .Range("B4").Value
End With

On Error Resume Next
With OutMail
.To = strSchoolMail
.CC = ""
.BCC = ""
.Subject = strSubject
.Body = strBody
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub
'<<============



---
Regards.
Norman


"Michelle" wrote in message
...
Oh, I forgot to say, it works if Outlook is running!?

M


"Michelle" wrote in message
...
I am using the code below, and it all seems to work fine until it gets to
the '.send' line, and then I get an 'Application or Object defined
Error'. Obviously sending is the key thing here, and I'm pulling my hair
out.

I can't see anything wrong with the code unless the numeric equivalent
for olMailItem isn't 0

I really want to get this to work, it's the last peice in a big puzzle
for me.

Thanks

M

===
Sub MailSend() 'SchoolAddress As String)
Dim oLook As Object
Dim oMessage As Object

Dim mTitle As String, mBody As String

' manually set the value of olMail item for late-binding
Const olMailItem As Integer = 0

On Error Resume Next
' Get a reference to the Outlook object model
Set oLook = GetObject(, "Outlook.Application")
If Err.Number = 429 Then 'if it's not already running
Set oLook = CreateObject("Outlook.Application")
End If
On Error GoTo 0

Set oMessage = oLook.CreateItem(olMailItem)

' SchoolMail is declared elsewhere
If SchoolMail = "" Then SchoolMail = "
mTitle = ThisWorkbook.Sheets("Message").Range("B2").Formula
mBody = ThisWorkbook.Sheets("Message").Range("B4").Formula

' Fill and send the message
With oMessage
.To = SchoolMail
.Subject = mTitle
.Body = mBody
.Send ' it just doesn't like this line!
End With

Set oMessage = Nothing

Set oLook = Nothing

End Sub