excel VBA sending mail

Can anyone tell me why this dose not work. The code below just keeps coming up with errors . Also I need to be able to reference cells to fill in the address, subject matter etc. So when the macro is run it should open outlook look at the current row take the email address and other contents for the title and body of the email then give me the option to press send.

Sub email()


Dim outlookOBJ As Outlook.Application
Dim mitem As Outlook.mailitem

Set outlookOBJ = New Outlook.Application
Set mitem = outlookOBJ.CreateItem(olMailItem)


With mitem

.To = "scoombes@****.com"
.Subjet = "Test"
.Body = "This is some text in an email"
.Display


End With
Sam CoombesAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Connect With a Mentor Excel & VBA ExpertCommented:
Or use late binding so the code will be compatible with all office versions.
Sub email()
Dim outlookOBJ As Object
Dim mitem As Object

Set outlookOBJ = CreateObject("Outlook.Application")
Set mitem = outlookOBJ.CreateItem(0)
With mitem
   .To = "scoombes@****.com"
   .Subject = "Test"
   .Body = "This is some text in an email"
   .Display
End With
End Sub

Open in new window

1
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
What error do you get?
Remember you are using early binding so you will need to add the reference of Microsoft Outlook <your office version> Object Library before running this code.

Also you have a typo in the code and the corrected line should be this...

.Subject = "Test"
0
 
Sam CoombesAuthor Commented:
The error is compile error

User-defined type not defined

Any ideas
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Add the reference of Microsoft Outlook <your office version> Object Library.

On VB Editor --> Tools --> References --> Scroll down to find Microsoft Outlook Object and check the box for it and click OK.
1
 
Sam CoombesAuthor Commented:
Thank you sooooo much
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Sam!
1
 
Sam CoombesAuthor Commented:
Any ideas on how to select a different mail box to send from
0
 
Sam CoombesAuthor Commented:
Fantastic great thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.