To send to list of emails with different content in different attachment

learningVBA813

New Member
Joined
Aug 26, 2020
Messages
3
Office Version
  1. 2016
Platform
  1. 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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
1598428329029.png


for example i want cell A1 : Z28 of each file to be send together with the message body for each email.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top