Send Emails In Bulk From Spreadsheet

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
I have a spreadsheet as below. Is there a macro that will open my email client and send an individual email in bulk to all in the sheet? Thanks

Body 1Body 2TitleEmail
JMatearsdgdfbxfbLogin Detailsexample@gmail.com
JBrowningfsfbdfbLogin Detailsexample1@gmail.com
JCampbelldffhdgLogin Detailsexample2@gmail.com

<colgroup><col><col span="2"><col></colgroup><tbody>
</tbody>
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,944
.
Code:
Option Explicit


Sub eMail()
Dim lRow As Integer
Dim i As Integer
Dim toDate As Date
Dim toList As String
Dim eSubject As String
Dim eBody As String
Dim OutApp, OutMail As Object


With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With


Sheets(1).Select
lRow = Cells(Rows.Count, 4).End(xlUp).Row


For i = 2 To lRow
'toDate = Replace(Cells(i, 3), ".", "/")
  'If Cells(i, 3) = Now Then
     Set OutApp = CreateObject("Outlook.Application")
     Set OutMail = OutApp.CreateItem(0)


        toList = Cells(i, 4)    'gets the recipient from col D
        eSubject = "Your VIP Client " & Cells(i, 2) & " participated in the recent Trade Show. " '& Cells(i, 3)
        eBody = "Dear " & Cells(i, 1) & vbCrLf & vbCrLf & "Just a quick note to advise your VIP Client's status at the show." & vbCrLf & vbCrLf & vbCrLf & _
        "Sincerely, " & vbCrLf & vbCrLf & _
        "Tom Banks "
        
        On Error Resume Next
        With OutMail
        .To = toList
        .CC = ""
        .BCC = ""
        .Subject = eSubject
        .Body = eBody
        '.bodyformat = 1
        .Display   ' ********* Creates draft emails. Comment this out when you are ready
        '.Send     '********** UN-comment this when you  are ready to go live
        End With
 
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
 Cells(i, 5) = "Mail Sent " & Date + Time 'Marks the row as "email sent in Column A"
'End If
Next i


ActiveWorkbook.Save


With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With
End Sub
Here is a pic of how the present sheet is designed :



A
B
C
D
E
1
Sales Rep NameVIP Client NameSales Rep EmailConfirmation Email Sent
2
Amit ABCamit@amit.comMail Sent 9/25/2019 8:05:14 PM
3
JackCDEjack@jack.comMail Sent 9/25/2019 8:05:14 PM
4
JillFGHjill@jill.comMail Sent 9/25/2019 8:05:14 PM



You will need to 'slightly' alter the existing code to match your design, or change the locations of your items to correspond to the present code.
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
Will this only work with outlook? I have windows 10 and use the email client that comes with that. Also will this send individual emails or one bulk one? I need individual ones as there will be individual data that others can't see. Thanks.
 
Last edited:

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
For some reason outlook will not install on my PC when I try to add my email, so there is no other email clients possible?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,944
.
The link to Ron's website does provide a few alternatives to Outlook. I admit though that I haven't had much luck with them.
Although there are others on this and other Forums who have. You'll just need to find that right person who has already
"done it".

Ron has an example for GMail and there is another example where you don't use any email client.

Here is the link for downloading a version to send using GMail, Yahoo, Outlook, Hotmail, etc. : https://www.amazon.com/clouddrive/share/0ygXkNTs3VBY1MbcY2Vk8rIDaqE3WCCZM6EJRaVovuD

Here is the GMail only version : https://www.amazon.com/clouddrive/share/TaHV8UYVa4Gimt0MQrdNQ0vGfasAv8n8Cf4kuDprbff
 

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,381
Thanks for you help. I am going to have to forget it, outlook will just not install on Windows 10 correctly. If there was a way to use the preinstalled email on windows 10 it would be great.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
2,944
.
Just out of curiosity ... have you tried using either of the two examples I provided ... to see if they will run on your system "as is" ?

I know it specifies OUTLOOK but I wonder if the code will work with Win 10's email client.
 

Forum statistics

Threads
1,086,257
Messages
5,388,710
Members
402,137
Latest member
pkulkarni

Some videos you may like

This Week's Hot Topics

Top