Send Emails In Bulk From Spreadsheet

[Dazzawm]

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

[TABLE="width: 725"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]Body 1[/TD]
[TD]Body 2[/TD]
[TD]Title[/TD]
[TD]Email[/TD]
[/TR]
[TR]
[TD]JMatear[/TD]
[TD]sdgdfbxfb[/TD]
[TD]Login Details[/TD]
[TD]example@gmail.com[/TD]
[/TR]
[TR]
[TD]JBrowning[/TD]
[TD]fsfbdfb[/TD]
[TD]Login Details[/TD]
[TD]example1@gmail.com[/TD]
[/TR]
[TR]
[TD]JCampbell[/TD]
[TD]dffhdg[/TD]
[TD]Login Details[/TD]
[TD]example2@gmail.com[/TD]
[/TR]
</tbody>[/TABLE]

 

[Logit]

.

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 :



[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td]Sales Rep Name[/td][td]VIP Client Name[/td][td][/td][td]Sales Rep Email[/td][td]Confirmation Email Sent[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td]Amit [/td][td]ABC[/td][td][/td][td]amit@amit.com[/td][td]Mail Sent 9/25/2019 8:05:14 PM[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td]Jack[/td][td]CDE[/td][td][/td][td]jack@jack.com[/td][td]Mail Sent 9/25/2019 8:05:14 PM[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td]Jill[/td][td]FGH[/td][td][/td][td]jill@jill.com[/td][td]Mail Sent 9/25/2019 8:05:14 PM[/td][/tr]
[/table]



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]

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.

 

[Logit]

Will this only work with outlook? YES

There are other examples on the internet which allow for use of Gmail, etc.

Also will this send individual emails or one bulk one? Each email will be separate from all the others.



Everything email : https://www.rondebruin.nl/win/s1/outlook/mail.htm

 

[Dazzawm]

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]

.
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]

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]

.
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.

 

[Dazzawm]

Yes I did and just came up with errors