0

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:
enter image description here

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
Community
  • 1
  • 1
Tariq
  • 101
  • 1
  • 1
  • 10
  • What's your problem? – Tarik Aug 15 '20 at 10:09
  • 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
  • 1
    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
  • @Tarik no it's not HTML format – Tariq Aug 15 '20 at 10:35
  • Have a look at @FaneDuru's answer – Super Symmetry Aug 15 '20 at 10:41

1 Answers1

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
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