1

I used the below coding earlier for some other type of mail but this time is not sending the mails.

Also not picking the email IDs and data given in Excel.
I have to send mails to different people with CC to their respective managers. I updated their Email IDs in Excel but the details updated in Excel are not taking and a draft mail is created without anything in To and CC.

Sub Send_Recertification_From_Excel()

Dim oXlWkBk As Excel.Workbook ' Excel Work Book Object
Dim oOLApp As Outlook.Application
Dim oOLMail As MailItem
Dim lRow As Long
Dim olMailItem
Dim sMailID As String
Dim sSalutation As String
Dim sName As String
Dim sDetails As String
Dim sSubject As String
Dim mailsSentString As String
Dim templateName As String
templateName = "C:\Users\m540797\Desktop\Recertification\Recertifications"
On Error GoTo Err_Trap

Set oXlWkBk = ActiveWorkbook
Set oOLApp = GetObject(, "Outlook.Application")
If oOLApp Is Nothing Then
    MsgBox "Please Open Outlook.."
    Exit Sub
End If

Dim i As Integer

For i = 6 To 50

    If Len(Trim(Sheet1.Cells(i, 1))) > 1 Then
        Set oOLMail = oOLApp.CreateItemFromTemplate(templateName)
        sMailID = Sheet1.Cells(i, 4)
        sSubject = "Recertification"
        
        With oOLMail
            .BodyFormat = olFormatHTML
            .HTMLBody = Replace(.HTMLBody, "<NAME>", Sheet1.Cells(i, 3))
            .SentOnBehalfOfName = "my mail ID given here"
            .To = sMailID
            .Subject = sSubject
            .CC = Sheet1.Cells(i, 6)
            .Send
        End With
    
        oOLMail.Send
    
    Else
        Exit For
    End If

Next i
MsgBox "Mails successfully sent to :" + vbCrLf + mailsSentString + vbCrLf + "with using the template :" + templateName

Destroy_Objects:
If Not oOLApp Is Nothing Then Set oOLApp = Nothing

Err_Trap:
    If Err <> 0 Then
        MsgBox Err.Description, vbInformation, "VBADUD AutoMail"
        Err.Clear
        GoTo Destroy_Objects
    End If

End Sub

Not giving any error just says mail sent successfully but it is lying in draft.

Community
  • 1
  • 1
Santhi
  • 11
  • 3
  • You are calling Send twice. – Dmitry Streblechenko Feb 17 '20 at 23:45
  • I tried removing the send (extra) from the code also it is not working. Mainly the issue is it is not picking the email ID's in excel sheet. – Santhi Feb 19 '20 at 08:49
  • Do you means the CC recipient is not added? – Dmitry Streblechenko Feb 19 '20 at 13:42
  • mail created and it is in drafts without any email ID's. Email ID's updated in excel – Santhi Feb 19 '20 at 13:46
  • What exactly do you mean by "email ID'"? – Dmitry Streblechenko Feb 19 '20 at 16:58
  • I have to send mails to different people with CC to their respective managers. I have updated their Email ID's in excel but the details updated in excel is not taking and just a draft mail is created without anything in To and CC. – Santhi Feb 20 '20 at 03:30
  • The cells may not be what you think they are. `Debug.Print " Sheet1.Cells(i, 4): " & Sheet1.Cells(i, 4)` and the reverse `Debug.Print " Sheet1.Cells(4, i): " & Sheet1.Cells(4, i)`. – niton Feb 24 '20 at 18:01
  • thanks for the help now I am able to run the code but need help on how to format the data in particular cell of excel while replacing the same in the html mail body (.htmlbody) – Santhi Feb 25 '20 at 06:33

0 Answers0