textbox value to be used as a "subject" line within an email

E

excelwatcher

I have a textbox for which I would like the value of that to be placed in the
"subject" line within a new email (Outlook)....what would the code be to do
this?

A user would click a button and have that value (from the textbox)become
populated in the "subject" line.
The "textbox" is NOT in a form....I just placed various textboxes within a
spreadsheet.

Here is the code I am using....
------------------------------------------------------------------------------
----------------------------------------
Sub EmailandSaveCellValue()

'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, MailTxt As String

'*********************************************************
'Set email details; Comment out if not required
Const MailTo = "(e-mail address removed)"
Const MailCC = "(e-mail address removed)"
Const MailBCC = "(e-mail address removed)"
MailSub = "Please review " & Range("Subject") <---This is where I need
the textfield value
MailTxt = "I have attached " & Range("Subject")
'*********************************************************

'Turns off screen updating
Application.ScreenUpdating = False

'Makes a copy of the active sheet and save it to
'a temporary file
ActiveSheet.Copy
Set WB = ActiveWorkbook
FileName = Range("Subject") & " Text.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\" & FileName

'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = MailTo
.Cc = MailCC
.Bcc = MailBCC
.Subject = MailSub
.Body = MailTxt
.Attachments.Add WB.FullName
.Display
End With

'Deletes the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restores screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top