[SOLVED:] Phone message macro - need help to copy textbox value into mail body [Archive]

View Full Version : [SOLVED:] 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