Try this:
I'm still not completely clear about the content of the Message Body - but you should be able to modify those two lines/cells as you desire.
Spreadsheet:
[TABLE="class: head"]
[TR]
[TH][/TH]
[TH][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]A[/COLOR]
[/TH]
[TH][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]B[/COLOR]
[/TH]
[TH][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]C[/COLOR]
[/TH]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]1[/COLOR]
[/TD]
[TD]Email Field[/TD]
[TD]Current Data
[/TD]
[TD]Cell Content
[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]2[/COLOR]
[/TD]
[TD]Send To:[/TD]
[TD]fastlane@tpg.com.au[/TD]
[TD]<< Data[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]3[/COLOR]
[/TD]
[TD]Sender:[/TD]
[TD]As per mail client[/TD]
[TD]<< Data[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]4[/COLOR]
[/TD]
[TD]Copy To:[/TD]
[TD]col.delane@tpg.com.au[/TD]
[TD]<< Data[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]5[/COLOR]
[/TD]
[TD]Subject line text:[/TD]
[TD]Rainbow 2[/TD]
[TD]<< Data[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]6[/COLOR]
[/TD]
[TD]Message body text:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]7[/COLOR]
[/TD]
[TD] Line 1:[/TD]
[TD]03/07/19, 07:16:12 PM[/TD]
[TD]<< Formula[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]8[/COLOR]
[/TD]
[TD] Line 2:[/TD]
[TD]Reference/Placement: New Sheet @ $A$1, {-1,1,9}[/TD]
[TD]<< Data[/TD]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]9[/COLOR]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[/TABLE]
Formulas:
[TABLE="class: head"]
[TR]
[TH][/TH]
[TH][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]A[/COLOR]
[/TH]
[TH][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]B[/COLOR]
[/TH]
[TH][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]C[/COLOR]
[/TH]
[/TR]
[TR]
[TD][COLOR=[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF"]#FFFFFF[/URL] ]7[/COLOR]
[/TD]
[TD] Line 1:[/TD]
[TD]= TEXT( NOW(), "mm/dd/yy, hh:mm:ss AM/PM" )[/TD]
[TD]<< Formula[/TD]
[/TR]
[/TABLE]
I'm from down-under (Australia), so just check that NOW() and the TEXT formula returns the correct value and format for the date for your region.
Defined Names:
[TABLE="class: head"]
[TR]
[TD]BodyLine1[/TD]
[TD]='.'!$B$7[/TD]
[/TR]
[TR]
[TD]BodyLine2[/TD]
[TD]='.'!$B$8[/TD]
[/TR]
[TR]
[TD]CopyToEmailAddress[/TD]
[TD]='.'!$B$4[/TD]
[/TR]
[TR]
[TD]SendToEmailAddress[/TD]
[TD]='.'!$B$2[/TD]
[/TR]
[TR]
[TD]Subject[/TD]
[TD]='.'!$B$5[/TD]
[/TR]
[/TABLE]
VBA Code:
- Copy this code to a standard module in the Visual Basic Editor
- Add a button (Form Control) to the spreadsheet, or to your Quick Access Toolbar, and link it to the macro.
Code:
Option Explicit
Sub mail_text_html()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim EmailSendTo As String
Dim EmailCopyTo As String
Dim EmailSubject As String
Dim myRange As Range
Dim EmailBody1 As String
Dim EmailBody2 As String
Set myRange = Range("SendToEmailAddress")
EmailSendTo = myRange.Text
Set myRange = Range("CopyToEmailAddress")
EmailCopyTo = myRange.Text
Set myRange = Range("Subject")
EmailSubject = myRange.Text
Set myRange = Range("BodyLine1")
EmailBody1 = myRange.Text
Set myRange = Range("BodyLine2")
EmailBody2 = myRange.Text
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.Subject = EmailSubject
.To = EmailSendTo
.CC = EmailCopyTo
.Body = EmailBody1 & vbNewLine & EmailBody2
Select Case MsgBox("Click:" & vbLf & vbLf & "Yes to DISPLAY the message (then manually send from email client)," & vbLf & vbLf & _
"No to SEND the message directly without displaying, or" & vbLf & vbLf & "Cancel to abort.", vbYesNoCancel Or vbQuestion Or vbDefaultButton1, "E-mail Despatch")
Case vbYes
.Display
Case vbNo
.Send
MsgBox "Done."
Case vbCancel
Exit Sub
End Select
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
Post back with any issues / what you think.