Solved

excel VBA sending mail

Posted on 2016-10-13
9
61 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 31

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 31

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
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 31

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 31

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 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