Excel send email on repeat

DemoLova0

New Member
Joined
Oct 18, 2020
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
Hello,

I have a list of people I need to send them emails.
The people email (@) is in cell number "G3-4-5-6-7-etc"
I need to send the email to the first guy then send it to the second guy ( the email is in the same sheet as the emails in A3-4-5-6-7-etc)
with keeping the A2:G2 in the email.
The email need to be .copy, because it have colors and so on.
however I am stuck with making it repeat to the next guy.
I was able to ancher the banner ( A2:G2)
and make a macro that delete the first row A3
so it moves on to a a3 again and send the email back.
however I am stuck with the "next" as it delete everything inside the sheet before it loop back the email sending.
then the email sending start and it appears empety
any one can assist?


Private Sub CommandButton1_Click()

Dim outlook As Object
Dim newEmail As Object
Dim I As Integer
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Sheet2")
Dim x As Integer


Dim last_row As Integer
last_row = Application.CountA(sh.Range("A:A"))


For I = 2 To last_row

Set outlook = CreateObject("Outlook.Application")
Set newEmail = outlook.CreateItem(0)


With newEmail

.To = sh.Range("G3" & I).Value
.CC = ""
.BCC = ""
.Subject = "a"
.body = "aa"

.Display


Dim xInspect As Object
Dim pageEditor As Object

Set xInspect = newEmail.GetInspector
Set pageEditor = xInspect.WordEditor

Sheet3.Range("A2:G3").Copy


pageEditor.Application.Selection.Start = Len(.body)
pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
pageEditor.Application.Selection.Paste

Set pageEditor = Nothing

Set xInspect = Nothing


End With

Set newEmail = Nothing
Set outlook = Nothing


For x = 1 To last_row


Rows("3").Select
Selection.delete Shift:=xlUp


Next I

End With



Next

End Sub
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Watch MrExcel Video

Forum statistics

Threads
1,128,126
Messages
5,628,858
Members
416,345
Latest member
sayad

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
Top