VB Help - Using a Command button from Excel to send email using any E-mail app

LillyVanilli

Hi,

I am making an excel spreadsheet with a Command button that when clicked should email the spreadsheet to an address. I have searched the web and found lots of coding but I cant get it to work. I have the following:

Option Explicit
Private moApp As Outlook.Application

Private Sub Command1_Click()

Dim oEmail As Outlook.MailItem

Set oEmail = moApp.CreateItem(olMailItem) ‘Or 0
oEmail.To = “x@domain.com”
oEmail.Subject = “Subject Text”
oEmail.Body = "Email Body Text"
oEmail.Send

End Sub

Private Sub Form_Load()
Set moApp = New Outlook.Application
End Sub


When compiling I get the following error:
Private moApp As Outlook.Application
User-defined type not defined.


Any idea what I need to do? I know that this coding is specific to Outlook and I would ideally like it to work for everybodies default email client so any help with changing the code to work with all clients would also be apprecaited.

Thanks

Malice

I've never done what you're describing so this may not be any help but this link creates the object a bit differently to your code (note the quotes around Outlook.Application):

[PHP]Set myOlApp = CreateObject("Outlook.Application")[/PHP]

Also make sure your references are set correctly. See the bottom of this page.

LillyVanilli

Ive actually got it working, kind of, by using different code I found (below).
In VB if I click Run Sub/UserForm, I get the "Microsoft Office Outlook error message saying a program is trying to automatically send email on behalf, do I want to allow this". But if I click the actual button in Excel nothing happens and I dont get this error message. Any ideas what could be wrong here? Also any ideas on how to convert this coding to work for all/most email clients?


Sub Mail_workbook_Outlook_1()
Dim OutApp As Object
Dim OutMail As Object

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

On Error Resume Next
With OutMail
.To = "x@domain.com"
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = "Hi there"
.Attachments.Add ActiveWorkbook.FullName
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing
End Sub

FSL

You need to code the on_click event for the button you have on the spreadsheet.

That is you need to execute the sub-routine you have coded when the button is clicked

LillyVanilli

Thanks FSL, you may have guessed Ive no idea what Im talking about so Im not sure what you mean. Could you let me know what exactly you mean by that?

--amadeus--

You need to assign the macro to the button, usually by right clicking on the control and choosing Assign Macro.

This is the best web resource for what you're trying to achieve, as far as I know.

LillyVanilli

Got it working, thanks a million for helping.