Link to home
Start Free TrialLog in
Avatar of jumbo_fr
jumbo_fr

asked on

Modify table margins for an imported table in Lotus mail (by Lotus script)

Hello everybody,
I use a lotus script which imports an excel range (excel file) into the body of  a Lotus Notes mail, and then sends the mail automatically. My problem is that, when impoting the range, lotus creates a similar table (as the excel range) but with very solid margins (quite ugly), and also adds an useless row at the beginning of the table.
I would like to be able to change the layout of the table before sending the mail. I tried using the NotesRichTextNavigator and NotesRichTextTable classes, but as I'm new in this, I could find a working solution. Could anyone give me some ideas  pls?!
This is my code:

Sub SendMailbyExcel()

Dim NotesDoc As Object, noWS As Object, uiDoc As Object, nortf As Object
Dim db As Object, noDatabase As Object, session As Object
Dim noWord As Object, myDoc As Object
Dim server, MailFile, user, usersig As String
Dim stSubject As Variant, stTitle As String
Dim bodyMsg As Variant
Dim rnBody As Range
Dim Data As New DataObject

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Workbooks("MacroSendMailFinal.xls").Activate

'le sujet du mail
stSubject = "Test Mail: Sending excel range as Rich Text, by Excel."

'le texte du mail
bodyMsg = "!---- This Excel Range is sent by mail in Rich Text Format, using an intermediate Excel File ----! " & Chr$(13) & Chr$(13)

'la plage excel à insérer dans le mail
Set rnBody = Range("Email3")
rnBody.Copy

'Application.Visible = True

'création d'un nouveau classeur excel (temporaire)
Workbooks.Add
    Range("A1").Select
    'coller la plage excel à partir de la cellule A1
    Selection.PasteSpecial Paste:=xlValues
    Selection.PasteSpecial Paste:=xlPasteFormats
    'enregistrement du nouveau fichier (dans le répértoire spécifié par l'utilisateur; ici C:\SG\ )
    ActiveWorkbook.SaveAs Filename:="C:\Users\Temp.xls", FileFormat:= _
        xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False

'connexion à la session Lotus et à la BD des mails
Set session = CreateObject("Notes.NotesSession")
Set noWS = CreateObject("Notes.NotesUIWorkspace")
'Set noDatabase = noSession.GETDATABASE("", "")

'récupération des infos liées au serveur Lotus
user = session.UserName
usersig = session.COMMONUSERNAME
server = session.GetEnvironmentString("MailServer", True)
MailFile = session.GetEnvironmentString("MailFile", True)
       
'connexion à Lotus Notes
Set db = session.GETDATABASE(server, MailFile)
Set noDatabase = noWS.CurrentDatabase

'création d'un nouveau document
Set NotesDoc = db.CreateDocument
Set nortf = NotesDoc.CreateRichTextItem("Body")
'initialisation des champs du mail
NotesDoc.Subject = stSubject
NotesDoc.SendTo = user
With NotesDoc
        .ComputeWithForm False, False
        .SaveMessageOnSend = True
        .Save True, False, True
        '.SaveOptions = "0"
End With

Set uiDoc = noWS.EDITDOCUMENT(False, NotesDoc)

'selection du corps du mail
Call uiDoc.GOTOFIELD("Body")
Call uiDoc.FIELDAPPENDTEXT("Body", bodyMsg & Chr$(13))

'importation du fichier excel temporaire
Call uiDoc.Import("Microsoft Excel", "c:\Users\TEMP.xls")

'Dim nortf2 As NotesRichTextItem
Dim noNav As Object
Dim noTab As Object
Dim noElem As Object

Set nortf = NotesDoc.GetFirstItem("Body")
Set noNav = nortf.CreateNavigator
If Not noNav.FindFirstElement(RTELEM_TYPE_TABLE) Then
   MsgBox "Body item does not contain a table "

End If

'Call noNav.GetFirstElement(RTELEM_TYPE_TABLE)
Set noTab = noNav.GetLastElement(RTELEM_TYPE_TABLE)
noTab.Style = TABLESTYLE_SOLID
Call noNav.RemoveRow(1)
'envoie du mail
Call uiDoc.Send

'fermeture de la fenêtre du mail dans Lotus
Call uiDoc.Close

'destruction des objets créés
Set NotesDoc = Nothing
Set noDatabase = Nothing
Set db = Nothing
Set noWS = Nothing
Set session = Nothing
Set noWord = Nothing

'suppression des données du Clipboard

AppActivate "Microsoft Excel"
Application.CutCopyMode = False

'suppression du fichier excel temporaire
Workbooks("TEMP.xls").Saved = True
Workbooks("TEMP.xls").Close
Kill "C:\Users\TEMP.xls"

Application.ScreenUpdating = True
Application.DisplayAlerts = False

MsgBox "E-mail has been sent !", vbInformation

End Sub
ASKER CERTIFIED SOLUTION
Avatar of Bill-Hanson
Bill-Hanson
Flag of United States of America 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
Avatar of SysExpert
I would also consider using copy and Paste via COM/ OLE using paste special, rather than import to see if the results look any better.

I hope this helps !
SOLUTION
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