1

I'm working to send Emails to each student containing ( student name and his marks ) from excel sheet as shown below

enter image description here

enter image description here

Everything working fine, But when the student name is in Arabic char. the name shows as ( ???? ) as you can see below

enter image description here

I changed the setting for local system to Arabic, but still, get the same problem.

Any advice?

Randomize
  • 8,651
  • 18
  • 78
  • 133
Tariq
  • 101
  • 1
  • 1
  • 10
  • Having the code being used to generate the email `HTMLBody` would be more helpful than screenshots of the described output. This is an encoding issue, you need the `HTMLBody` string content to be *unicode*, but unless you explicitly ask for it then what you get is ANSI encoding, which doesn't cover Arabic (or Chinese, Japanese, and Korean character sets). [`StrConv`](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/strconv-function) should help. – Mathieu Guindon Aug 15 '20 at 15:56
  • Your question needs more information. i.e. it needs to be self-contained. You shouldn't assume that people know about your other question here https://stackoverflow.com/questions/63424811/advice-to-send-emails-to-each-student-using-vba/63426717#comment112155752_63426717. You should at least add a link to the other question. Anyway as I saw it earlier I posted an answer below. – Super Symmetry Aug 15 '20 at 17:05
  • @SuperSymmetry Thanks for your support .. I'm sorry for this .. – Tariq Aug 15 '20 at 18:13

1 Answers1

0

You need to set htmlBody and use utf-8 character set.

Use the following function to make a simple transformation of a text string into html string.

Function StringToHTML(sStr As String) As String
    sStr = Replace(sStr, Chr(10), "<br/>")
    sStr = Replace(sStr, Chr(13), "<br/>")
    sStr = Replace(sStr, Chr(11), "<br/>")
    StringToHTML = "<!doctype html><html lang=""en""><body><p>"
    StringToHTML = StringToHTML & sStr
    StringToHTML = StringToHTML & "</p></body></html>"
End Function

With reference to this, you need to replace the line objEmail.TextBody = mailBody with the following two lines

objEmail.htmlBody = StringToHTML(mailBody)
objEmail.HtmlBodyPart.Charset = "utf-8"

If you face further problems (e.g. the email subject contains arabic chars but doesn't display properly) try adding these two lines

objEmail.TextBodyPart.Charset = "utf-8"
objEmail.BodyPart.Charset = "utf-8"

Edit (following comment)

Your full code should be like this

Sub SendMail()
    Dim objEmail
    Dim mailBody as String

    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 = "email@some.com"
     mailpassword = "password"
     ''''''''
     
     Dim n As Integer
     n = Application.WorksheetFunction.CountA(Range("c:c"))
     For i = 2 To n
     
     mailto = Range("c" & i).Value
     mailSubject = Range("e" & i).Value
     mailBody = "Hi " & Range("b" & i) & "," & vbCrLf & vbCrLf & _
               "Below you can find your marks:" & vbCrLf & vbCrLf & _
               "Math: - " & Range("F" & i) & vbCrLf & _
               "Network: - " & Range("G" & i) & vbCrLf & _
               "Physics: - " & Range("H" & i) & vbCrLf & _
               "Antenna: - " & Range("I" & i)

    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.htmlBody = StringToHTML(mailBody)
    objEmail.HtmlBodyPart.Charset = "utf-8"
    objEmail.Send

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

Function StringToHTML(sStr As String) As String
    sStr = Replace(sStr, Chr(10), "<br/>")
    sStr = Replace(sStr, Chr(13), "<br/>")
    sStr = Replace(sStr, Chr(11), "<br/>")
    StringToHTML = "<!doctype html><html lang=""en""><body><p>"
    StringToHTML = StringToHTML & sStr
    StringToHTML = StringToHTML & "</p></body></html>"
End Function
Super Symmetry
  • 2,837
  • 1
  • 6
  • 17