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("MacroSendMailFi nal.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.x ls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
'connexion à la session Lotus et à la BD des mails
Set session = CreateObject("Notes.NotesS ession")
Set noWS = CreateObject("Notes.NotesU IWorkspace ")
'Set noDatabase = noSession.GETDATABASE("", "")
'récupération des infos liées au serveur Lotus
user = session.UserName
usersig = session.COMMONUSERNAME
server = session.GetEnvironmentStri ng("MailSe rver", True)
MailFile = session.GetEnvironmentStri ng("MailFi le", 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.CreateRichTextIte m("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("Bod y", 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("Bod y")
Set noNav = nortf.CreateNavigator
If Not noNav.FindFirstElement(RTE LEM_TYPE_T ABLE) Then
MsgBox "Body item does not contain a table "
End If
'Call noNav.GetFirstElement(RTEL EM_TYPE_TA BLE)
Set noTab = noNav.GetLastElement(RTELE M_TYPE_TAB LE)
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").Save d = True
Workbooks("TEMP.xls").Clos e
Kill "C:\Users\TEMP.xls"
Application.ScreenUpdating = True
Application.DisplayAlerts = False
MsgBox "E-mail has been sent !", vbInformation
End Sub
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
Application.DisplayAlerts = False
Workbooks("MacroSendMailFi
'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.x
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False
, CreateBackup:=False
'connexion à la session Lotus et à la BD des mails
Set session = CreateObject("Notes.NotesS
Set noWS = CreateObject("Notes.NotesU
'Set noDatabase = noSession.GETDATABASE("", "")
'récupération des infos liées au serveur Lotus
user = session.UserName
usersig = session.COMMONUSERNAME
server = session.GetEnvironmentStri
MailFile = session.GetEnvironmentStri
'connexion à Lotus Notes
Set db = session.GETDATABASE(server
Set noDatabase = noWS.CurrentDatabase
'création d'un nouveau document
Set NotesDoc = db.CreateDocument
Set nortf = NotesDoc.CreateRichTextIte
'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("Bod
'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("Bod
Set noNav = nortf.CreateNavigator
If Not noNav.FindFirstElement(RTE
MsgBox "Body item does not contain a table "
End If
'Call noNav.GetFirstElement(RTEL
Set noTab = noNav.GetLastElement(RTELE
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").Save
Workbooks("TEMP.xls").Clos
Kill "C:\Users\TEMP.xls"
Application.ScreenUpdating
Application.DisplayAlerts = False
MsgBox "E-mail has been sent !", vbInformation
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I hope this helps !