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.
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Hi Logit Thank you so much for all your kindness, effort and time. wowww, i am really impressed this stuff is really good Thank you once again. :) :giggle::giggle:
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Hi Logit Sorry to be pain but i was wondering is there any way to avoid sending duplicate email if email already has been sent in past for same name and date?
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Morning Logit this is perfect. i cannot enough of your appreciation. Thank you for all your time, effort and kindness. :giggle: :) :giggle:
 

hsolanki

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

ADVERTISEMENT

Hi Logit sorry just a quick question how can i put a message box if no new due date is found in any of the department, e.g. YOU button pressed and there no new due dates and i press Email button it will still sends out an blank email can we avoid this. i done below whereby user will need to select each department prior to sending out an email.
VBA Code:
If ActiveSheet.AutoFilterMode = False Then
    MsgBox "Select the department First", vbExclamation, "Select dept."
    Exit Sub
    End If

however below only works if filter is off and nothing in first cell, mainly it works when i clear all data
VBA Code:
If Sheet2.Range("A2").Value = "" Then
    MsgBox "There is no data to send email", vbExclamation, "Confirm Department first"
    Exit Sub
    End If

i am really sorry once again if m i being a pain.
 

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
4,150
VBA Code:
Sub CopyRows()
Dim i As Integer

If Sheet2.Range("A2") = "" Then
    MsgBox "Nothing to email.", vbInformation, "NO DATA"
    Exit Sub
End If

TopNRows

Dim ws1 As Worksheet: Set ws1 = ThisWorkbook.Sheets("EmailReport")
    ws1.Range("A1:C36").Copy
    
Sheets("EmailReport").Range("A1").Select

Mail_Selection_Range_Outlook_Body
    
End Sub
 

hsolanki

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

ADVERTISEMENT

Hi Logit it still doing the same whereby message box only comes up if i clear all data however if Filter is OFF then it works but If filter is on then you can still be able to send blank email. i uploaded 2 pic with Filter On and Filter Off
 

Attachments

  • Filter OFff.jpg
    Filter OFff.jpg
    65.4 KB · Views: 2
  • Filter On.jpg
    Filter On.jpg
    67.3 KB · Views: 3

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Sorry i meant if Filter is On and there is no Due dates you can still sends blank email.
 

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
153
Office Version
  1. 2010
Platform
  1. Windows
Hi Logit i am sorry i wonder if you can help me one more time as i have got an issues. for example when you filter the department lets say Head then it will send a email and copy all those names to EmailsSent however when you filter another department again let say Training it will send an email with all all names and copy all the names in Training however when it pastes on to EmailsSent sheet it will overwrite on the very previous name row on the EmailsSent instead of pasting it on to the next blank row. i have tried so much but did not go too far.
 

Forum statistics

Threads
1,141,412
Messages
5,706,297
Members
421,439
Latest member
JordsdoExcel

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