Send e-mail from Excel macro problem

[Nico]

I'm receiving the following error:

Compile error: user-defined type not defined

It's not recognizing this:

Dim objOutlk As New Outlook.Application 'Outlook

I've compared it to others online, and it looks the same, but I must be
missing something?

The code is below. Any help would be much appreicated! Thanks!

Sub SendEmails()

Application.ScreenUpdating = False

Dim Row, Col As Integer
Dim ConfDate, UName, UNumber As String

Row = 2
ConfDate = Application.InputBox("Enter the date attestation should be
received: eg (Friday August 18, 2006")
UName = Application.InputBox("Enter your name as it will appear at the
bottom of the email")
UNumber = Application.InputBox("Enter your phone number as it will appear at
the bottom of the email")
Workbooks.Open Filename:="[server path]\Business Leaders.xls"

Range("A2").Activate
Do
If Cells(Row, 1).Value = "Investigate" Then
MsgBox ("One file found where investigation is required- Name
Investigate.xls")

Else
MsgBox "Click to Send Email"
Dim objOutlk As New Outlook.Application 'Outlook
Dim objMail As MailItem 'Email item
Dim strMsg
Const olMailItem = 0

'Create a new message
Set objOutlk = New Outlook.Application
Set objMail = objOutlk.CreateItem(olMailItem)
objMail.To = Cells(Row, 1).Text
objMail.cc = Cells(Row, 2).Text
objMail.Subject = "User Verification " & " - " & Cells(Row, 1).Text & " " &
Date$
'Add the body
strMsg = Null

strMsg = "Hello " & Cells(Row, 2).Text & vbCrLf & vbCrLf

[etc...]

 

[Jim Thomlinson]

You need to create a reference to the Outlook library. In the VBE select
Tools -> References -> Microsoft Outlook. Note that you are performing early
binding when you do this whcih means that you can not use this on other
systems that do not have the save version of Outlook. You would need late
binding to do that.

Also take a look at this link on declaring variables. Many of your variables
are declared improperly.

http://www.cpearson.com/excel/variables.htm

 

[Nico]

Thanks Jim, I knew it had to be something straightforward I was missing.

You need to create a reference to the Outlook library. In the VBE select
Tools -> References -> Microsoft Outlook. Note that you are performing early
binding when you do this whcih means that you can not use this on other
systems that do not have the save version of Outlook. You would need late
binding to do that.

Also take a look at this link on declaring variables. Many of your variables
are declared improperly.

http://www.cpearson.com/excel/variables.htm
--
HTH...

Jim Thomlinson

I'm receiving the following error:

Compile error: user-defined type not defined

It's not recognizing this:

Dim objOutlk As New Outlook.Application 'Outlook

I've compared it to others online, and it looks the same, but I must be
missing something?

The code is below. Any help would be much appreicated! Thanks!

Sub SendEmails()

Application.ScreenUpdating = False

Dim Row, Col As Integer
Dim ConfDate, UName, UNumber As String

Row = 2
ConfDate = Application.InputBox("Enter the date attestation should be
received: eg (Friday August 18, 2006")
UName = Application.InputBox("Enter your name as it will appear at the
bottom of the email")
UNumber = Application.InputBox("Enter your phone number as it will appear at
the bottom of the email")
Workbooks.Open Filename:="[server path]\Business Leaders.xls"

Range("A2").Activate
Do
If Cells(Row, 1).Value = "Investigate" Then
MsgBox ("One file found where investigation is required- Name
Investigate.xls")

Else
MsgBox "Click to Send Email"
Dim objOutlk As New Outlook.Application 'Outlook
Dim objMail As MailItem 'Email item
Dim strMsg
Const olMailItem = 0

'Create a new message
Set objOutlk = New Outlook.Application
Set objMail = objOutlk.CreateItem(olMailItem)
objMail.To = Cells(Row, 1).Text
objMail.cc = Cells(Row, 2).Text
objMail.Subject = "User Verification " & " - " & Cells(Row, 1).Text & " " &
Date$
'Add the body
strMsg = Null

strMsg = "Hello " & Cells(Row, 2).Text & vbCrLf & vbCrLf

[etc...]