Send email from excel to notes - save email in sent folder
Hi,
I am sending email from excel to notes using below code. Dim LotusNotesSession As Object Dim LotusNotesMailFile As Object Dim LotusNotesDocument As Object Dim LotusNotesField As Object strshdrivepath = Range("c33").Value If Right(strshdrivepath, 1) < "\" Then strshdrivepath = strshdrivepath & "\" End If Dim vaRecipient As Variant vaRecipient = Sheets("RawData").Range("c6:c30").Value strDate = Format(Now(), "mm/dd/yy") strmsg = "Daily Accum file as of " & strDate & " (Restricted)" vaFiles = strshdrivepath & Myfile & ".zip" Set LotusNotesSession = CreateObject("Notes.NotesSession") Set LotusNotesMailFile = LotusNotesSession.GETDATABASE("", "") LotusNotesMailFile.OPENMAIL Set LotusNotesDocument = LotusNotesMailFile.CREATEDOCUMENT Set LotusNotesField = LotusNotesDocument.APPENDITEMVALUE("Subject", strmsg) Set LotusNotesField = LotusNotesDocument.APPENDITEMVALUE("SendTo", vaRecipient) Set LotusNotesField = LotusNotesDocument.CREATERICHTEXTITEM("Body") With LotusNotesField .addnewline 1 .appendtext "Restricted" .addnewline 2 .appendtext "Hi All," .addnewline 2 .appendtext "Please find the attached file, in an Excel and Notebook format for your review." .addnewline 2 .appendtext "Thanks & Regards" .addnewline 1 .appendtext Sheets("RawData").Range("c36").Value .addnewline 1 .appendtext Sheets("RawData").Range("c37").Value .addnewline 1 .appendtext Sheets("RawData").Range("c38").Value .addnewline 2 .appendtext "********************* System Generated email ********************* " .addnewline 2 End With LotusNotesField = LotusNotesField.EmbedObject(1454, "", vaFiles) LotusNotesDocument.savemessageonsend = True LotusNotesDocument.Posteddate = Now() LotusNotesDocument.Send (0) Set LotusNotesSession = Nothing Set bjNotesSession = Nothing Set LotusNotesMailFile = Nothing Set LotusNotesDocument = Nothing Set LotusNotesField = Nothing SendMail = True MsgBox "The e-mail has successfully been created and sent!", vbInformation The multiple recipients are picked from Range("c6:c30").Value. The email is not getting saved in the sent item folder. please help me on that. I have tried below code to solve this ' Dim recip(25) As Variant ' recip(0) = Sheets("RawData").Range("c6").Value ' recip(1) = Sheets("RawData").Range("c7").Value ' recip(2) = Sheets("RawData").Range("c8").Value But my number of recepients can change. please help!! thx! Boss |
All times are GMT +1. The time now is 03:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com