LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
excelwatcher
 
Posts: n/a
Default textbox value to be used as a "subject" line within an email

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 = "
Const MailCC = "
Const MailBCC = "
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
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Subject line in email defaults to workbook file name. swellett Excel Discussion (Misc queries) 2 March 17th 06 04:35 PM
how to open email from opt on line to excel danny58 New Users to Excel 3 January 2nd 06 05:04 PM
Can I link timeline labels to a line chart? hizzle Charts and Charting in Excel 1 December 6th 05 09:36 AM
A 2 line text showing up in the Cell in Excel prints in 1 line Danny Excel Discussion (Misc queries) 6 July 12th 05 08:47 PM
pivot table multi line chart souris Charts and Charting in Excel 2 December 7th 04 03:56 AM


All times are GMT +1. The time now is 11:44 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"