Send email with text based on cell values to each student using VBA

Question

I'm trying to send an email to each student (that contain the student name and marks) using VBA.

I have an Excel sheet as below:

I need to send an email to each student with email body text as below:

Hi " Student name "

Below you can found your marks:-

Math :- " his mark"
Network :- "his mark"
Physics :- "his mark"
Antenna :- " his mark"

How do I send this text to each student in the mailBody section?

Sub SendMail()
    Dim objEmail

    Const cdoSendUsingPort = 2  ' Send the message using SMTP
    Const cdoBasicAuth = 1      ' Clear-text authentication
    Const cdoTimeout = 100      ' Timeout for SMTP in seconds

    mailServer = "smtp.gmail.com"
    SMTPport = 465     '25 'SMTPport = 465
    mailusername = Range("j9").Value
    mailpassword = Range("j10").Value
    ''''''''
    Dim n As Integer
    n = Application.WorksheetFunction.CountA(Range("c:c")) - 1
    For i = 1 To n
     
        mailto = Range("c1").Offset(i, 0).Value
        mailSubject = Range("e1").Offset(i, 0).Value

        'mailBody = ??** What i should to set ?

        Set objEmail = CreateObject("CDO.Message")
        Set objConf = objEmail.Configuration
        Set objFlds = objConf.Fields

        With objFlds
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = cdoSendUsingPort
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = mailServer
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = SMTPport
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = cdoTimeout
            .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = cdoBasicAuth
            .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = mailusername
            .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = mailpassword
            .Update
        End With

        objEmail.To = mailto
        objEmail.From = mailusername
        objEmail.subject = mailSubject
        objEmail.TextBody = mailBody
        'objEmail.AddAttachment "C:\report.pdf"
        objEmail.CC = Range("d1").Offset(i, 0).Value
        objEmail.BCC = Range("k1").Offset(i, 0).Value
        objEmail.Send

        Set objFlds = Nothing
        Set objConf = Nothing
        Set objEmail = Nothing
    Next i
End Sub

My problem how i can build the structure of the mail body as i mentions above — Tariq, Aug 15 '20 at 10:14
The structure above is just a string. If the body format is HTML, then add the br tag at the end of each line. — Tarik, Aug 15 '20 at 10:21
Use string concatenation. In vba the concatenation operator is `&`. For example the first line of `mailBody` (followed by a blank line) could be set like this `mailBody = "Hello " & Range("b1").Offset(i, 0).Value & "," & Chr(10) & Chr(10)` — Super Symmetry, Aug 15 '20 at 10:22
@SuperSymmetry , Many thanks for your support .. The ( Math , Network ... etc ) are column name which is constant for all student, the variable will be the marks and student name ,,, plz can u support me with example, i'm new in this .. — Tariq, Aug 15 '20 at 10:34

FaneDuru · Accepted Answer · 2020-08-15T10:45:33.443

1

Try this approach, please:

 mailBody = "Hy " & Range("B" & i) & "," & vbCrLf & vbCrLf & _
           "Below you can find your marks:" & vbCrLf & vbCrLf & _
           "Network: - " & Range("G" & i) & vbCrLf & _
           "Physics: - " & Range("H" & i) & vbCrLf & _
           "Antenna: - " & Range("I" & i)

And start the iteration from 2:

 For i = 2 To n

Then no need to any Offset:

objEmail.CC = Range("d" & i).Value
objEmail.BCC = Range("k" & i).Value

edited Aug 15 '20 at 10:45

answered Aug 15 '20 at 10:32

FaneDuru

38,298
4
19
27

I think you should change `i` to `i+1` – Super Symmetry Aug 15 '20 at 10:40
2

@Super Symmetry: In his way of writing the code, yes... I also tried suggesting (after your comment) to him to start the iteration from `2`. Thanks, anyhow! You were right. – FaneDuru Aug 15 '20 at 10:47
1

@Tarik: Doesn't my code answer your question? If now, where is the problem? – FaneDuru Aug 15 '20 at 12:33
@FaneDuru , Thanks for your support .. when i sent the message the text email body show " False" word only .. no student name or marks show in the email body – Tariq Aug 15 '20 at 13:36
What? How a string to return `False`? You maybe make a wrong comparing... Only in such a way it will return `False'... – FaneDuru Aug 15 '20 at 13:51

Send email with text based on cell values to each student using VBA

1 Answers1

Linked