Mail par vba

Le
Jacquouille Le Gaulois
Bonjour,

J'utilise un fichier de Pierre Wautier avec la macro ci-dessous et je
voudrais l'ameliorer mais je n y arrive pas.
1-Je voudrais que seul les lignes avec des donnees en colonne A soient
selectionnees (dans la plage A1:AI48)

2-Je voudrais que ce nouveau classeur soit envoye par mail à une
adresse defini dans ma feuille "Parametre" en cellule "C15"
3-J aimerais que le classeur en piece jointe porte le nom de la feuille
exporte et non pas "ClasseurX.xls"
est ce possible?
Merci


Sub transfert_réseau_DSIP()
'
ActiveSheet.Unprotect
Application.GoTo Reference:="R1C1"
Range("A1:AI48").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:lse
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:lse
ActiveWindow.SmallScroll Down:=-21
Columns("A:A").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 3.44
Columns("A:A").ColumnWidth = 4.44
Columns("B:B").EntireColumn.AutoFit
Application.CutCopyMode = False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:lse
Columns("C:AI").Select
Selection.ColumnWidth = 2.3
Range("A1").Select
With ActiveWindow
.DisplayGridlines = False
.DisplayZeros = False
End With
ActiveWindow.SmallScroll Down:=-6
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.787401575)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.4921259845)
.FooterMargin = Application.InchesToPoints(0.4921259845)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.79)
.RightMargin = Application.InchesToPoints(0.79)
.TopMargin = Application.InchesToPoints(0.98)
.BottomMargin = Application.InchesToPoints(0.53)
.HeaderMargin = Application.InchesToPoints(0.49)
.FooterMargin = Application.InchesToPoints(0.49)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.79)
.RightMargin = Application.InchesToPoints(0.79)
.TopMargin = Application.InchesToPoints(0.54)
.BottomMargin = Application.InchesToPoints(0.53)
.HeaderMargin = Application.InchesToPoints(0.49)
.FooterMargin = Application.InchesToPoints(0.49)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintPreview
Application.Dialogs(xlDialogSendMail).Show
ActiveWindow.Close
ActiveWindow.SmallScroll Down:=-27
Range("C5").Select
ActiveSheet.Protect DrawingObjects:lse, Contents:=True,
Scenarios:lse
End Sub

--
("|`-''-/").___..--''"`-._
`6_ 6 ) `-. ( ).`-.__.`)
(_Y_.)' ._ ) `._ `. ``-..-'
_..`--'_..-_/ /--'_.' ,'
(il),-'' (li),' ((!.-'
Vos réponses
Gagnez chaque mois un abonnement Premium avec GNT : Inscrivez-vous !
Trier par : date / pertinence
DJ9B
Le #363129
Bonjour "Jacquouille Le Gaulois" !

Je pense que l'instruction "Application.Dialogs(xlDialogSendMail).Show" ne
permet pas cette souplesse et t'enregistrera automatiquement le fichier en
entier en tant que pièce jointe.

--
@ tantôt !
DJ9B
email : http://www.cerbermail.com/?avOmkXbR4h
Site : http://www.dj9b.fr.st


"Jacquouille Le Gaulois" de news:
Bonjour,

J'utilise un fichier de Pierre Wautier avec la macro ci-dessous et je
voudrais l'ameliorer mais je n y arrive pas.
1-Je voudrais que seul les lignes avec des donnees en colonne A soient
selectionnees (dans la plage A1:AI48)

2-Je voudrais que ce nouveau classeur soit envoye par mail à une
adresse defini dans ma feuille "Parametre" en cellule "C15"
3-J aimerais que le classeur en piece jointe porte le nom de la feuille
exporte et non pas "ClasseurX.xls"
est ce possible?
Merci


Sub transfert_réseau_DSIP()
'
ActiveSheet.Unprotect
Application.GoTo Reference:="R1C1"
Range("A1:AI48").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Add
Range("A1").Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:úlse
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:úlse
ActiveWindow.SmallScroll Down:=-21
Columns("A:A").EntireColumn.AutoFit
Columns("A:A").ColumnWidth = 3.44
Columns("A:A").ColumnWidth = 4.44
Columns("B:B").EntireColumn.AutoFit
Application.CutCopyMode = False
Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:úlse
Columns("C:AI").Select
Selection.ColumnWidth = 2.3
Range("A1").Select
With ActiveWindow
.DisplayGridlines = False
.DisplayZeros = False
End With
ActiveWindow.SmallScroll Down:=-6
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.787401575)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.4921259845)
.FooterMargin = Application.InchesToPoints(0.4921259845)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.79)
.RightMargin = Application.InchesToPoints(0.79)
.TopMargin = Application.InchesToPoints(0.98)
.BottomMargin = Application.InchesToPoints(0.53)
.HeaderMargin = Application.InchesToPoints(0.49)
.FooterMargin = Application.InchesToPoints(0.49)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.79)
.RightMargin = Application.InchesToPoints(0.79)
.TopMargin = Application.InchesToPoints(0.54)
.BottomMargin = Application.InchesToPoints(0.53)
.HeaderMargin = Application.InchesToPoints(0.49)
.FooterMargin = Application.InchesToPoints(0.49)
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ActiveWindow.SelectedSheets.PrintPreview
Application.Dialogs(xlDialogSendMail).Show
ActiveWindow.Close
ActiveWindow.SmallScroll Down:=-27
Range("C5").Select
ActiveSheet.Protect DrawingObjects:úlse, Contents:=True,
Scenarios:úlse
End Sub

--
("|`-''-/").___..--''"`-._
`6_ 6 ) `-. ( ).`-.__.`)
(_Y_.)' ._ ) `._ `. ``-..-'
_..`--'_..-_/ /--'_.' ,'
(il),-'' (li),' ((!.-'



Publicité
Poster une réponse
Anonyme