declaring variables

MrGJEB

New Member
Joined
Jun 13, 2008
Messages
37
Hi,
apologies as i am a self taught user of VBA and never quite got to grips with declaring variables. I am trying to run the code below to send a feedback survey out as an email attachment. When i run it i keep getting a compile error "user-defined type not defined" and the second variable "objOutlook" is highlighted.

help with this would be much appreciated.

Thank you

Sub exemail()

Dim survey As String
Dim objOutlook As Outlook.Application
Dim objMail As Outlook.MailItem, objAttachment As Outlook.Attachment
Dim strRecipient1 As String
Response = MsgBox("An email confirming the completion of the job will now be sent to the client. Would you like to proceed?", vbYesNo + vbQuestion)
If Response = vbYes Then

Range("A" & (ActiveCell.Row)).Select
End If
'\\Declare variables that store path to attachments
survey = "C:\Documents and Settings\grant.blackwell\My Documents"
'\\Declare variables of recipents email address
strRecipient1 = ActiveCell.Offset(0, 11).Value
'\\Create an Outlook email
'See if Outlook is already running. If not open Outlook
On Error Resume Next
Set objOutlook = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
'if this is an error then Outlook cannot be running so open it
Err.Clear
On Error GoTo 0
Set objOutlook = CreateObject("Outlook.Application")

If Err.Number <> 0 Then
Err.Clear
'Outlook is not installed
MsgBox "Unable to attach spreadsheet to Outlook message."
Exit Sub
End If
End If
'Create message with attachment
Set objMail = objOutlook.CreateItem(0) 'olMailItem constant = 0
With objMail
.To = strRecipient1
.Subject = "Creative Services Completion of Job"
.display 'Display Outlook message
.Body = "Dear colleague," & vbCrLf & vbCrLf & "This email is to confirm that " & ActiveCell.Offset(0, 1).Value & " (Job Number: " & ActiveCell.Value & ") has now been completed." & vbCrLf & vbCrLf & "We hope the work has been delivered to you satisfaction." & vbCrLf & vbCrLf & "We like to monitor our performance - and your feedback is vital. Could you please complete the attached survey." & vbCrLf & vbCrLf & "With kind regards" & vbCrLf & vbCrLf & "Maureen Jeffers" & vbCrLf & "Production and Client Services Manager" & vbCrLf & "Publishing and Creative Services" & vbCrLf & "Telephone Number: 020 8498 7347"
.Attachments.Add survey, , 9999
.send
End With
Set objOutlook = Nothing
Set objMail = Nothing
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

MrGJEB

New Member
Joined
Jun 13, 2008
Messages
37
i have checked the box by "OTOutlook 1.0 Type library" in tools/references.

It still does not appear to work. it that the right one?
 
Upvote 0

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
In my Office2002 at work it would be

Microsoft Outlook 10.0 Object Library

so yours should be very similar to this.
 
Upvote 0

Forum statistics

Threads
1,191,501
Messages
5,986,924
Members
440,067
Latest member
Swatts1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top