Solved

How to create an email function within Excel

Posted on 2010-08-25
32
361 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
The viewer will learn how to successfully download and install the SARDU utility on Windows 8, without downloading adware.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now