Hi,
I'm trying to insert an object into an e-mail but instead of it being at the end it is at the beginning, I want it at the end. I put the code in bold. Please make sure you turn on the appropriate references such as Microsoft Outlook and Word. Thanks for your help
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("Scorecard").Range("D13").Value, "$#,##0")
dw_pending = Format(ThisWorkbook.Worksheets("Scorecard").Range("C13").Value, "$#,##0")
dw_percent = Format(ThisWorkbook.Worksheets("Scorecard").Range("G23").Value, "0%")
itf_approved = Format(ThisWorkbook.Worksheets("Scorecard").Range("J13").Value, "$#,##0")
itf_pending = Format(ThisWorkbook.Worksheets("Scorecard").Range("I13").Value, "$#,##0")
itf_percent = Format(ThisWorkbook.Worksheets("Scorecard").Range("L23").Value, "0%")
workweek_for_email = pulled_data.Range("B13").Value
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
Set olEmail = myolApp.CreateItemFromTemplate("C:\Users\rbirch\AppData\Roaming\Microsoft\Templates\2015_AES_Scorecard _ww.oft")
olEmail.Display
'make a random variable for salutations using an array
With olEmail
.Subject = "2015 AES Scorecard-ww" & workweek_for_email
.Body = "Hi 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" ' change body??
End With
' paste figures into e-mail
With olEmail
.BodyFormat = olFormatRichText
Set olInsp = .GetInspector
Set wdDoc = olInsp.WordEditor
ThisWorkbook.Worksheets("Scorecard").Activate
Range("B1:L34").Copy
wdDoc.Range(Len(Body), Len(Body)).Paste 'syntax issue
End With
With olEmail
.BodyFormat = olFormatHTML
.Attachments.Add ActiveWorkbook.FullName
End With
End Sub
Bookmarks