Sending email via UserForm

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

snoozee

Thread Starter
Joined
Apr 10, 2006
Messages
272
Hi

I need some VBA help please :)

I have a button on my userform that when clicked i want it to open up an email and for the email body to contain the text that the user has entered into TextBox2.

I've had a search around yet can't find anything that matches what i need to do :-(
 

dvk01

Derek
Retired Moderator Retired Malware Specialist
Joined
Dec 14, 2002
Messages
56,452
is this a web based application or are you using access or Excel or other similar programs on a computer to do it?
 

dvk01

Derek
Retired Moderator Retired Malware Specialist
Joined
Dec 14, 2002
Messages
56,452
That's OK. One of the experts in using this sort of set up will be on later. I am sure they can suggest something
 

Keebellah

Hans
Trusted Advisor
Joined
Mar 27, 2008
Messages
6,639
Hi there

Two options one explicit Outlook the first for the default mailer, just pass the parameters
This for the USerform button (Userform1 in your case)
Code:
Private Sub CommandButton2_Click()
Dim eMail       As String
Dim eSubject    As String
Dim eBody       As String
Dim URLString   As String

eMail = "[email protected]"
eSubject = "Due date: " & Format(CDate(Me.TbxDueDate.Value), "dddd mmmm d, yyyy")
eBody = Me.TextBox2.Value

URLString = "mailto:" & eMail & "?subject=" & eSubject & "&body=" & eBody

'*  use the existing default email client
SendMail URL:=URLString

'*  use Outlook (if present)
SendviaOutlook eMail:=eMail, eSubject:=eSubject, eBody:=eBody

End Sub

The mail module
Code:
Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As Long
#Else
    Private Declare Function ShellExecute Lib "shell32.dll" _
        Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, _
        ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
        ByVal nShowCmd As Long) As Long
#End If

Public Sub SendMail(URL As String)
' From Mr.Excel board
' Creates message including body text but now no excel file attached.
ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus
' Note The Shell function runs other programs asynchronously.
' This means that a program started with Shell might not finish executing before the statements following the Shell function are executed.
' Need to play with times to get it to work.
'Application.Wait (Now + TimeValue("0:00:09"))
'Application.SendKeys "%s"
'Application.Wait (Now + TimeValue("0:00:09"))
'MsgBox "Email Sent"
'Next
End Sub

Public Sub SendviaOutlook(eMail As String, eSubject As String, eBody As String, Optional eCC As String, Optional eBCC As String)
Dim oOLook As Object
Dim oEMail As Object
Set oOLook = CreateObject("Outlook.Application")
oOLook.Session.Logon
Set oEMail = oOLook.CreateItem(0)
oEMail.Display
On Error Resume Next
With oEMail
    .To = eMail
    .CC = eCC
    .BCC = eBCC
    .Subject = eSubject
    .Body = eBody
    .Display
End With
End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top