1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sending email via UserForm

Discussion in 'Business Applications' started by snoozee, May 31, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    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 :-(
     
  2. dvk01

    dvk01 Moderator Malware Specialist

    Joined:
    Dec 14, 2002
    Messages:
    56,283
    First Name:
    Derek
    is this a web based application or are you using access or Excel or other similar programs on a computer to do it?
     
  3. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    Sorry I should have said i’m using excel
     
  4. dvk01

    dvk01 Moderator Malware Specialist

    Joined:
    Dec 14, 2002
    Messages:
    56,283
    First Name:
    Derek
    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
     
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    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
    
    
     
  6. snoozee

    snoozee It's My Birthday! Thread Starter

    Joined:
    Apr 10, 2006
    Messages:
    264
    Thanks heaps I'll give it a go :)
     
  7. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1210996

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice