Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to create an email function within Excel

Posted on 2010-08-25
32
Medium Priority
?
376 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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:Tracy
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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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.
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

610 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