troubleshooting Question

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

Avatar of jumbo_fr
jumbo_fr asked on
Lotus IBM
3 Comments2 Solutions1231 ViewsLast Modified:
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


'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")

'Application.Visible = True

'création d'un nouveau classeur excel (temporaire)
    '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)
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
Kill "C:\Users\TEMP.xls"

Application.ScreenUpdating = True
Application.DisplayAlerts = False

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

End Sub

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros