Excel to Open Lotus Notes
 

Hi,

I need some VB code that will enable me to send an attachment from
excel to Lotus Notes, but I would like to enable the user to add email
addresses once the file has been forwarded to Lotus Notes.

This is the code i have at the moment - it create the par spreadsheet,
then attaches it to an email, but it sends it just to one address which
I have inputted in the code, this send the attachment invisably. Which
means you cannot add other addressee's in the email without the code.
CAN SOMEONE PLEASE HELP!

Sub Email_IMP_EXP()

Dim noSession As Object, noDatabase As Object
Dim noDocument As Object, noAttachment As Object
Dim vaFiles As Variant
Dim OneFile As Boolean
Dim I As Long
Dim strFile As String
Dim wb As Workbook
Dim strdate As String

Const EMBED_ATTACHMENT = 1454
Dim stSubject As String
Dim stMsg As String
Dim vaRecipient As Variant

'Populate the variant-array with recipients.
vaRecipient = InputBox("Email recipient", "?????", ")
stSubject = "Import & Export Bonus Figures"
stMsg = "Please find attached the Import and Export Bonus
Information"
'Get the Excel-files from the user(s).
strdate = Format(Now, "dd-mm-yy")
Sheets(Array("IMPORT Daily", "EXPORT Daily")).Copy
Set wb = ActiveWorkbook
With wb
.SaveAs ThisWorkbook.Name & " " & strdate & ".xls"
End With

strFile = ActiveWorkbook.FullName
OneFile = True

finish:
'Instantiate the Lotus Notes COM objects.
Set noSession = CreateObject("Notes.NotesSession")
Set noDatabase = noSession.GETDATABASE("", "")

'Check if mail-database is open or not.
If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

'Instantiate the Lotus E-mailobject.
Set noDocument = noDatabase.CREATEDOCUMENT
Set noAttachment = noDocument.CREATERICHTEXTITEM("Body")

'Add the selected Excel-files to the E-mail.
With noAttachment
.EMBEDOBJECT EMBED_ATTACHMENT, "", strFile
End With
With wb
.Close False
End With
'Populate mainproperties of the created E-mail and save & send
'the e-mail.

With noDocument
.Form = "Memo"
' .SendTo = vaRecipient
.Subject = stSubject
.Body = stMsg
.SaveMessageOnSend = True
.PostedDate = Now()
.SEND 0, vaRecipient
End With
'Release objects from memory.
Set noDocument = Nothing
Set noDatabase = Nothing
Set noSession = Nothing

'Return to Excel.
AppActivate "Microsoft Excel"

MsgBox "The e-mail has successfully been created and sent!",
vbInformation, "PARCELFORCE"
Exit Sub

End Sub