We help IT Professionals succeed at work.

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

jumbo_fr
jumbo_fr asked
on
1,225 Views
Last Modified: 2013-12-18
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
Comment
Watch Question

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2007

Commented:
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 !
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.