'Sending Email through Excel VBA Macro From Gmail

I've spent the last couple few weeks to try to figure this out why below goven is not working, I've managed to stop all the errors, however the email doesn't show up in my inbox . I've tried to change everything up and still it doesn't show up. The main purpose is to send the data with his or her data only to an gmail,

Sub send_email_via_Gmail()

Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet1")

Dim myMail As CDO.Message
Set myMail = New CDO.Message

Dim i As Integer
Dim j As Integer
Dim last_row As Integer

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smptauthenticate") = 1

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 1465

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "[email protected]"

myMail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "123456"


last_row = Application.WorksheetFunction.CountA(sh.Range("X:X"))

For i = 2 To last_row

    myMail.To = sh.Range("Y" & i).Value
    myMail.CC = sh.Range("Z" & i).Value & " ; " & sh.Range("AA" & i).Value & " ; " & sh.Range("AB" & i).Value
    myMail.Subject = "Order Dispatch Details"
        myMail.htmlbody = "<html><body><br>Dear Concern, Please find below dispatch status<br><br><style>table, th, td { border: 1px solid black; border-collapse: collapse;}</style>" & _
                    "<table><tr><td><b>" & sh.Range("A1").Value & "</b></td><td><b>" & sh.Range("B1").Value & "</b></td><td><b>" & sh.Range("C1").Value & "</b></td><td><b>" & sh.Range("D1").Value & "</b></td><td><b>" & sh.Range("E1").Value & "</b></td><td><b>" & sh.Range("F1").Value & "</b></td><td><b>" & sh.Range("G1").Value & "</b></td><td><b>" & sh.Range("H1").Value & "</b></td><td><b>" & sh.Range("I1").Value & "</b></td><td><b>" & sh.Range("J1").Value & "</b></td><td><b>" & sh.Range("K1").Value & "</b></td><td><b>" & sh.Range("L1").Value & "</b></td><td><b>" & sh.Range("M1").Value & "</b></td><td><b>" & sh.Range("N1").Value & "</b></td><td><b>" & sh.Range("O1").Value & "</b></td><td><b>" & sh.Range("P1").Value & "</b></td><td><b>" & sh.Range("Q1").Value & "</b></td><td><b>" & sh.Range("R1").Value & "</b></td><td><b>" & sh.Range("S1").Value & "</b></td><td><b>" & sh.Range("T1").Value & "</b></td><td><b>" & sh.Range("U1").Value & "</b></td><td><b>" & sh.Range("V1").Value & _
"</b></td><td><b>" & sh.Range("W1").Value & "</b></td><td><b>" & sh.Range("X1").Value & _
                        "<tr><td>" & sh.Range("A" & i).Value & "</td><td>" & sh.Range("B" & i).Value & "</td><td>" & sh.Range("C" & i).Value & "</td><td>" & sh.Range("D" & i).Value & "</td><td>" & sh.Range("E" & i).Value & "</td><td>" & sh.Range("F" & i).Value & "</td><td>" & sh.Range("G" & i).Value & "</td><td>" & sh.Range("H" & i).Value & "</td><td>" & sh.Range("I" & i).Value & "</td><td>" & sh.Range("J" & i).Value & "</td><td>" & sh.Range("K" & i).Value & "</td><td>" & sh.Range("L" & i).Value & "</td><td>" & sh.Range("M" & i).Value & "</td><td>" & sh.Range("N" & i).Value & "</td><td>" & sh.Range("O" & i).Value & "</td><td>" & sh.Range("P" & i).Value & "</td><td>" & sh.Range("Q" & i).Value & "</td><td>" & sh.Range("R" & i).Value & "</td><td>" & sh.Range("S" & i).Value & "</td><td>" & sh.Range("T" & i).Value & "</td><td>" & sh.Range("U" & i).Value & "</td><td>" & sh.Range("V" & i).Value & "</td><td>" & sh.Range("W" & i).Value & "</td><td>" & sh.Range("X" & i).Value & "</td><td>"
    For j = (i + 1) To last_row
        If sh.Range("Y" & i).Value = sh.Range("Y" & j).Value Then
            
            myMail.htmlbody = myMail.htmlbody & "<tr><td>" & sh.Range("A" & j).Value & "</td><td>" & sh.Range("B" & j).Value & "</td><td>" & sh.Range("C" & j).Value & "</td><td>" & sh.Range("D" & j).Value & "</td><td>" & sh.Range("E" & j).Value & "</td><td>" & sh.Range("F" & j).Value & "</td><td>" & sh.Range("G" & j).Value & "</td><td>" & sh.Range("H" & j).Value & "</td><td>" & sh.Range("I" & j).Value & "</td><td>" & sh.Range("J" & j).Value & "</td><td>" & sh.Range("K" & j).Value & "</td><td>" & sh.Range("L" & j).Value & "</td><td>" & sh.Range("M" & j).Value & "</td><td>" & sh.Range("N" & j).Value & "</td><td>" & sh.Range("O" & j).Value & "</td><td>" & sh.Range("P" & j).Value & "</td><td>" & sh.Range("Q" & j).Value & "</td><td>" & sh.Range("R" & j).Value & "</td><td>" & sh.Range("S" & j).Value & "</td><td>" & sh.Range("T" & j).Value & "</td><td>" & sh.Range("U" & j).Value & "</td><td>" & sh.Range("V" & j).Value & "</td><td>" & sh.Range("W" & j).Value & "</td><td>" & sh.Range("X" & j).Value & "</td><td>"
        i = j
        End If
    Next j
    
  myMail.htmlbody = myMail.htmlbody & "</table></body><br>Regards,<br>Vikas Karn,<br> Mobile no: +91-83 7795 2790</html>"
    
    myMail.Send

Next i

MsgBox "Sent"
Set myMail = Nothing
End Sub

excel vba

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source

'Sending Email through Excel VBA Macro From Gmail

Sources

Related Questions