Sending E-mail from Excel via Comman Button

[Andy SIBL]

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]

[vba]


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
[/vba]

[Andy SIBL]

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]

Do you really have Outlook on that machine (not Outlook Express, Outlook)?

[Andy SIBL]

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]

Okay, so Outlook is not client resident? And what made you try that?

[Andy SIBL]

I googled :

"-2147024770 (8007007e) VBA Excel"

And the top result had the info requiired in it.

Google is your friend!

[lhtqasonline]

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

newbie

[georgiboy]

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

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