How to create an email function within Excel

this is the basic code:

Sub SendEmail()
Dim Ol As Object 'create the outlook app object
Dim olm As Outlook.MailItem

Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook
Set olm = Outlook.CreateItem(olMailItem) 'set olm to a new mail message

With olm ' set the properties of the mail message
    .To = "Person@Domain.com; AnotherPerson@domain.com"
    .CC = "" 'see above
    .BCC = "" 'see above
    .Attachments.Add 'filename
    .Subject = "My subject"
    .Body = "My Body"
    .Send 'or .display to send it yourself
End With

Set Ol = Nothing
Set olm = Nothing

End Sub

Select allOpen in new window

So do I just copy this to a sheet or create a macro?

oops

Sub SendEmail()  
Dim Ol As Object 'create the outlook app object  
Dim olm As Outlook.MailItem  
  
Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook  
Set olm = Ol.CreateItem(olMailItem) 'set olm to a new mail message  
  
With olm ' set the properties of the mail message  
    .To = "Person@Domain.com; AnotherPerson@domain.com"  
    .CC = "" 'see above  
    .BCC = "" 'see above  
    .Attachments.Add 'filename  
    .Subject = "My subject"  
    .Body = "My Body"  
    .Send 'or .display to send it yourself  
End With  
  
Set Ol = Nothing  
Set olm = Nothing  
  
End Sub

Select allOpen in new window

would copy this to a module in VBA.....you would then edit the To, CC, BCC, Attachment, Body, and Subject lines as needed.....
For example you could do this (assuming A2:A10 have email addresses):

Sub SendEmail()    
Dim Ol As Object 'create the outlook app object    
Dim olm As Outlook.MailItem    
   
Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook    
Set olm = Ol.CreateItem(olMailItem) 'set olm to a new mail message    
 for each c in range ("A2:A10")
With olm ' set the properties of the mail message    
    .To = c.value    
    .CC = ""   
    .BCC = ""   
    .Attachments.Add 'filename    
    .Subject = "My subject"    
    .Body = "My Body"    
    .Send
End With
next c

Set Ol = Nothing    
Set olm = Nothing    
   
End Sub
This would send a seperate email to each email address in the range A2:A10.

Or you could send one email to all the emails like this:
 
 

Sub SendEmail()    
Dim Ol As Object 'create the outlook app object    
Dim olm As Outlook.MailItem    
dim recs as string    
Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook    
Set olm = Ol.CreateItem(olMailItem) 'set olm to a new mail message    
 for each c in range ("A2:A10")
       recs = recs & c.value & ";"
 next c
With olm ' set the properties of the mail message    
    .To = recs    
    .CC = ""    
    .BCC = ""   
    .Attachments.Add 'filename    
    .Subject = "My subject"    
    .Body = "My Body"    
    .Send 
End With 

Set Ol = Nothing    
Set olm = Nothing    
    
End Sub

Select allOpen in new window

If you post your workbook, I will get the code ready for you so that you can look over it and learn through your own example.

here you go
vendor-pricing-form.xlsx

If you just want to send the workbook to the same people every time, then the workbook.Sendmail method is probably sufficient.

Ha, Ok....you're gonna need to exlpain where the email addresses will be.  What the body should be, the subject etc.

Depends if the OP needs a body message. If not, then:
Activeworkbook.Sendmail array("John smith", "Bob wilson"), "here's the workbook"

I tried creating the macro.  When I try to run I get a Compile Error: User-defeined type not defined

Go to the Visual Basic Editor (VBE) by pressing Alt + F11.  Click on Tools --> References and select Microsoft Outlook 11.0 Object Library (11.0 may be different depending on version of Office).

I tried to edit the VBE and references is not selectable.

You need to stop the code first.

I now am getting other errors.  What info in the above code needs to changed?

What error's are you getting?

I copied the code from above in a macro called email.  I save the macro and document.  I then try to run the macro and I get a complie error: argument not optional

here is what I have...

Sub Email()
Dim Ol As Object 'create the outlook app object
Dim olm As Outlook.MailItem
Dim recs As String
Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook
Set olm = Ol.CreateItem(olMailItem) 'set olm to a new mail message
 For Each c In Range("A2:A10")
       recs = recs & c.Value & ";"
 Next c
With olm ' set the properties of the mail message
    .To = "mpanzica@brownevans.com"
    .CC = ""
    .BCC = ""
    .Attachments.Add
    .Subject = "My subject"
    .Body = "My Body"
    .Send
End With

Set Ol = Nothing
Set olm = Nothing
End Sub

run this.  do you get an error?

Sub SendEmail()  
Dim Ol As Object 'create the outlook app object  
Dim olm As Outlook.MailItem  
  
Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook  
Set olm = Outlook.CreateItem(olMailItem) 'set olm to a new mail message  
  
With olm ' set the properties of the mail message  
    .To = "Person@Domain.com; AnotherPerson@domain.com"  
    .CC = "" 'see above  
    .BCC = "" 'see above  
    '.Attachments.Add 'filename  
    .Subject = "My subject"  
    .Body = "My Body"  
    .display
End With  
  
Set Ol = Nothing  
Set olm = Nothing  
  
End Sub

Select allOpen in new window

comment out attachments.add like this
'attachments.add
or just remove that line.

when I run the below I get Run-time error '429':
activex component can't create object

Sub Email()
Dim Ol As Object 'create the outlook app object
Dim olm As Outlook.MailItem
Dim recs As String
Set Ol = CreateObject(Outlook.Application) 'set ol to a new instance of outlook
Set olm = Ol.CreateItem(olMailItem) 'set olm to a new mail message
 For Each c In Range("A2:A10")
       recs = recs & c.Value & ";"
 Next c
With olm ' set the properties of the mail message
    .To = "mpanzica@brownevans.com"
    .CC = ""
    .BCC = ""
    '.Attachments.Add 'filename
    .Subject = "My subject"
    .Body = "My Body"
    .Send
End With

Set Ol = Nothing
Set olm = Nothing
End Sub

did you goto Tools>References and choose "Microsoft Outlook 11.0/12.0...."

oops.....small problem with my code....sorry
Put quotes around Outlook.Application
set ol = createobject("Outlook.Application")

Getting closer.  Now when I run the macro it emails me but does not attach anything only gives me "my subject" and in body "my body"

I also found the attached outlook macro from another ee thread.  It brought me to another website to download.  Would you mind looking at the code to make sure it is legit.
Install-Outlook-Mail-Template.xls

you don't any templates....that's overkill......
just uncomment attachments.add and put a file next to it like this:
attachements.add "C:\myworkbook.xls"
Or to email the same workbook
attachments.add thisworkbook.fullname
If you're going to send the same exact email with the same workbook everytime, check out Rory's solution as well

okay i am trying to do solution email same workbook.

does the line read
'.attachments.add this workbook.fullname'

Sub Email()
Dim Ol As Object 'create the outlook app object
Dim olm As Outlook.MailItem
Dim recs As String
Set Ol = CreateObject("Outlook.Application") 'set ol to a new instance of outlook
Set olm = Ol.CreateItem(olMailItem) 'set olm to a new mail message
 For Each c In Range("A2:A10")
       recs = recs & c.Value & ";"
 Next c
With olm ' set the properties of the mail message
    .To = "mpanzica@brownevans.com"
    .CC = ""
    .BCC = ""
    '.Attachments.Add this workbook.fullname'
    .Subject = "Vendor Pricing"
    .Body = "My Body"
    .Send
End With

Set Ol = Nothing
Set olm = Nothing
End Sub

Thanks.  How does rory's solution work?  Is it another macro?

Rory's solution is a much more simple approach but takes away alot of the ability to customize the email......
It's actually only one line of code......
Thisworkbook.SendMail Recipients, Subject
it sends the email to the recipents with that subject and with the workbook attached.  No Body.
So to send your workbook:
Thisworkbook.SendMail "mpanzica@brownevans.com", "Vendor Pricing"
This email will be sent to that email, with Vendor Pricing as the subject, and with the workbook attached, with no Body.

 

It also doesn't assume you are using Outlook, though it appears you are. :)