View Full Version : Sending E-mail from Excel via Comman Button
Andy SIBL
08-02-2007, 07:29 AM
This is probably really straight forward but I really don't know where to start with VBA.
What Code do I need to write so that when the button is clicked on a new email is opended up with the to, bcc (multiple recipients) and subject field pre-completed?
The starting point is:
Private Sub CommandButton1_Click()
End Sub
I use outlook if this is of any help.
I have been struggling with this for hours!
Any help greatly appreciated!
Andy
Bob Phillips
08-02-2007, 07:33 AM
Dim oOutlook As Object
Dim oMailItem As Object
Dim oRecipient As Object
Dim oNameSpace As Object
Set oOutlook = CreateObject("Outlook.Application")
Set oNameSpace = oOutlook.GetNameSpace("MAPI")
oNameSpace.Logon , , True
Set oMailItem = oOutlook.CreateItem(0)
Set oRecipient = _
oMailItem.Recipients.Add("someone@somewhere.com")
oRecipient.Type = 1 '1 = To, use 2 for cc
'keep repeating these lines with
'your names, adding to the collection.
With oMailItem
.Subject = "The subject matter."
.Body = "The body text"
.Attachments.Add ("filename") 'change to your filename
.Display 'use .Send when all testing done
End With
Andy SIBL
08-02-2007, 08:05 AM
When I try and run it it comes up the following error:
'-2147024770 (8007007e)'
And when I debug it it highlights the following line in yellow:
Set oOutlook = CreateObject("Outlook.Application")
Am I missing something?
Bob Phillips
08-02-2007, 08:30 AM
Do you really have Outlook on that machine (not Outlook Express, Outlook)?
Andy SIBL
08-02-2007, 08:42 AM
No it is Outlook not Outlook express but I have managed to remedy it by amending the script to:
Set oOutlook = CreateObject("Outlook.Application", "localhost").
It works perfectly now.
Many thanks for your help!
Bob Phillips
08-02-2007, 10:52 AM
Okay, so Outlook is not client resident? And what made you try that?
Andy SIBL
08-03-2007, 12:03 AM
I googled :
"-2147024770 (8007007e) VBA Excel"
And the top result had the info requiired in it.
Google is your friend!
lhtqasonline
01-15-2009, 03:05 AM
Hi
I wish to use the code in this thread but i have a different need
In my userform i have a textbox called SuppEmail that shows the e-mail address of my supplier from my worksheet
I need the code to take the e-mail address in the textbox and add that as the recipient of the e-mail when i click the button
Any ideas
: pray2: newbie
georgiboy
01-15-2009, 03:27 AM
Hi
I wish to use the code in this thread but i have a different need
In my userform i have a textbox called SuppEmail that shows the e-mail address of my supplier from my worksheet
I need the code to take the e-mail address in the textbox and add that as the recipient of the e-mail when i click the button
Any ideas
: pray2: newbie
You should find what you need in this...
http://www.vbaexpress.com/forum/showthread.php?t=24694
I have put a workbook in there that grabs outlook contacts and diplays them in a listbox, you then select the email and hit the button. You could change the listbox for a textbox.
Hope this helps
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.