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
"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