Solved

excel VBA sending mail

Posted on 2016-10-13
9
53 Views
Last Modified: 2016-10-27
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
0
Comment
Question by:Sam Coombes
  • 4
  • 4
9 Comments
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41842231
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
 

Author Comment

by:Sam Coombes
ID: 41842246
The error is compile error

User-defined type not defined

Any ideas
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41842255
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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41842264
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
 

Author Comment

by:Sam Coombes
ID: 41842269
Thank you sooooo much
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41842278
You're welcome Sam!
1
 

Author Comment

by:Sam Coombes
ID: 41842279
Any ideas on how to select a different mail box to send from
0
 

Author Closing Comment

by:Sam Coombes
ID: 41862738
Fantastic great thank you
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Outlook Free & Paid Tools
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now