Emailing directly from Excel

I have a user form for a bug / feedback report that allows the users to email me. The problem is that the only thing I could get working so far is a script that sends the email through Outlook. If Outlook isn't open, then it doesn't send.

For several reasons, I would like to be able to email directly out of Excel. Can anyone help? In a perfect world, this wouldn't require the users to install any additional files, but if that's as good as I can get, then so be it.

BTW, I'm using Office 2007, but would like it to be backwards compatible if possible.

Thanks for the help!!!

BHsoundman

Show us the script, it will be easier to try and amend it.

Thanks, Here's the script that is fully functional.


Private Sub CommandButton1_Click()

If Sheets("combos").Range("h48") = "" Then
msgbox "Please select the subject of the email."
cancel = True
Else

Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = "email address goes here"
.CC = ""
.BCC = ""
.Subject = "Sales Kiosk v" & Sheets("combos").Range("h44") & Sheets("combos").Range("h48")
.body = "Name : " & TextBox1.Value & Chr(13) & Chr(10) & "e-Mail : " & TextBox2.Value & Chr(13) & Chr(10) & "Mailing List : " & Sheets("combos").Range("h47") & Chr(13) & Chr(10) & "Operating System : " & TextBox4.Value & Chr(13) & Chr(10) & "Excel Version : " & TextBox5.Value & Chr(13) & Chr(10) & "Comments : " & TextBox3.Value
.Send
.ReadReceiptRequested = False
End With
On Error GoTo 0


Set OutMail = Nothing
Set OutApp = Nothing

Dim Msg, Style, Title
Msg = "Thanks for your feedback." & Chr(13) & Chr(10) & "Your comments will be sent once Outlook is open." & Chr(13) & Chr(10) & "Press OK to continue."
Style = vbOKOnly + vbInformation
Title = "Bug Report Status"
Response = msgbox(Msg, Style, Title)
ActiveWorkbook.Protect Structure:=True, Windows:=False, Password:="audio"
Unload Me
End If
End Sub

I usually add a reference for both Microsoft Office 11.0 Object Library and Microsoft Outlook 11.0 Object Library whenever working with other microsoft office applications from excel. Do this by going to, in VBA window, Tools>References> place a checkbox in both those two.

Here is sample code I have successfully used:


On error goto Error
Dim olApp As outlook.Application, olMail As MailItem
Dim txtEmailAddress as string, txtSubject as string, txtBody as string
Dim ws as worksheet: set ws = thisworkbook.sheets("SheetName")

Set olApp = New outlook.Application
Set olMail = olApp.CreateItem(olMailItem)

With olMail
.To = ws.range("A1")
.Subject = ws.range("A2")
.Body = ws.range("A3")
.Send
End With

Set olApp = Nothing
Set olMail = Nothing
Exit Sub
Error:
msgbox "Error sending email."


Couple of notes: You should declare all variables prior to using them, also reccommend setting Option Explicit on your modules, can do this automatically through Tools>Options>Editor>Require Variable Declaration and also when referencing a worksheet, you should always use something like Thisworkbook.Sheets("SheetName") or Thisworkbook.Sheet1, or you can set a variable for it.

Thanks for the reply Josh, but this script would still utilize outlook if i'm correct. Since I'm trying to use it as a stand alone application, I assume I would need to include the smtp etc info in order to send.

If you already have outlook setup for regular use (ie smtp etc), you should not have to do anything other then create the app, create the mail, then fill out in the info and send. You should not need to fill out anything regarding the outlook settings as they default to what is already in outlook.

That's correct. This version of the script works as anticipated, but it requires Outlook. What I want to do is avoid Outlook (or any application aside from Excel) and email it directly from Excel.

The primary reason is that I'm using the workbook as a point of sales kiosk on rental laptops and I want to avoid having to setup Outlook profiles on each machine.

Thanks,
Bill

If you have Windows and you don't want to use an email client (i.e. Outlook), even behind the scene's, which I would still recommend you do, you can use the CDO method. Read about it, along with a code sample, here...

http://www.rondebruin.nl/cdo.htm

HTH

Ahh well I understand now.

Thank you , Thank you, Thank you!! I finally got the basic scripting to work. I just have to adapt it to my project. Thanks!!!