Send Emails In Bulk From Spreadsheet

Dazzawm

Well-known Member
Joined
Jan 24, 2011
Messages
3,748
Office Version
  1. 365
Platform
  1. Windows
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>
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
.
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.
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
.
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
 
Upvote 0
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.
 
Upvote 0
.
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.
 
Upvote 0
Yes I did and just came up with errors
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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