Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 382
  • Last Modified:

How to create an email function within Excel

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
fijimark
Asked:
fijimark
  • 13
  • 13
  • 4
  • +1
1 Solution
 
MWGainesJRCommented:
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
 
fijimarkAuthor Commented:
So do I just copy this to a sheet or create a macro?
0
 
MWGainesJRCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
MWGainesJRCommented:
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
 
MWGainesJRCommented:
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
 
fijimarkAuthor Commented:
0
 
Rory ArchibaldCommented:
If you just want to send the workbook to the same people every time, then the workbook.Sendmail method is probably sufficient.
0
 
MWGainesJRCommented:
Ha, Ok....you're gonna need to exlpain where the email addresses will be.  What the body should be, the subject etc.
0
 
Rory ArchibaldCommented:
Depends if the OP needs a body message. If not, then:
Activeworkbook.Sendmail array("John smith", "Bob wilson"), "here's the workbook"


0
 
fijimarkAuthor Commented:
I tried creating the macro.  When I try to run I get a Compile Error: User-defeined type not defined
0
 
TracyVBA DeveloperCommented:
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
 
fijimarkAuthor Commented:
I tried to edit the VBE and references is not selectable.
0
 
Rory ArchibaldCommented:
You need to stop the code first.
0
 
fijimarkAuthor Commented:
I now am getting other errors.  What info in the above code needs to changed?
0
 
MWGainesJRCommented:
What error's are you getting?
0
 
fijimarkAuthor Commented:
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
 
fijimarkAuthor Commented:
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
 
MWGainesJRCommented:
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
 
MWGainesJRCommented:
comment out attachments.add like this
'attachments.add
or just remove that line.
0
 
fijimarkAuthor Commented:
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
 
MWGainesJRCommented:
did you goto Tools>References and choose "Microsoft Outlook 11.0/12.0...."
0
 
MWGainesJRCommented:
oops.....small problem with my code....sorry
Put quotes around Outlook.Application
set ol = createobject("Outlook.Application")
0
 
fijimarkAuthor Commented:
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
 
fijimarkAuthor Commented:
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
 
MWGainesJRCommented:
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
 
fijimarkAuthor Commented:
okay i am trying to do solution email same workbook.

does the line read
'.attachments.add this workbook.fullname'
0
 
fijimarkAuthor Commented:
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
 
MWGainesJRCommented:
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
 
fijimarkAuthor Commented:
Thanks.  How does rory's solution work?  Is it another macro?
0
 
MWGainesJRCommented:
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
 
Rory ArchibaldCommented:
It also doesn't assume you are using Outlook, though it appears you are. :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

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