learningVBA813
New Member
- Joined
- Aug 26, 2020
- Messages
- 3
- Office Version
- 2016
- Platform
- Windows
Hi,
I have a list of people i want to send emails with different messages. (each message will be a range of cells from different files)
column D is the different file paths, Column E2:E11 is the recipient emails and
I currently got a code which can only help me send email with the message i want and include column D's files as an attachment..
I would like to replace it.. instead of the attachment, i want the content in the attachment to be the body in the email... how should i do?
THanks
Sub AutomatedEmail()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Email list")
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("E2:E11"))
For i = 2 To 10
Set msg = OA.CreateItem(0)
msg.To = sh.Range("E" & i).Value
msg.CC = sh.Range("H" & i).Value
msg.Subject = sh.Range("F" & i).Value
msg.Body = sh.Range("G" & i).Value
If sh.Range("D" & i).Value <> "" Then
msg.Attachments.Add sh.Range("D" & i).Value
End If
msg.Send
I have a list of people i want to send emails with different messages. (each message will be a range of cells from different files)
column D is the different file paths, Column E2:E11 is the recipient emails and
I currently got a code which can only help me send email with the message i want and include column D's files as an attachment..
I would like to replace it.. instead of the attachment, i want the content in the attachment to be the body in the email... how should i do?
THanks
Sub AutomatedEmail()
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Email list")
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("E2:E11"))
For i = 2 To 10
Set msg = OA.CreateItem(0)
msg.To = sh.Range("E" & i).Value
msg.CC = sh.Range("H" & i).Value
msg.Subject = sh.Range("F" & i).Value
msg.Body = sh.Range("G" & i).Value
If sh.Range("D" & i).Value <> "" Then
msg.Attachments.Add sh.Range("D" & i).Value
End If
msg.Send