Phone message macro - need help to copy textbox value into mail body

**bmdo** · 05-29-2015, 04:51 AM

Hi,

our receptionist sends emails daily to a user as he/she was not available to take a call.
She just types in the subject - like ** Call Peter, from Microsoft, phone +45 22 33 44 55

We want to create a simple macro for her

I have the form ready - see attached picture

The code is almost ready

Please can you help me to finish it. I am not sure how to insert spaces in the subject, and I cannot get the txtComment.Value into the mail body

Please let me know if you need any more info or if some of the code is not correct

Private Sub cmdCancel_Click()
    Me.Hide
End Sub

Private Sub cmdSubmit_Click()

Dim oFSO As Object
    
    Dim objOutlook As Outlook.Application
    Dim objMail As Outlook.MailItem

    Set objMail = Outlook.CreateItem(olMailItem)
    strBody = objMail.HTMLBody
    
    'Insert Recipient
    objMail.To = txtTo.Value
    
    'Spaces missing
    objMail.Subject = "** Ring til" + txtContact.Value + txtOrg.Value + txtPhone.Value
        
    
    ' Set email body format to HTML
    objMail.BodyFormat = olFormatHTML
    
    ' Doesn't work
    objMail.HTMLBody = txtComment.Value
    
    ' Insert email text and signatur to email.
    objMail.HTMLBody = "<body font-size=11pt>" & sText & strBody & "</body>"
     

    Me.Hide
    objMail.Display
     
    Set oFSO = Nothing
    Set oFS = Nothing
    Set objMail = Nothing
    
End Sub

Private Sub UserForm_Activate()
        
    txtTo.Value = ""
    txtContact.Value = ""
    txtOrg.Value = ""
    txtPhone.Value = ""
    txtComment.Value = ""
    
End Sub

**bmdo** · 05-29-2015, 04:54 AM

Attached the image twice - removed it here

**gmayor** · 05-30-2015, 03:44 AM

The following should work for you. Note that as you are running from Outlook you don't have to create an Outlook item, you already have one that you are using!

Private Sub cmdSubmit_Click()

Dim objMail As Outlook.MailItem
Dim olInsp As Outlook.Inspector
Dim wdDoc As Object
Dim oRng As Object

    Me.Hide
    Set objMail = objOutlook.CreateItem(0)
    With objMail
        .To = Me.txtTo.Text
        'Spaces missing - Chr(32) is a space or " "
        .Subject = "** Ring til " & Me.txtContact.Text & Chr(32) & _
                   Me.txtOrg.Text & Chr(32) & Me.txtPhone.Text
        ' Set email body format to HTML
        .BodyFormat = 2
        Set olInsp = .GetInspector
        Set wdDoc = olInsp.WordEditor
        Set oRng = wdDoc.Range(0, 0)
        .Display
        oRng.Text = Me.txtComment.Text
        oRng.Font.Size = 11
    End With
    Set objMail = Nothing
lbl_Exit:
    Set olInsp = Nothing
    Set objMail = Nothing
    Set wdDoc = Nothing
    Set oRng = Nothing
    Exit Sub
End Sub

If you unload the form in the calling macro you won't need to reset the field values. e.g.

Option Explicit
            Sub ShowMyForm()
            UserForm1.Show
            Unload UserForm1
            End Sub

Thread: Phone message macro - need help to copy textbox value into mail body

Thread Tools

Display

Phone message macro - need help to copy textbox value into mail body

Posting Permissions