send data of range to many emails

[abdelfattah]

hi
i'm asking about if is there way to send data of range to many emails the data of range begins from a2: d and emails in col e
i appreciate if any body help to get macro do that

emails.xlsb
	A	B	C	D	E
1	name	number	it	amount	email
2	al1	111111111111.000	1	100	m1@yahoo.com1
3	al2	222222222.000	2	200	m1@yahoo.com2
4	al3	333333333333333.000	3	300	m1@yahoo.com3
5	al4	444370444444444.000	4	400	m1@yahoo.com4
6	al5	610981555555555.000	5	500	m1@yahoo.com5
7	al6	777592666666666.000	6	600	m1@yahoo.com6
8	al7	944203777777777.000	7	700	m1@yahoo.com7
9	al8	1110814888888890.000	8	800	m1@yahoo.com8
10	al9	1277426000000000.000	9	900	m1@yahoo.com9
11	al10	1444037111111110.000	10	1000	m1@yahoo.com10
12	al11	1610648222222220.000	11	1100	m1@yahoo.com11
13	al12	1777259333333330.000	12	1200	m1@yahoo.com12
14	al13	1943870444444440.000	13	1300	m1@yahoo.com13
15	al14	2110481555555550.000	14	1400	m1@yahoo.com14
16	al15	2277092666666660.000	15	1500	m1@yahoo.com15
17	al16	2443703777777770.000	16	1600	m1@yahoo.com16
18	al17	2610314888888890.000	17	1700	m1@yahoo.com17
19	al18	2776926000000000.000	18	1800	m1@yahoo.com18
20	al19	2943537111111110.000	19	1900	m1@yahoo.com19
21	al20	3110148222222220.000	20	2000	m1@yahoo.com20
22	al21	3276759333333330.000	21	2100	m1@yahoo.com21
23	al22	3443370444444440.000	22	2200	m1@yahoo.com22
24	al23	3609981555555550.000	23	2300	m1@yahoo.com23
25	al24	3776592666666660.000	24	2400	m1@yahoo.com24
26	al25	3943203777777770.000	25	2500	m1@yahoo.com25
27	al26	4109814888888880.000	26	2600	m1@yahoo.com26
28	al27	4276426000000000.000	27	2700	m1@yahoo.com27
29	al28	4443037111111110.000	28	2800	m1@yahoo.com28
30	al29	4609648222222220.000	29	2900	m1@yahoo.com29
Sheet1

thanks advance

 

[GWteB]

You have to make some amendments but take a look over here to get an idea ...

VBA Macro - Copying selected range of cells to email and sending whilst Outlook is running.

Hi, Hopefully somebody can help a complete amateur out: I have created an order form to simplify an ordering process between 30 stores (with varying levels of I.T ability) and a HQ. When the 'send weekly orders' button is pressed (with the assigned macro below) it copies the table (including...

www.mrexcel.com

 

[abdelfattah]

hi, GWteb i changed some ranges based on my data so i need your help if something need amending i no deal with this code about emails before

Sub esendtable()

If MsgBox("Are you sure you would like to send this weeks Customer Orders?", vbYesNo) = vbNo Then Exit Sub

Dim outlook As Object
Dim newEmail As Object
Dim xInspect As Object
Dim pageEditor As Object
Dim rng As Range

'Optimize Code
  Application.ScreenUpdating = False

'Store Range to a variable
  Set rng = Range("E2:E100")

'Clear Any prior sorting
  ActiveSheet.Sort.SortFields.Clear

'Sort Range Alphabetically (A-Z)
  rng.Sort Key1:=rng.Cells(1), Order1:=xlAscending, Header:=xlNo
  


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


With newEmail
    .To = "Myemail.com"
    .CC = ""
    .BCC = ""
    .Subject = ""
    .Body = "Please see below this weeks customer orders. Thanks"
    .Display
  
    Set xInspect = newEmail.GetInspector
    Set pageEditor = xInspect.WordEditor
  
    Sheet1.Range("a2:d100").Copy
  
    pageEditor.Application.Selection.Start = Len(.Body)
    pageEditor.Application.Selection.End = pageEditor.Application.Selection.Start
    pageEditor.Application.Selection.PasteAndFormat (wdFormatPlainText)
    .Display
    .Send
    Set pageEditor = Nothing
    Set xInspect = Nothing
  
    MsgBox "Your Orders Have Been Sent"
End With
End Sub

thanks

 

[GWteB]

Some more information is required:
- does each data row represent the content of ONE single message?
- if yes, what do you want to be sent?
- if no, what do you want to be sent?
You need to be more specific.

 

[abdelfattah]

yes each data row represent the content of ONE single message
send supposes data from range a2:d as my image

 

[GWteB]

See if this works for you. Ged rid of the single quote to actually send each mail and you may comment out the .Display to avoid unnecessary pop ups in case you're content with the result.

Public Sub SendMails()

    Dim olApp       As Object
    Dim newEmail    As Object
    Dim sMsg        As String
    Dim rng         As Range
    Dim c           As Range

    On Error Resume Next
    Set olApp = GetObject(, "Outlook.Application")
    On Error GoTo 0
    If olApp Is Nothing Then
        Set olApp = CreateObject("Outlook.Application")
    End If

    With ThisWorkbook.Sheets("Sheet1")
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
    End With

    For Each c In rng
        sMsg = c.Value2 & vbCrLf & _
               c.Offset(, 1).Value2 & vbCrLf & _
               c.Offset(, 2).Value2 & vbCrLf & _
               c.Offset(, 3).Value2 & vbCrLf
        
        Set newEmail = olApp.CreateItem(0)
        With newEmail
            .To = c.Offset(, 4).Text
            .CC = ""
            .BCC = ""
            .Subject = "Subject"
            .Body = "Dear customer," & vbCrLf & vbCrLf & sMsg & vbCrLf & "Regards"
            .Display
         '   .Send
        End With
    Next c
End Sub

 

[abdelfattah]

actually there is a problem this is what i got so far
Open the sending e-mail list and write the e-mail sent to him and the data under some

However, the email address is not sent

 

[abdelfattah]

When you click on Send, the Send Email screen appears, with To

The email that you wrote in Excel is completely written in it, as well as the data that you wrote in Excel appears under each other in the inclination sending screen

Suddenly it disappears and I check the tendency to find the message but i do not find any thing has sent

Thank you for attention, may God bless you

 

[GWteB]

You are welcome, however ..... apparently I was not clear enough in my explanation. The code was just a start so you could examine the result. I could imagine that you don't want to send an email that looks like this. For that reason the line calling the .Send method was commented out (see attached image) so nothing was sent yet! You were supposed to go through the code using the F8 key or (even better) to set a breakpoint on the second bottom line (cursor on Next c followed by pressing F9 key, followed by pressing F5 key).
Regarding any further information required, for example, should the date be enumerated, as in:
name _ :al1
number_:1111111
it _ _ :1
amount :100
or as in
name|number|it|amount
al1 |111111|1 |100
or perhaps in a real (colored) table? Do you use a concluding remark and/or an automatic signature?
In short, I cannot determine exactly what you have in mind and I do not want to guess. If you can provide more information maybe even through an image, I would be happy to help.