Link to home
Start Free TrialLog in
Avatar of odrion
odrion

asked on

Create attachment in Notes from Excel VBA

Hi all,
Currently I have code which works perfectly as far as adding part of excel sheet to the body of mail is concerned.

Instead of that I would like to add activeworksheet as attachment. Please have a look at code below.
Haw should I adjust code?




Sub paste_data_to_lotus()

Dim CN, UserName, MailFile As String
   
    Dim workspace As Object
    Set workspace = CreateObject("Notes.NotesUIWorkspace")
   
    'Getting/creating the name of the current users mail DB file
    'Note this will only work on a Single user install of Lotus Notes that has a
    'common name convention e.g. FirstInitalfirst seven of last name

    Dim Session As Object
    Set Session = CreateObject("Notes.NotesSession")
    CN = Session.COMMONUSERNAME
    UserName = LCase(Left(Left$(CN, 1) & Right$(CN, (Len(CN) - InStr(1, CN, " "))), 8)) & ".nsf"
    Set Session = Nothing
    MailFile = "mail\" & UserName

    'Enter your Domino Server name in the following ServerName spot

    Call workspace.OpenDatabase("myServer", MailFile)
   
    'Set your selection area on the worksheet (this is what will get pasted in your Notes Emial
   
   
   Dim varRange As Range
Set varRange = _
Application.InputBox("Select a range of cells:", Type:=8)

varRange.Select
   

    Selection.Copy
       
    Dim NotesUIDoc As Object
    Set NotesUIDoc = workspace.COMPOSEDOCUMENT("", "", "Memo")

    'Enter a valid user name in the Valid Username spot
    Call NotesUIDoc.FIELDSETTEXT("EnterSendTo", "Enter recipents")
       Call NotesUIDoc.FIELDSETTEXT("EnterCopyTo", "Enter recipents")
    'Using a Domino Group above makes more sense for multiple users
    Call NotesUIDoc.FIELDSETTEXT("Subject", ">>>>>> Enter subject of the mail <<<<<")
    NotesUIDoc.GOTOFIELD "Body"
    NotesUIDoc.Paste
   
   
   
    'NotesUIDoc.SEND
   ' NotesUIDoc.Close

    'Clean it up
    Set NoteUIDoc = Nothing
    Set workspace = Nothing
 
    Range("A1").Select
    Application.CutCopyMode = False


End Sub


Odrion
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

If you want it to be an attachment, you need to save the Excel-sheet in a file, then you can attach that file to the mail-document.

http:Q_21503598.html "Attaching an Excel Workbook"
Avatar of odrion
odrion

ASKER

Hello sejf bosman,

Thanks for link. I used it, but I would like to know one more thing. How to activate Lotus notes from Excel VBA?
I guess it should be one line of command.

Can you also help me with this?
Thanks in advance,

Odrion
ASKER CERTIFIED SOLUTION
Avatar of Sjef Bosman
Sjef Bosman
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial