VBA Mail Pièce jointe, définir la source ?

[ipotez]

Bonjour,
je bricole ce code et je ne parviens pas à formuler ma requête, je voudrais qu'au lieu de prendre en pièce jointe la feuille "1er semestreDD", il me prenne un fichier qui se trouve sur C:/
Comment l'écrire ?

Merci pour vos conseils

Sub Mail()
'Working in 97-2007
    Dim FileExtStr As String
    Dim FileFormatNum As Long
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    Dim TempFilePath As String
    Dim TempFileName As String

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    Set Sourcewb = ActiveWorkbook

    'Copy the sheet to a new workbook
    ThisWorkbook.Sheets("1er SemestreDD").Copy
    Set Destwb = ActiveWorkbook

    'Determine the Excel version and file extension/format
    With Destwb
        If Val(Application.Version) < 12 Then
            'You use Excel 97-2003
            FileExtStr = ".xls": FileFormatNum = -4143
        Else
            'You use Excel 2007, we exit the sub when your answer is
            'NO in the security dialog that you only see  when you copy
            'an sheet from a xlsm file with macro's disabled.
            If Sourcewb.Name = .Name Then
                With Application
                    .ScreenUpdating = True
                    .EnableEvents = True
                End With
                MsgBox "Your answer is NO in the security dialog"
                Exit Sub
            Else
                Select Case Sourcewb.FileFormat
                Case 51: FileExtStr = ".xlsx": FileFormatNum = 51
                Case 52:
                    If .HasVBProject Then
                        FileExtStr = ".xlsm": FileFormatNum = 52
                    Else
                        FileExtStr = ".xlsx": FileFormatNum = 51
                    End If
                Case 56: FileExtStr = ".xls": FileFormatNum = 56
                Case Else: FileExtStr = ".xlsb": FileFormatNum = 50
                End Select
            End If
        End If
    End With

     'Change all cells in the worksheet to values if you want
    With Destwb.Sheets(1).UsedRange
    .Cells.Copy
    .Cells.PasteSpecial xlPasteValues
    .Cells(1).Select
    End With
    '    Application.CutCopyMode = False

    'Save the new workbook/Mail it/Delete it
    TempFilePath = Environ$("temp") & "\"
    TempFileName = "Planning Formateur - " _
                 & Format(Now, "dd-mmm-yyyy")

    With Destwb
        .SaveAs TempFilePath & TempFileName & FileExtStr, _
                FileFormat:=FileFormatNum
        On Error Resume Next
        .SendMail ThisWorkbook.Sheets("Tables").Range("b17").Value, _
                  ThisWorkbook.Sheets("Tables").Range("e15").Value
        On Error GoTo 0
        .Close SaveChanges:=False
    End With

    'Delete the file you have send
    Kill TempFilePath & TempFileName & FileExtStr

    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub

 

[Manu62]

Re : VBA Mail Pièce jointe, définir la source ?

Salut,

Ci dessous mon code que j'utilise pour ta demande.


Extrait du code pour piece jointe...

Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Set OutlookApp = New Outlook.Application
Set MItem = OutlookApp.CreateItem(olMailItem)
Set myAttachments = MItem.Attachments
myAttachments.Add Range("R8").Value 'Pas de signe "=" apres Add (en r8 c'est le chemin du fichier)
With MItem
.To = [R9]
.Subject = "Bon de Commande "
.Body = "Bonjour, ,,,,,, "
.Display
End With
End Sub

 

[ipotez]

Re : VBA Mail Pièce jointe, définir la source ?

salut manu, merci pr l'aide.

J'ai copié ton code et modifié le chemin, par contre il bloque dès la lecture de :
Dim OutlookApp As Outlook.Application

Ya t il un addin a ajouter ? Prends t il le logiciel de messagerie par défaut ?
Merci
pour info, j'utilise plutôt Windows Live Mail

Sub mail()
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.MailItem
Dim myAttachments As Outlook.Attachments
Set OutlookApp = New Outlook.Application
Set MItem = OutlookApp.CreateItem(olMailItem)
Set myAttachments = MItem.Attachments
myAttachments.Add Range(C:\TEST.PDF).Value 'Pas de signe "=" apres Add (en r8 c'est le chemin du fichier)
With MItem
.To = [R9]
.Subject = "Bon de Commande "
.Body = "Bonjour, ,,,,,, "
.Display
End With
End Sub