• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 74
  • Last Modified:

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
0
Sam Coombes
Asked:
Sam Coombes
  • 4
  • 4
1 Solution
 
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Subodh Tiwari (Neeraj)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
 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now