CODEWITHSUNDEEP
vbaexcelemail
MopMop
MopMop

Reputation: 119

Excel VBA email cell value

I have the following code where the adress doen't work... I wonder how I can select the e-mail adress value out of a cell because with cells(1,1).value it doesn't seem to work in my case.

Sub email_missing_forecast()

Application.ScreenUpdating = False

'Déclaration des variables
derniereligne = Range("B").End(xlUp).Row
Adresse = Cells(i, "D").Value
Adresse2 = Cells(i, "E").Value
Adresse3 = Cells(i, "F").Value
Project_number = Cells(1, 2).Value
Project_name = Cells(2, 2).Value
Project_due = Cells(3, 2).Value
Lien = Cells(4, 2).Value


Dim Objoutlook As New Outlook.Application
Dim Objectmail

'Condition
For i = 6 To derniereligne
If Cells(i, "B").Value = "No" Then
    Set Objoutlook = New Outlook.Application
    Set Objectmail = Outlook.createitem(olmailitem)
        With Objectmail
        .To = Adresse & ";" & Adresse2 & ";" & Adresse3
        .Subject = "Bobbi Brown| " & Project_number & " " & Project_name & "| Forecast due " & Project_due
        .Body = "Dear All, " & Chr(10) & "I kindly remind you that forecasts for program " & Project_number & " " & Project_name & " are due " & Project_due & "." & Chr(10) & "Please enter your forecast " & "<a href=lien>here.</a>" & Chr(10) & "Best Regards," & Chr(10) & "Christian Chen"
        .Send
End with
End If
Next i
Application.ScreenUpdating = True
MsgBox "Your e-mails have been sent successfully", , "FIY"
End Sub

Upvotes: 0

Views: 1701

Answers (1)

Veve
Veve

Reputation: 6758

You didn't set i before using it for your Adresse variables, moving the declaration of these variables in your loop should solve your problem:

Sub email_missing_forecast()

Application.ScreenUpdating = False

'Déclaration des variables
derniereligne = Range("B").End(xlUp).Row
Project_number = Cells(1, 2).Value
Project_name = Cells(2, 2).Value
Project_due = Cells(3, 2).Value
Lien = Cells(4, 2).Value


Dim Objoutlook As New Outlook.Application
Dim Objectmail

'Condition
For i = 6 To derniereligne
    Adresse = Cells(i, "D").Value
    Adresse2 = Cells(i, "E").Value
    Adresse3 = Cells(i, "F").Value

    If Cells(i, "B").Value = "No" Then
        Set Objoutlook = New Outlook.Application
        Set Objectmail = Outlook.createitem(olmailitem)
        With Objectmail
            .To = Adresse & ";" & Adresse2 & ";" & Adresse3
            .Subject = "Bobbi Brown| " & Project_number & " " & Project_name & "| Forecast due " & Project_due
            .Body = "Dear All, " & Chr(10) & "I kindly remind you that forecasts for program " & Project_number & " " & Project_name & " are due " & Project_due & "." & Chr(10) & "Please enter your forecast " & "<a href=lien>here.</a>" & Chr(10) & "Best Regards," & Chr(10) & "Christian Chen"
            .Send
        End with
    End If
Next i
Application.ScreenUpdating = True
MsgBox "Your e-mails have been sent successfully", , "FIY"
End Sub

Upvotes: 2

Related Questions