send data of range to many emails

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,083
Office Version
  1. 2010
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
ABCDE
1namenumberitamountemail
2al1111111111111.0001100m1@yahoo.com1
3al2222222222.0002200m1@yahoo.com2
4al3333333333333333.0003300m1@yahoo.com3
5al4444370444444444.0004400m1@yahoo.com4
6al5610981555555555.0005500m1@yahoo.com5
7al6777592666666666.0006600m1@yahoo.com6
8al7944203777777777.0007700m1@yahoo.com7
9al81110814888888890.0008800m1@yahoo.com8
10al91277426000000000.0009900m1@yahoo.com9
11al101444037111111110.000101000m1@yahoo.com10
12al111610648222222220.000111100m1@yahoo.com11
13al121777259333333330.000121200m1@yahoo.com12
14al131943870444444440.000131300m1@yahoo.com13
15al142110481555555550.000141400m1@yahoo.com14
16al152277092666666660.000151500m1@yahoo.com15
17al162443703777777770.000161600m1@yahoo.com16
18al172610314888888890.000171700m1@yahoo.com17
19al182776926000000000.000181800m1@yahoo.com18
20al192943537111111110.000191900m1@yahoo.com19
21al203110148222222220.000202000m1@yahoo.com20
22al213276759333333330.000212100m1@yahoo.com21
23al223443370444444440.000222200m1@yahoo.com22
24al233609981555555550.000232300m1@yahoo.com23
25al243776592666666660.000242400m1@yahoo.com24
26al253943203777777770.000252500m1@yahoo.com25
27al264109814888888880.000262600m1@yahoo.com26
28al274276426000000000.000272700m1@yahoo.com27
29al284443037111111110.000282800m1@yahoo.com28
30al294609648222222220.000292900m1@yahoo.com29
Sheet1

thanks advance
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,889
Office Version
  1. 2013
Platform
  1. Windows
You have to make some amendments but take a look over here to get an idea ...
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,083
Office Version
  1. 2010
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
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,889
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
May 3, 2019
Messages
1,083
Office Version
  1. 2010

ADVERTISEMENT

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

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
1,889
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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

Well-known Member
Joined
May 3, 2019
Messages
1,083
Office Version
  1. 2010

ADVERTISEMENT

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

Well-known Member
Joined
May 3, 2019
Messages
1,083
Office Version
  1. 2010
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

Well-known Member
Joined
Sep 10, 2010
Messages
1,889
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Attachments

  • ScreenShot129.jpg
    ScreenShot129.jpg
    243 KB · Views: 4

Forum statistics

Threads
1,141,818
Messages
5,708,769
Members
421,589
Latest member
b_gernert

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