Error on Macro that Sends email through Outlook

Posted by Ben O. on May 04, 2001 2:49 PM

I've got a macro that sends a simple email message (it does not attach the Excel workbook) using Outlook. I used the macro on a previous spreasheet and it worked fine. Now I've copied the code to my new worksheet and it doesn't work. A keep getting a "User-defined type not defined" compile error. I can't remember where I got the code from, so I can't remember if there are any more steps I have to take. Here's the code. If anyone could help, I'll be grateful:

Private Sub Notify()

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

With objMail
.To = "beno@bsg.com"
.Subject = "Test"
.Body = "This is an automated message from Excel."
.Display
End With
Set objMail = Nothing
Set objOL = Nothing

End Sub

Posted by Ivan Moala on May 04, 2001 4:14 PM

Dim objOL As New Outlook.Application Dim objMail As MailItem Set objOL = New Outlook.Application Set objMail = objOL.CreateItem(olMailItem) .To = "beno@bsg.com" .Subject = "Test" .Body = "This is an automated message from Excel." .Display End With Set objMail = Nothing Set objOL = Nothing

Ben
You need to reference the outlook object library file. In VBA editor Goto Tools / references and
select the applicable library file
msoutl8.obl or msout9.obl depending on which version
of ecel

Ivan

Posted by Andrew Kirby on May 05, 2001 6:26 PM

I have also used a similar method, however I need to insert some data from a specific range of cells within the sheet, is this possible?

Thanks,

Andrew Dim objOL As New Outlook.Application Dim objMail As MailItem Set objOL = New Outlook.Application Set objMail = objOL.CreateItem(olMailItem) .To = "beno@bsg.com" .Subject = "Test" .Body = "This is an automated message from Excel." .Display End With Set objMail = Nothing Set objOL = Nothing

Posted by Andrew Kirby on May 05, 2001 6:26 PM

I have also used a similar method, however I need to insert some data from a specific range of cells within the email body, is this possible?

Thanks,

Posted by Ivan Moala on May 05, 2001 10:15 PM

Try this amend
Sub Notify()

Dim objOL As New Outlook.Application
Dim objMail As MailItem
Set objOL = New Outlook.Application
Set objMail = objOL.CreateItem(olMailItem)

With objMail
.To = Range("A1").Text
.Subject = "Test"
.Body = Range("A2").Text
.Display
End With
objMail.Send
Set objMail = Nothing
Set objOL = Nothing

End Sub

Just change the range to reflect the text to send and the address.

Ivan

Posted by Ben O. on May 07, 2001 7:39 AM

Thanks Ivan...

I never would have thought of that! I don't rememeber doing that on my other spreadsheet with the email macro. But that did the trick. Thanks again,

-Ben