VBA code to send auto emails from Outlook App based conditions set in Excel

Sonu Singh 21

Hi,

I am looking for VBA cod to automate a reminder sending emails. For Ex, I sent 5 emails consisting of XYZ details & i got response from 2 and rest 3 didn't responded so i want to create a VBA so that a reminder should be sent to those recipients who have not responded yet.

I maintain a tracker in spreadsheet which has 13 columns & 13th column has the recipients email ID and 12th column has the status as " Send Reminder" for which i need to send the reminder.

while sending this reminder i want all the rows from 1 to 10 should there in the body of the email with with "XYZ" comment and all the rows from 1 to 10 from the spreadsheet.

Accepted answer

Doug Robbins - MVP 716 Reputation points

2021-10-03T03:26:52.793+00:00

See "Mail from Excel with Outlook on the following page of Ron de Bruin's web site:

https://www.rondebruin.nl/win/s1/outlook/mail.htm
Please sign in to rate this answer.
Sonu Singh 21 Reputation points

2021-10-03T14:50:00.683+00:00

Thanks for your kind help on this.

I tried getting help for the VBA subject "Mail a row or rows to each person in a range" from the above link but still getting errors not my requirement is not getting fulfilled.

I've attached a sample file here, it would be great help if you can update the VBA in the given file pointedly my requirement as posted earlier and send back to me. This is test file I've attached here and I am using MS 365excel version.

In this file column K is having the VBA condition to whether the mail send required or not. Status mentioned as "Send Reminder" are the one for which I need to send the email with column B:J in the body of the mail.

I will've maximum 1000 entries in this file "A1:L1000", VBA need to look at the data whatever is available within the given range and should send an auto email where be the column K is mentioned as "Send Reminder". It should also send 1 single email if recipients are same for some rows, For Ex in this given file rows no 3 & 6 has the status as send reminder but for both the rows recipients are same so it should send single email with those rows data in the body of the email instead of 2 different emails. Need to have some comments above those rows data in the body of the email as given below.

Hi,

PFB the pending cases.

Regards,
SS

If the column K named (VBA status) is blank then no need to send any email.

Thanks for your assistance here in advance.
137197-test-file.txt
Sign in to comment

4 additional answers

Doug Robbins - MVP 716 Reputation points

2021-10-04T05:59:52.513+00:00

Use

Option Explicit
Dim oOutlookApp As Object
Dim oItem As Object
Dim bStartApp As Boolean
Dim strSignature As String
Dim i As Long
Sub SendReminders()
Set oOutlookApp = GetObject(, "Outlook.Application")
If Err <> 0 Then
Set oOutlookApp = CreateObject("Outlook.Application")
bStartApp = True
End If
Set oItem = oOutlookApp.CreateItem(0) 'olMailItem
With oItem
.BodyFormat = 1 'olFormatPlain
.Display
strSignature = .Body
.Delete
End With
With Sheets(1).Range("A1")
For i = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(i, 10) = "Send Reminder" Then
Set oItem = oOutlookApp.CreateItem(0)
oItem.HTMLBody = "Hi " & .Offset(i, 3) & "," & vbCr & vbCr & "This is a reminder" & vbCr & vbCr & strSignature
oItem.To = .Offset(i, 11)
oItem.Subject = "Reminder"
oItem.Display
oItem.Send
End If
Next i
End With
If bStartApp = True Then
oOutlookApp.Quit
End If
Set oOutlookApp = Nothing
Set oItem = Nothing
End Sub
Please sign in to rate this answer.
Sonu Singh 21 Reputation points

2021-10-04T09:36:12.067+00:00

Thanks for your quick response on this.

I tried with given VBA but it's seems to be not working out as when I run this in my worksheet then a new email populating for a moment and going off.

Could you please check and update on this.
Sign in to comment
Doug Robbins - MVP 716 Reputation points

2021-10-04T09:43:57.91+00:00

Did you check the Sent Items folder?

What is the actual data that you were using? The data in the test-file.txt is very suspect.
Please sign in to rate this answer.
Sonu Singh 21 Reputation points

2021-10-04T12:01:42.63+00:00

Thanks for your quick response again.

Kindly ignore my previous comment. Now I could see it's working fine and mails are going but it's sending a separate email for each row item.

I am looking for a single email if recipients are same for some of the rows items. For Ex: If row no 3 , 8.12 has the same recipients and status as Send Reminder then single mail should be sent consisting those 3,8,12 rows items in the same email as HTML body.

in the same manner it should happen for other row items.

Is there any way to do this.

Sonu Singh 21 Reputation points

2021-10-07T05:17:17.917+00:00

Hi @Doug Robbins - MVP

Could you please help me on this. I am almost done with my requirement if only constraints of sending a single email with all the rows items mentioned against the similar recipients in excel file is fixed. Pls help

Doug Robbins - MVP 716 Reputation points

2021-10-07T05:52:19.753+00:00

That sounds like an application for the use of the Many to One Facility on my Merge Tools Add-in that is contained in the MERGE TOOLS ADD-IN.zip file that you can download from the following page of my One Drive:

http://bit.ly/1hduSCB

Extract the files from the archive and read the:

“READ ME – Setting up and using the Merge Tools Add-in.pdf

to see how to install and use the various tools.

Using those tools, it is possible to perform the following types of merge that cannot be done with Mail Merge “out-of-the-box”:

● Merge to e-mail messages either with or without attachments, with the documents created by the merge being sent as either Word or PDF attachments or as the body of the e-mail message.
● Merge to individual documents in either Word or PDF format with the filenames being supplied by the data in one of the fields in the data source
● Many to One type merges, which can be used for creating documents such as invoices where there are multiple records in the data source that have common data in one of the fields
● Merging to a document that will include a chart that is unique to each record in the data source
● Merging a document with Content Controls
● Merging a document that contains Legacy FormFields
● Duplex Merges
● Merging to a printer that will collate and staple the output created from each record in the data source.

Sonu Singh 21 Reputation points

2021-10-10T04:30:30.15+00:00

Can't this be done through VBA as I've some security restriction on my system which will not allow me to save this MergeTools - 2021 file in the given path. I'm holding an asset which is issued by my organization having lot of security constraints applied in it which will not allow me to save any such file.

Kindly check and help me if we can make this possible through VBA pls.
Sign in to comment
Doug Robbins - MVP 716 Reputation points

2021-10-10T05:45:17.317+00:00

The add-in uses VBA to do exactly what you want. However, due to the hundreds of hours spent developing it, I am not about to reveal the code that it uses.

Have you tried saving it in the Word Startup Folder, which, by default, being a trusted location, should allow you to run it.
Please sign in to rate this answer.
Sonu Singh 21 Reputation points

2021-10-17T09:09:27.567+00:00

Thanks for the suggestion and I tried saving this in the Word Startup Folder and saved it successfully.

Now when I am trying to execute the "Merge with attachments" option to then getting error as "This application is only designed to be run with a Letters type mail merge main document".

Could you please check this error and let me know what wrong I am doing and how to fix this.
Sign in to comment
Doug Robbins - MVP 716 Reputation points

2021-10-17T09:18:57.1+00:00

The add-in is based on starting with a Letters type mail merge main document to which you attached your Excel Data Source.

To set the mail merge main document as a Letters type, access the Start Mail Merge dropdown in the Start Mail Merge section of the Mailings tab of the ribbon in Word and click on Letters. Then, use the Select Recipients facility to attach your Excel data source to the mail merge main document.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment