Hi All,
I have this code that will automatically create an email template from excel.
I'm having trouble with the settings. I want the message to automatically set to Sensitivity = Private.
Dim Sheet1 As Worksheet
Dim Sheet2 As Worksheet
Dim olLook As Object 'Start MS Outlook
Dim olNewEmail As Object 'New email in Outlook
Dim strContactEmail As String 'Contact email address
Dim Overall As Range
Set olLook = CreateObject("Outlook.Application")
Set olNewEmail = olLook.CreateItemFromTemplate("P:\Email Template - Aaron\Untitled.oft")
Set Sheet1 = Worksheets("Sheet1")
Set Sheet2 = Worksheets("Sheet2")
DistributionFigure = Sheet2.Range("B11").Value
LoanAmountPrincipal = Sheet2.Range("B14").Value
Difference = Sheet2.Range("B16").Value
DistributionFigure = Format(Sheet2.Range("B11"), "Currency")
LoanAmountPrincipal = Format(Sheet2.Range("B14"), "Currency")
Difference = Format(Sheet2.Range("B16"), "Currency")
'strEmailSubject = Range("M9") & Range("D11") & " " & UCase(Range("D12"))
strEmailText = ""
'strContactEmail = NAME@DOMAIN
With olNewEmail 'Attach template
.to = ""
.CC = ""
.htmlBody = Msg & Sheet2.Range("B2").Value & " " & "<b>" & Sheet2.Range("B3").Value & "</b>" & _
"<br><br>" & "<b><u>" & Sheet2.Range("B6").Value & "</b></u>" & _
"<br><br>" & Sheet2.Range("B8").Value & "<b>" & Sheet2.Range("B9").Value & "</b>" & _
Sheet2.Range("B10").Value & "<b>" & DistributionFigure & "</b>" & Sheet2.Range("B12").Value & _
"<b>" & Sheet2.Range("B9").Value & "</b>" & Sheet2.Range("B13").Value & "<b>" & LoanAmountPrincipal & "</b>" & _
Sheet2.Range("B15").Value & "<b>" & Difference & "</b>" & Sheet2.Range("B17").Value & "<br><br>" & _
Sheet2.Range("B36").Value & "<br><br>" & _
"<b>" & Sheet2.Range("B18").Value & "</b><br><br>" & " " & Sheet2.Range("B19").Value & _
"<u>" & Sheet2.Range("B20").Value & "</u>" & Sheet2.Range("B21").Value & "<b>" & Difference & "</b>" & Sheet2.Range("B22").Value & _
"<br><br> " & Sheet2.Range("B23").Value & "<br><br>" & _
"  " & Sheet2.Range("B24").Value & "<br>  " & Sheet2.Range("B25").Value & _
"<br>  " & Sheet2.Range("B26").Value & "<br><br>  " & Sheet2.Range("B27").Value & "<br><br>" & _
Sheet2.Range("B28").Value & "<u>" & Sheet2.Range("B29").Value & "</u>" & Sheet2.Range("B30").Value & _
"<b>" & Difference & "</b>" & Sheet2.Range("B31").Value & "<br><br>" & Sheet2.Range("B32").Value & _
"<br><br>" & Sheet2.Range("B33").Value
.Subject = Sheet2.Range("A6").Value
'.attachments.Add Range("O2").Value
.display
End With
As for the workbook, I cant upload it since it's confidential.
Any help will do. Thanks in advance.
Bookmarks