Solved

excel VBA sending mail

Posted on 2016-10-13
9
40 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 28

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 28

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
 
LVL 28

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

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

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Outlook Free & Paid Tools
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

759 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

19 Experts available now in Live!

Get 1:1 Help Now