I have a macro vba code below but i don't know how to manipulate it in my desired output.
1st Paragraph: Hi All,
2nd Paragraph: GED Day1 Adjustments report is now ready for your review.
3rd Paragraph: Should you require amendments in your Risk Assessment status, please refer to Day1 tab of the file. Kindly include the amendments you made in your confirmation email.
4th Paragraph: Screenshot (jpeg)
5th Paragraph: ***Risk assessments of the Day1 hold outs are based on (1) Amount, (2) Age Days, and (3)Level of comfort***
6th Paragraph should look into Excel sheet email cell e17
Public Sub Send_Notes_Email()
'Requires reference to Lotus Domino Objects (domobj.tlb) for constants such as EMBED_ATTACHMENT and FONT_HELV, etc.
'Code based on answer by Bill-Hanson:
'http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/Lotus_SmartSuite/Lotus_Notes/Q_22733557.html#a19616928
Dim NSession As Object
Dim NUIWorkspace As Object
Dim NMailDb As Object
Dim NDocumentTemp As Object
Dim NUIDocumentTemp As Object
Dim NUIDocument As Object
Dim NRTItemBody As Object
Dim NRTStyle As Object, NRTStyleDefault As Object
Dim NRTItemAttachment As Object, embeddedAttachment As Object
Dim Subject As String
Dim SendTo As String, CopyTo As String, BlindCopyTo As String
Dim fileAttachment As String
Dim embedCells As Range
Dim FSO As Object
Dim tempFolder As String, tempCellsJPG As String
Dim Copy_and_Paste As Boolean
'--------- EDIT USER-DEFINED SETTINGS IN THIS SECTION ---------
'The Excel cells to be included in the email body as an image
Set embedCells = ActiveSheet.Range("A1:C8")
'The file to be attached to the email, if it exists
fileAttachment = "C:\folder1\folder2\file.txt"
SendTo = "email1@email.com,email2@email.com"
CopyTo = "email2@email.com"
BlindCopyTo = ""
Subject = "Email subject"
'--------- END OF USER-DEFINED SETTINGS ---------
'Copy_and_Paste flag
'True = copy and paste Excel cells into email body using the clipboard
'False = save Excel cells as a temporary .jpg file and import into email body
Copy_and_Paste = True
Set FSO = CreateObject("Scripting.FileSystemObject")
tempFolder = FSO.GetSpecialFolder(2)
'File name for temporary .jpg file containing Excel cells
tempCellsJPG = tempFolder & "\" & Replace(FSO.GetTempName(), ".tmp", ".jpg")
Set NSession = CreateObject("Notes.NotesSession") 'OLE (late binding only) because we access Notes UI classes
Set NUIWorkspace = CreateObject("Notes.NotesUIWorkspace")
Set NMailDb = NSession.GetDatabase("", "")
NMailDb.OpenMail
'Create the default rich text style
Set NRTStyleDefault = NSession.CreateRichTextStyle
With NRTStyleDefault
.NotesColor = COLOR_BLACK
.FontSize = 10
.NotesFont = FONT_HELV
.Bold = False
.Italic = False
End With
Set NRTStyle = NSession.CreateRichTextStyle
'Create a temporary NotesDocument
Set NDocumentTemp = NMailDb.CreateDocument
With NDocumentTemp
.Form = "Memo"
'Add a rich text item to contain the email body text and file attachment
Set NRTItemBody = .CreateRichTextItem("Body")
With NRTItemBody
'--------- ADD/EDIT CODE IN THIS SECTION FOR THE EMAIL BODY TEXT ---------
'Compose the email body text
.AppendText "1st paragraph - default font."
.AddNewLine 2
With NRTStyle
.NotesFont = FONT_ROMAN
.FontSize = 14
.NotesColor = COLOR_BLUE
.Bold = True
End With
.AppendStyle NRTStyle
.AppendText "2nd paragraph - Times New Roman Blue 14 Bold"
.AddNewLine 2
'Add placeholder text which will be replaced by the Excel cells
.AppendText "{PLACEHOLDER}"
.AddNewLine 2
With NRTStyle
.NotesFont = FONT_HELV
.FontSize = 10
.NotesColor = COLOR_RED
.Italic = True
End With
.AppendStyle NRTStyle
.AppendText "3rd paragraph - Helvetica Red 10 italic."
'Same paragraph, default style
.AppendStyle NRTStyleDefault
.AppendText " Excel cells are shown above."
If fileAttachment <> "" Then
.AddNewLine 2
.AppendText fileAttachment & " attached"
.AddNewLine 1
.EmbedObject EMBED_ATTACHMENT, "", fileAttachment
.AddNewLine 1
End If
'--------- END OF EMAIL BODY TEXT SECTION --------
End With
.Save False, False
End With
Bookmarks