Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assign a macro to send email in lotus notes from Excel spreadsheet | Excel Worksheet Functions | |||
Send email in Lotus Notes | Excel Programming | |||
How do I send an Excel workbook through Lotus Notes Email? | Excel Discussion (Misc queries) | |||
Excel VBA to send email using Lotus Notes | Excel Programming | |||
Send email with Lotus Notes | Excel Programming |