QUVi9F7N

1Private Sub CommandButton1_Click()
2Dim myMail As CDO.Message
3Set myMail = New CDO.Message
4Dim Login_EmailAddress, Login_EmailPassword As String, SMTPServer As String
5Dim ServerPort As Integer
6Dim To_Email, CC_Email As String, BCC_Email As String, Email_Subject As String, Email_Body As String, Attachment_Path     As String
7Dim CustomerEmail As String
8Dim finalrow As Integer
9Dim i As Long
10Dim x As Long
11
12x = ThisWorkbook.Sheets("birthdaymail").Cells(Rows.Count, 7).End(xlUp).Row
13
14line1:
15For i = 2 To x
16    If Sheets("birthdaymail").Cells(i, 7).Value <> "" Then
17       CustomerEmail = Sheets("birthdaymail").Cells(i, 7).Value
18
19       '********************ACCOUNT DETAILS********************************************************************************
20       SMTPServer = "smtp.gmail.com"
21       ServerPort = 465
22       Login_EmailAddress = "*****@gmail.com"
23       Login_EmailPassword = "#######"
24
25       '*********************EMAIL DETAILS******************************************
26       To_Email = CustomerEmail
27       CC_Email = ""                   '"deepak.lohia@gmail.com"
28       BCC_Email = ""                  '"deepak.lohia@gmail.com"
29       Attachment_Path = ""            '"C:Usersdpk-pcDesktopshortcutphoto.jpg"
30       Email_Subject = "áãé÷ä àçú ùúééí ùìåù àøáò"
31       Email_Body = "æä îééì áãé÷ä 2/2/2018"
32       '****************************************************************************************************
33
34       With myMail.Configuration.Fields
35           .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
36           .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
37           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = SMTPServer
38           .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = ServerPort
39           .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
40           .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = Login_EmailAddress
41           .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = Login_EmailPassword
42           .Update
43       End With
44
45       With myMail
46           .From = Login_EmailAddress
47           .Subject = Email_Subject
48           .To = To_Email
49           .CC = CC_Email
50           .BCC = BCC_Email
51           .TextBody = Email_Subject
52           If Attachment_Path <> "" Then .AddAttachment Attachment_Path
53           If To_Email = ThisWorkbook.Sheets("birthdaymail").Cells(1, 7) Then GoTo line2
54           If To_Email = "" Then GoTo line1
55           .Send
56       End With
57
58       If To_Email = "" Then GoTo line1
59       On Error Resume Next
60
61line2:
62       If Err.Number <> 0 Then
63         MsgBox Err.Description, vbCritical
64       Else
65         MsgBox ("Mail has been sent"), vbInformation
66       End If
67    End If
68Next i
69End Sub