AABID QURESHI
New Member
- Joined
- Dec 6, 2020
- Messages
- 20
- Office Version
- 2010
- Platform
- Windows
Dear Sir,
I have a format for sending multiple mails with the help of excel.
I need your help to create a code to add the default signature in vba code.
below are the codes.
Option Explicit
Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer
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 = Replace(sh.Range("A" & i).Value, ",", ";")
msg.cc = Replace(sh.Range("B" & i).Value, ",", ";")
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value
If sh.Range("E" & i).Value <> "" Then
msg.attachments.Add sh.Range("E" & i).Value
msg.attachments.Add sh.Range("F" & i).Value
End If
msg.send
sh.Range("G" & i).Value = "Sent"
Next i
MsgBox "All the mails have been sent successfully"
End Sub
I have a format for sending multiple mails with the help of excel.
I need your help to create a code to add the default signature in vba code.
Send Multiple Mails.xlsm | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | To | CC | Subject | Body | Attachment1 | Attachment2 | Status | ||
2 | Form - 16 | C:\Users\aabid.q\Desktop\FORM 16 - Copy\GURM_PART_A\10000015_AITPM6356J_2021-22.pdf | C:\Users\aabid.q\Desktop\FORM 16 - Copy\GURM_PART_B\10000015_AITPM6356J_PARTB_2021-22.PDF | Sent | |||||
3 | |||||||||
4 | |||||||||
5 | |||||||||
6 | |||||||||
7 | |||||||||
Send_Mails |
below are the codes.
Option Explicit
Sub Send_Mails()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Send_Mails")
Dim i As Integer
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 = Replace(sh.Range("A" & i).Value, ",", ";")
msg.cc = Replace(sh.Range("B" & i).Value, ",", ";")
msg.Subject = sh.Range("C" & i).Value
msg.body = sh.Range("D" & i).Value
If sh.Range("E" & i).Value <> "" Then
msg.attachments.Add sh.Range("E" & i).Value
msg.attachments.Add sh.Range("F" & i).Value
End If
msg.send
sh.Range("G" & i).Value = "Sent"
Next i
MsgBox "All the mails have been sent successfully"
End Sub