I am trying to send an email via VBA from with Excel.

But many people who will use this spreadsheet have various versions of
Outlook. So I am trying to use late binding becuase settting a reference to
outlook does not seem to work.

here is the basic code for sending the email

Private Sub SendPers(savetype As Integer)
Dim myOL As Outlook.Application
Dim newmessage As MailItem


Set myOL = CreateObject("Outlook.Application")
Set newmessage = myOL.CreateItem(olMailItem)

With newmessage
If savetype = 1 Then
.Body = "A new skills survey has been entered for " & myfname
Else
.Body = "A change has been made to the skills survey for " & myfname
End If
.Subject = "Skills Inventory " & myfname
.To = "Jones, Mary;Jones, Chris"
.send
End With

End Sub

The problem is with the "newmessage" object. I apparently don't have the
syntax or construct right for this because it stops on the "Set newmessage
=...." statement.

Any suggestions?????

Dennis

Den,

Since you are using late binding, all Outlook objects should be
declared As Object. Change

Dim newmessage As MailItem
' to
Dim newmessage As Object


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Den" wrote in message
...
I am trying to send an email via VBA from with Excel.

But many people who will use this spreadsheet have various
versions of
Outlook. So I am trying to use late binding becuase settting a
reference to
outlook does not seem to work.

here is the basic code for sending the email

Private Sub SendPers(savetype As Integer)
Dim myOL As Outlook.Application
Dim newmessage As MailItem


Set myOL = CreateObject("Outlook.Application")
Set newmessage = myOL.CreateItem(olMailItem)

With newmessage
If savetype = 1 Then
.Body = "A new skills survey has been entered for " & myfname
Else
.Body = "A change has been made to the skills survey for "
& myfname
End If
.Subject = "Skills Inventory " & myfname
.To = "Jones, Mary;Jones, Chris"
.send
End With

End Sub

The problem is with the "newmessage" object. I apparently
don't have the
syntax or construct right for this because it stops on the "Set
newmessage
=...." statement.

Any suggestions?????

Dennis

Hi Dennis,

A few other changes are necessary to get this to work. Here is the revised
code:

Private Sub SendPers(savetype As Integer)
Dim myOL As Object
Dim newmessage As Object
Dim myFName As String

Set myOL = CreateObject("Outlook.Application")
Set newmessage = myOL.CreateItem(0)

With newmessage
If savetype = 1 Then
.Body = "A new skills survey has been entered for " & myFName
Else
.Body = "A change has been made to the skills survey for " & myFName
End If
.Subject = "Skills Inventory " & myFName
.To = "Jones, Mary;Jones, Chris"
.send
End With
End Sub


Notice that all OL objects are declared As Object. Plus, olMailItem is a
constant in the OL library, so it won't work with Late Binding. Replace it
with its value, which is 0.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Den wrote:
I am trying to send an email via VBA from with Excel.

But many people who will use this spreadsheet have various versions of
Outlook. So I am trying to use late binding becuase settting a
reference to outlook does not seem to work.

here is the basic code for sending the email

Private Sub SendPers(savetype As Integer)
Dim myOL As Outlook.Application
Dim newmessage As MailItem


Set myOL = CreateObject("Outlook.Application")
Set newmessage = myOL.CreateItem(olMailItem)

With newmessage
If savetype = 1 Then
.Body = "A new skills survey has been entered for " & myfname
Else
.Body = "A change has been made to the skills survey for " &
myfname End If
.Subject = "Skills Inventory " & myfname
.To = "Jones, Mary;Jones, Chris"
.send
End With

End Sub

The problem is with the "newmessage" object. I apparently don't
have the syntax or construct right for this because it stops on the
"Set newmessage =...." statement.

Any suggestions?????

Dennis