I'm automatizing a service and I was able to create a mail using xls vba code but my issue is that the file is attacched externally of the body of the mail while I'd like to have the following situation:
Mail Body
text...
attached file
text...
End of the mail Body
I used the following code
Sub Send_Email_via_Lotus_Notes()
Dim oSession ' AS NotesSession
Dim strServer
Dim strUserName
Dim strMailDbName
Dim oCurrentMailDb ' as NOTESDATABASE
Dim oMailDoc ' as NOTESDOCUMENT
Dim ortItem ' as NOTESRICHTEXTITEM
Dim ortAttachment ' as NOTESRICHTEXTITEM
Dim oEmbedObject ' as ????
Dim cstrAttachment
Dim blAttachment
Dim serialNumber As String
Dim ticketNumber As String
ticketNumber = Range("C7").Value
serialNumber = Range("C19").Value
cstrAttachment = "C:\dummy.txt"
blAttachment = True
' Start a session to notes
Set oSession = CreateObject("Notes.NotesSession")
strServer = oSession.GETENVIRONMENTSTRING("MailServer", True)
strUserName = oSession.UserName
strServer = oSession.GETENVIRONMENTSTRING("MailServer", True)
strUserName = oSession.UserName
strMailDbName = Left(strUserName, 1) & Right(strUserName, (Len(strUserName) - InStr(1, strUserName, ""))) & ".nsf"
' open the mail database in Notes
Set oCurrentMailDb = oSession.CURRENTDATABASE
If oCurrentMailDb.IsOpen = False Then
MsgBox "## Opening Lotus Notes mail database..."
oCurrentMailDb.OPENMAIL
End If
' Create a document in the back end
Set oMailDoc = oCurrentMailDb.CREATEDOCUMENT
' Set the form name to memo
oMailDoc.form = "Memo"
With oMailDoc
.SendTo = "[email protected]"
'.BlindCopyTo = "[email protected]"
'.CopyTo = "[email protected]"
.Subject = "ACTION REQUIRED - Format the disk/s of your system in de-activation and sign the related form"
End With
Set BoldBlueStyle = oSession.CREATERICHTEXTSTYLE
Set NormalStyle = oSession.CREATERICHTEXTSTYLE
With BoldBlueStyle
.NOTESFONT = DEFAULT_SANS_SERIF
.FontSize = 14
.NOTESCOLOR = COLOR_BLUE
.Bold = True
End With
With NormalStyle
.NOTESFONT = DEFAULT_SANS_SERIF
.FontSize = 12
.NOTESCOLOR = COLOR_BLACK
.Bold = False
End With
Set obAttachment = oMailDoc.CREATERICHTEXTITEM("cstrAttachment")
Set ortItem = oMailDoc.CREATERICHTEXTITEM("Body")
With ortItem
.APPENDTEXT ("Hi")
.ADDNEWLINE (1)
.APPENDTEXT ("This note is related to the de-acativation of the system/s owned by you requested by the SR:")
.ADDNEWLINE (1)
.APPENDSTYLE (BoldBlueStyle)
.APPENDTEXT ("=================================================================")
.ADDNEWLINE (1)
.ADDTAB (2)
.APPENDTEXT ("SR# System S/N")
.ADDNEWLINE (1)
.ADDTAB (2)
.APPENDTEXT ("") + ticketNumber
.ADDTAB (3)
.APPENDTEXT ("") + serialNumber
.ADDNEWLINE (1)
.APPENDTEXT ("=================================================================")
.ADDNEWLINE (2)
.APPENDSTYLE (NormalStyle)
.APPENDTEXT ("As part of the new De-Activation (DeEoS) process,")
.ADDNEWLINE (2)
.APPENDTEXT ("https://securelinux.romelab.it.ibm.com/security/Process_EoS.html")
.ADDNEWLINE (2)
.APPENDTEXT ("you are required for the distruction of the residual IBM Confidential Information from the")
.ADDNEWLINE (1)
.APPENDTEXT ("Hard Disk/s.")
.ADDNEWLINE (1)
.APPENDTEXT ("Once you have performed the format of the disk/s:")
.ADDNEWLINE (2)
.APPENDTEXT (" 1 - Download the precompiled form from this note")
.ADDNEWLINE (1)
.APPENDTEXT (" 2 - Print and Sign it")
.ADDNEWLINE (1)
.APPENDTEXT (" 3 - Make available the signed form to the operator who will pick up your system.")
.ADDNEWLINE (2)
.APPENDTEXT ("The signed form will be archived, and made ··available for future reference.")
.ADDNEWLINE (2)
.APPENDSTYLE (BoldBlueStyle)
.APPENDTEXT ("================= Form To Download and sign =====================")
.ADDNEWLINE (2)
End With
With obAttachment
Set EMBEDOBJECT = obAttachment.EMBEDOBJECT(EMBED_ATTACHMENT, "", cstrAttachment, "Attachment" )
End With
With ortItem
.APPENDTEXT ("<SR_<Ticket Number>_formatting.pdf> ")
.APPENDTEXT ("=================================================================")
.ADDNEWLINE (2)
.APPENDSTYLE (NormalStyle)
.APPENDTEXT ("For the NON intel systems, the destruction of the residual data can be usually done, using the")
.ADDNEWLINE (1)
.APPENDTEXT ("appropriate installation CD/DVD of the involved OS.")
.ADDNEWLINE (2)
.APPENDTEXT ("For the Intel based systems, this operation can be done, burning a CD,on another system, using")
.ADDNEWLINE (1)
.APPENDTEXT ("the iso downloadable from ISSI at the link showed below: ")
.ADDNEWLINE (2)
.APPENDTEXT (" http://ibmurl.hursley.ibm.com/45PX ")
.ADDNEWLINE (2)
.APPENDTEXT ("You can burn the formatting CD also on another machine, and use it on the system under")
.ADDNEWLINE (1)
.APPENDTEXT ("DeEos (De Activation).")
.ADDNEWLINE (2)
.APPENDTEXT ("Many Thanks.")
.ADDNEWLINE (4)
.APPENDTEXT ("Cordiali Saluti / Best Regards")
.ADDNEWLINE (1)
.APPENDTEXT ("________________________________________")
.ADDNEWLINE (1)
.APPENDTEXT ("IBM Software Group Tivoli - Rome")
.ADDNEWLINE (1)
.APPENDTEXT ("Via Sciangai 53 - 00144 Rome Italy")
.ADDNEWLINE (1)
.APPENDTEXT ("+39 06 59660166")
.ADDNEWLINE (1)
.APPENDTEXT ("[email protected]")
.ADDNEWLINE (1)
.APPENDTEXT ("________________________________________")
End With
MsgBox "## Sending email..."
With oMailDoc
.PostedDate = Now()
.SAVEMESSAGEONSEND = "True"
.SEND (False)
End With
MsgBox "## Sent !"
' close objects
Set oMailDoc = Nothing
Set oCurrentMailDb = Nothing
Set oSession = Nothing
End Sub
I'd like to know how to have the file "inside" the body text instead of an attachment external to the body.
Thank you
