Bonjour la Communauté,

Je voudrais envoyer un mail via excel avec comme attachment (en C:\XXXX\A.pdf) un fichier correspondant à la valeur se trouvant en colonne A de ma feuille excel.

Dans ce cas-ci j'ai essayé

Code : Sélectionner tout - Visualiser dans une fenêtre à part
 .Attachments.Add "C:\xxx\" & Range("A" & cROw) & ".pdf"
En somme je parviens pas à récupérer la valeur réelle de A de ma feuille excel.

Merci d'avance

Habiler


Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
 Option Explicit
 
Dim titleName As String
Dim firstName As String
Dim lastName As String
Dim fullName As String
Dim clientEmail As String
Dim ccEmail As String
Dim bccEmail As String
Dim emailMessage As String
 Dim cROw As Long
 
Sub GenerateInfo()
 
    Dim WS As Worksheet
    Dim lrow As Long
    Dim cROw As Long
 
    Set WS = ActiveSheet
 
    With WS
        lrow = .Range("A" & .Rows.Count).End(xlUp).Row
        Application.ScreenUpdating = False
        For cROw = 2 To lrow
            If Not .Range("A" & cROw).Value = "" Then
 
                firstName = .Range("C" & cROw).Value
                clientEmail = .Range("E" & cROw).Value
 
                Call SendEmail(cROw)
 
                .Range("Y" & cROw).Value = "Yes"
                .Range("Y" & cROw).Font.Color = vbGreen
 
            Else
                .Range("Y" & cROw).Value = "No"
                .Range("Y" & cROw).Font.Color = vbRed
            End If
        Next cROw
    End With
 
    Application.ScreenUpdating = True
 
    MsgBox "Process completed!", vbInformation
 
End Sub
Sub SendEmail(cROw As Long)
 
    Dim outlookApp As Object
    Dim outlookMail As Object
    Dim sigString As String
    Dim Signature As String
    Dim insertPhoto As String
    Dim photoSize As String
 
    Set outlookApp = CreateObject("Outlook.Application")
    Set outlookMail = outlookApp.CreateItem(0)
 
 
    emailMessage = " Hello " & _
                    "<p>  <strong> xxxxxxxxx </strong>." & _
                    "<p>." & _
                    "<p>E" & _
                    "<p>"
 
 
    With outlookMail
        '.To = clientEmail
        .To = "moi@moi.com"
        .CC = ""
        .BCC = ""
        .Subject = "xxxxxxxxxxxxxxxxxxx"
        .BodyFormat = 2
        .Attachments.Add "C:\xxx\" & Range("A" & cROw) & ".pdf"
        Debug.Print cROw
        .HTMLBody = emailMessage
        .Importance = 2
        '.ReadReceiptRequested = True
        .Display
        '.Send
 
    End With
 
    Set outlookApp = Nothing
    Set outlookMail = Nothing
End Sub