I'm working with the following code to send an e-mail. However, I want to highlight the variables I created. I tried dw_approved.Color = 65535 , however that and others like it haven't work.
Thanks for the help in advance. FYI, I used references to get this e-mail to work
Sub CreateFromTemplate()
Dim pulled_data As Worksheet
Set pulled_data = ThisWorkbook.Worksheets("Records")
'click on ITF Records before mailing out to get workweek date
dw_approved = Format(ThisWorkbook.Worksheets("Records").Range("AL30").Value, "$#,##0")
dw_pending = Format(ThisWorkbook.Worksheets("Records").Range("AK30").Value, "$#,##0")
dw_percent = Format(ThisWorkbook.Worksheets("Records").Range("AO30").Value, "0%")
itf_approved = Format(ThisWorkbook.Worksheets("Records").Range("AR30").Value, "$#,##0")
itf_pending = Format(ThisWorkbook.Worksheets("Records").Range("AQ30").Value, "$#,##0")
itf_percent = Format(ThisWorkbook.Worksheets("Records").Range("AT40").Value, "0%")
workweek_for_email = pulled_data.Range("B13").Value
ThisWorkbook.Worksheets("Records").Range("AJ18:AT51").Copy
dw_approved.Color = 65535
Dim myOlApp As Outlook.Application
Dim olEmail As Outlook.MailItem
Set myOlApp = CreateObject("Outlook.Application")
Dim olInsp As Outlook.Inspector
Dim wdDoc As Word.Document
Dim strbody As String
Set olEmail = myOlApp.CreateItemFromTemplate("C:\Users\rbirch\AppData\Roaming\Microsoft\Templates\2015_AES_Scorecard _ww.oft")
olEmail.Display
strbody = "Hello Team," & vbTab _
& vbCr & "Here is the scorecard and Daily Activity Tracker for workweek " & workweek_for_email & vbCr & vbCr & "Synopsis: Talk about the difference between workweek scorecards cards, big wins, small wins in DW. " _
& vbCr & vbCr & "Extracts are located at: http://myteams/sites/embedded3/Pipeline%20DB%20For%20John%20Hodges/Pipeline%20Extracts/Forms/AllItems.aspx" _
& vbCr & vbCr & "Design Wins Summary" & vbCr _
& "- Approved DW " & dw_approved & "K ( " & dw_percent & " approved to KSO goal)" & vbCr _
& "- Pending DW " & dw_pending & "K" & vbCr _
& vbCr & vbCr _
& "ITF Summary" & vbCr _
& "- Approved ITF " & itf_approved & "K ( " & itf_percent & " approved to KSO goal)" & vbCr _
& "- Pending ITF " & itf_pending & "K"
'make a random variable for salutations using an array
With olEmail
.BodyFormat = olFormatRichText
.Subject = "2015 AES Scorecard-ww" & workweek_for_email
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
.Display
wdDoc.Range.InsertBefore strbody
ThisWorkbook.Worksheets("Records").Activate
Range("AJ18:AT51").Copy
wdDoc.Range(Len(strbody), Len(strbody)).Paste
'wk7.ActiveChart.ChartArea.Copy
End With
' paste scorecard
'paste charts
With olEmail
.BodyFormat = olFormatHTML
.Attachments.Add ActiveWorkbook.FullName
End With
End Sub
Bookmarks