nikita_patel06
New Member
- Joined
- Dec 3, 2019
- Messages
- 2
- Office Version
- 2019
- Platform
- Windows
Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer
Dim customBody As Range
Dim OA As Object
Dim msg As Object
Set OA = CreateObject("outlook.application")
Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))
For i = 2 To last_row
Set msg = OA.CreateItem(0)
msg.To = sh.Range("A" & i).Value
msg.CC = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value & Range("G2:I10").Select
Selection.Copy
customBody = Range("G2:I10").Copy
If sh.Range("customBody").Value <> "" Then
msg.body.Add sh.Range("C" & i).Value
End If
msg.display
sh.Range("F" & i).Value = "Sent"
Next i
MsgBox "All the mails have been sent successfully"
End Sub
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer
Dim customBody As Range
Dim OA As Object
Dim msg As Object
Set OA = CreateObject("outlook.application")
Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))
For i = 2 To last_row
Set msg = OA.CreateItem(0)
msg.To = sh.Range("A" & i).Value
msg.CC = sh.Range("B" & i).Value
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value & Range("G2:I10").Select
Selection.Copy
customBody = Range("G2:I10").Copy
If sh.Range("customBody").Value <> "" Then
msg.body.Add sh.Range("C" & i).Value
End If
msg.display
sh.Range("F" & i).Value = "Sent"
Next i
MsgBox "All the mails have been sent successfully"
End Sub