Solved

How to create an email function within Excel

Posted on 2010-08-25
32
366 Views
Last Modified: 2012-05-10
I am trying to create an excel sheet that I can update daily and then send out to multiplt email accounts.  I have read similar threads both on EE and the internet.  I am a little confused and was hoping to have someone point me to "instructions"
0
Comment
Question by:fijimark
[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
  • 13
  • 13
  • 4
  • +1
32 Comments
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33522825
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

Open in new window

0
 

Author Comment

by:fijimark
ID: 33522834
So do I just copy this to a sheet or create a macro?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33522845
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

Open in new window

0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33522926
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

Open in new window

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33522942
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.
0
 

Author Comment

by:fijimark
ID: 33523000
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33523006
If you just want to send the workbook to the same people every time, then the workbook.Sendmail method is probably sufficient.
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33523029
Ha, Ok....you're gonna need to exlpain where the email addresses will be.  What the body should be, the subject etc.
0
 
LVL 85

Expert Comment

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


0
 

Author Comment

by:fijimark
ID: 33523078
I tried creating the macro.  When I try to run I get a Compile Error: User-defeined type not defined
0
 
LVL 24

Expert Comment

by:broomee9
ID: 33523264
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).
0
 

Author Comment

by:fijimark
ID: 33524027
I tried to edit the VBE and references is not selectable.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33524113
You need to stop the code first.
0
 

Author Comment

by:fijimark
ID: 33524186
I now am getting other errors.  What info in the above code needs to changed?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524239
What error's are you getting?
0
 

Author Comment

by:fijimark
ID: 33524255
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
0
 

Author Comment

by:fijimark
ID: 33524274
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
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524275
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

Open in new window

0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524316
comment out attachments.add like this
'attachments.add
or just remove that line.
0
 

Author Comment

by:fijimark
ID: 33524322
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
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524347
did you goto Tools>References and choose "Microsoft Outlook 11.0/12.0...."
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524380
oops.....small problem with my code....sorry
Put quotes around Outlook.Application
set ol = createobject("Outlook.Application")
0
 

Author Comment

by:fijimark
ID: 33524407
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"
0
 

Author Comment

by:fijimark
ID: 33524429
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
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524514
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
0
 

Author Comment

by:fijimark
ID: 33524572
okay i am trying to do solution email same workbook.

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

Author Comment

by:fijimark
ID: 33524576
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
0
 
LVL 13

Accepted Solution

by:
MWGainesJR earned 500 total points
ID: 33524607
uncomment it.......
I only commented it because it didn't have file which was throwing the argument error......after giving it a file, uncomment it:
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 thisworkbook.fullname
    .Subject = "Vendor Pricing"
    .Body = "My Body"
    .Send
End With

Set Ol = Nothing
Set olm = Nothing
End Sub
Make sure the workbook is saved, or you'll get an error that it can't find it.
0
 

Author Comment

by:fijimark
ID: 33524655
Thanks.  How does rory's solution work?  Is it another macro?
0
 
LVL 13

Expert Comment

by:MWGainesJR
ID: 33524758
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.

 
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 33525202
It also doesn't assume you are using Outlook, though it appears you are. :)
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

Suggested Solutions

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article was originally published on Monitis Blog, you can check it here . If you have responsibility for software in production, I bet you’d like to know more about it. I don’t mean that you’d like an extra peek into the bowels of the sourc…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
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…

734 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