Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Subject line in email defaults to workbook file name. | Excel Discussion (Misc queries) | |||
how to open email from opt on line to excel | New Users to Excel | |||
Can I link timeline labels to a line chart? | Charts and Charting in Excel | |||
A 2 line text showing up in the Cell in Excel prints in 1 line | Excel Discussion (Misc queries) | |||
pivot table multi line chart | Charts and Charting in Excel |