excel to send email when all dates in column are due

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Hi I have an staff training record whereby in column F-Z starting from row 9 with the expiry date and in column A9 onward all the staff name and type of training in columns F9-Z9.

i want macro to send email with the all the Due date in each column to One send email with the name, due date and types of training are due for the column F, for example. and so on for different column to different people.

i would really appreciate if someone could help me with the above query.

btw Happy New Year 2021 to all.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
.
Regarding the email :

#1 Do you have Outlook installed on your computer ?

#2 You need to make a "reference" to Outlook. You can reference either of the following :
Microsoft Outlook 12.0 Object Library
Microsoft Office 12.0 Object Library

Check or add an object library reference (VBA)

I'll look at the other issue of copying only the individual cell vs the entire row.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Hi Logit Sorry yes i do have an outlook and i managed to get it worked with the email.

only the 2nd issue whereby if can be done on basis of date +60days and copy only the cells that has triggered off something similar as picture attached on my previous post.

also on current sheet Email report it is only copying until the column Z and missing from AA - AN when copying on EmailReport.

also with the current it is duplicating rows

No problem, Thank you
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
.
Presently, copying duplicate rows ... that is because there are more than one red cell in the row. If there are two red cells, the row will be copied twice.
If there are three red cells, the row will be copied three times.

"only copying until col Z ..." you had indicated only the columns F:Z were of interest to you.
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
YH sorry but can it be changed to dates if more than 60days from the expiry date. I want all the information from the training matrix whenever it meets the criteria of 60days + and only copy of the column that has date matched with the name column.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142

ADVERTISEMENT

(y)
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
.
VBA Code:
Sub sintekJ3v16()
Dim Temp(), i As Long, cell As Range, Rng As Range
Set Rng = Sheets("TrainingMatrix").Range("E9:AM36"): ReDim Preserve Temp(1 To Rng.Cells.Count, 1 To 3)
For Each cell In Rng.Cells
    If cell <> "" And IsDate(cell) And cell >= Date And cell <= Date + 60 Then
        i = i + 1: Temp(i, 1) = Cells(cell.Row, 2): Temp(i, 2) = Cells(6, cell.Column): Temp(i, 3) = cell
    End If
Next cell

With Sheets("EmailReport"): .Range("A1").Resize(i, 3) = Temp: .Columns.AutoFit: End With

End Sub

Download workbook : Training Matrix 1-7-2021.xlsm
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi Logit Thank you so much, i really appreciate for the excellent piece of work you have put together and it works, however can be tweeted little with the code if can be done. i have put the code in Private Sub Workbook_Open() therefore whenever file is open email will send automatically with the code you have done however now i have got 2 problems;

1- when email is open it will copy even the all blank rows from EmailReport range, can it be copy only the rows that has value within the EmailReport range?
2- Each time workbook is open email will repeat for the same names and dates therefore is there any way to avoid sending out duplicate emails?
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
I don't understand the issues you are experiencing there.

Running the macro/s here, it finds all pending expirations that will occur between the current date to 60 days into the future. It does not
copy "blank cells".

Your goal was to send the report to a single email address - the macro accomplishes that.

?????
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Hi Logit Yes it does what i meant is that can it be avoid sending duplicate emails e.g. if expired date found then email will sent and against the column it will say Email "Sent" therefore it will skip that date and look for another expiry date if there. currently it will send out email each time command button is pressed or open. i was wondering if something like attached image showing "sent" therefore it will skip that.
 

Attachments

  • Email sent.jpg
    Email sent.jpg
    89 KB · Views: 7
  • email sent 2.jpg
    email sent 2.jpg
    189.1 KB · Views: 6

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,142
From the images provided, you have changed your workbook layout. The version I have here is not the same as you are showing.

?????
 

Forum statistics

Threads
1,141,045
Messages
5,703,922
Members
421,321
Latest member
blusky4

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