Automatically send email based on Excel cell values (Macro)

Topic

New Reply

Hey all, l’m new here and hope I can get some help with a macro that I have spent hours trying to get correct and have not had any luck. I have a workbook that tracks data from employees and what I need is an automatic email sent to HR when either of two columns (column AE and AF) reach a certain cell value. It will also need to send another email with any increases to the cell value after the starting value is reached but decreases to the cell value will not create an email. I have been trying to get this put together for some time now but I just can’t seem to figure it out so I’m asking any of you experts for some help. Any help would be greatly appreciated!!! Below is what I believe to be the necessary data and if I’ve missed something please let me know.

Using Excel 2013
Email is Outlook
Column B is populated with employee names
Column AE starting value = 4
Column AF starting value = 40
The two columns are populated with formulas, they are not a manual entry
The worksheet title is “Summary” and this is the only worksheet in this workbook that the macro needs to be applied to.
I would like, but not required, to have the email sent when the workbook is closed. It can be sent as soon as the cell is changed.
The data collection starts on Row 7 but the last row fluctuates depending on the number of employees
All emails will be sent to the same email address, let’s use hrmanager@companyabc.com

Email Subject = “(Data in Column B) attendance”
The body of the emails needs to read:
“HR Manager,
(Data in Column B) currently has (data in AE) tardies in the past year and needs to have his/her attendance reviewed.
Thanks”

Or

“HR Manager,
(Data in Column B) currently has (data in AF) unexcused hours in the past year and needs to have his/her attendance reviewed.
Thanks”

Reply | Quote

Replies

Hi

Welcome to the lounge.

See my attached workbook.
I have added named cells to set the limits for triggering the emails.
In the vba routines, for testing I used
.Display
change this to
.Send
to actually send the emails
Don’t forget to set the correct email address in the vba

zeddy

Reply | Quote

Something to consider to augment Zeddy’s nice piece of code would be to indicate if an email was previously sent along with being time stamped with a date and time. If a comment is inserted in the cells using code in Column AE when an email is sent, it can include a date and time for reference. Also if new tardies occur and you re-run the code, it can skip the tardies whose emails were sent the day before by looking if a comment exists.

You can see this here:

http://windowssecrets.com/forums/showthread//166318-Creating-Outlook-Calendar-reminders-from-Excel-spreadsheet?p=983120&viewfull=1#post983120

HTH,
Maud

Reply | Quote

Hi Zeddy,

Thank You! This is great but it appears that it will only send the first occurrence in the list of employees, unless I’m doing something wrong. What do I need to do to make it send emails for the entire list of employees? Once I get that set up I will try and apply Maudibe’s code because I do not want the same email being sent multiple times, unless a new tardy occurs. Thanks again, to both of you!

Reply | Quote

SA,

I could not find any issues with Zeddy’s code except for one very minor flaw. The named range countUnexcused points to AI5 instead of AH5 which gives a garbled message in the status bar…. otherwise sound.

To add the comment with timestamp and then checks for it when it cycles through the list, change to the following lines in blue:

Sub sendTardies()

Code:

For Each cell In Range(temp)
    zTardy = cell.Value
    [COLOR=”#0000FF”]If zTardy > zTardyLimit And cell.Comment Is Nothing Then
        cell.AddComment
        cell.Comment.Text Text:=”Email sent ” & Date[/COLOR]
        zRow = cell.Row
        zName = Cells(zRow, “B”) 

Sub SendUnexcusedEmails()

Code:

For Each cell In Range(temp)
    zHours = cell.Value
    [COLOR=”#0000FF”]If zHours > zLimit And cell.Comment Is Nothing Then
        cell.AddComment
        cell.Comment.Text Text:=”Email sent ” & Date
[/COLOR]        zRow = cell.Row
        zName = Cells(zRow, “B”)

39211-Tardies

HTH,
Maud

Reply | Quote

Hi Maud

..thanks for fixing my named cell. I was ‘moving’ all the named cells to a ‘spare’ column to the right of the data (to make it easier for SA to incorporate). Missed that. On my system, it processed the emails so fast I never saw the messages in the statusbar.

I like your use of cell comments in the updated code.
Especially good as one person may trigger both emails!

zeddy

Reply | Quote

I was looking for a solution to a similar problem and stumbled on to this. This is 90% of what I need, but I’m having trouble making it fit my application. I’m trying to poll a massive shared excel spreadsheet row by row for missing data in one particular cell (it would be blank), and then send the previous 6 cells in that row along with the row headers to an email address in the cell 7 rows previous. It’s reminders for sites that haven’t reported back corrective action on incidents, the previous rows are the demographics of the incident and the polled cell is where the corrective action SHOULD be. Basically, we’re spending a lot of time clicking, copying, and pasting information into an email template, then pulling the email address based on the site ID and hitting send.

I’m good with Excel, so I can shoehorn the data to make it fit whatever template I might find, but I just can’t figure out exactly how to fix the macro. One challenge is that I think I need more variables to pull the data into the email, I was reading in the Help that you have to declare variables, and I’m not sure where to do it. And instead of testing the cell to see if it matches another, I just want to see if it’s blank. zHours = 0 and zHour = “” both seem to not work. I suppose I could just filter out anything that isn’t blank before I run the macro and skip that whole bit entirely…

I attached some dummy data formatted the way mine is currently. In short, for each row, if H is blank, I want to send B through H (and the headers) to A. I imagine it would be pretty easy for anyone who knows this stuff to modify the posted macro to get something to work. Failing that, any guidance on how to proceed would save me a lot of additional research. I used to program in QBASIC and C++ years ago, so I’m not totally hopeless, I just don’t know enough about the particulars of VB. Thanks in advance for any help!

Reply | Quote

Hi Maud, I am new to the site and found this while looking for a similar solution to my problem. What I am trying to determine is if this code can be modifed to send emails to individual addresses in the spreadsheet. Ie, each employee will get an email sent to their own email and also if it is possible to have the content of the email changed based a cell value. ie if the employee has a number 10 send message 3.

I am not really familar with coding or macros but can understand most of what has been done in your examples and any help or advise you could provide would be greatly appreciated.

Thank you

Fenton

Reply | Quote

SA,

I could not find any issues with Zeddy’s code except for one very minor flaw. The named range countUnexcused points to AI5 instead of AH5 which gives a garbled message in the status bar…. otherwise sound.

To add the comment with timestamp and then checks for it when it cycles through the list, change to the following lines in blue:

Sub sendTardies()

Code:

For Each cell In Range(temp)
    zTardy = cell.Value
    [COLOR=”#0000FF”]If zTardy > zTardyLimit And cell.Comment Is Nothing Then
        cell.AddComment
        cell.Comment.Text Text:=”Email sent ” & Date[/COLOR]
        zRow = cell.Row
        zName = Cells(zRow, “B”) 

Sub SendUnexcusedEmails()

Code:

For Each cell In Range(temp)
    zHours = cell.Value
    [COLOR=”#0000FF”]If zHours > zLimit And cell.Comment Is Nothing Then
        cell.AddComment
        cell.Comment.Text Text:=”Email sent ” & Date
[/COLOR]        zRow = cell.Row
        zName = Cells(zRow, “B”)

39211-Tardies

HTH,
Maud

Hi Maud, I am new to the site and found this while looking for a similar solution to my problem. What I am trying to determine is if this code can be modifed to send emails to individual addresses in the spreadsheet. Ie, each employee will get an email sent to their own email and also if it is possible to have the content of the email changed based a cell value. ie if the employee has a number 10 send message 3.

I am not really familar with coding or macros but can understand most of what has been done in your examples and any help or advise you could provide would be greatly appreciated.

Thank you

Fenton

Reply | Quote

…each employee will get an email sent to their own email and also if it is possible to have the content of the email changed based a cell value.

Fenton,

What you are seeking is very doable. Running reports at work, I generate emails daily to various individual department heads with customized data added to the body based on returned cell values. Your simplified example is no different.

Zeddy has demonstrated this nicely throughout this thread. To maintain continuity and since Zeddy has developed the code seen here, he would be the best to modify it to meet your needs if he would like. Of course, if you hit a brick wall, I would be willing to help out.

Maud

Reply | Quote

You guys are awesome… I am out of the office for the week but will peck around with this some more as soon as I get back!

Reply | Quote

Hi zeddy!

Many thanks for your excellent help and support.

I am too trying to create a macro that it will send automatically an e-mail as a reminder when it reaches a date (i.e. columns F, J, R, V, Z and AD). Could you please help?

The info in these threads is too much for me to handle and as much as I am trying it doesn’t get me anywhere. 🙁

Many thanks in advance, really appreciated!

Keep up the good work!

Reply | Quote

Can someone please help me?

Thanks.

Reply | Quote

Hi Fenton

It would be great if you could attach a sample file of what you have.
It doesn’t need to include any sensitive info.
You could use dummy names and dummy email addresses if you want
(see my dummy names in post#68 – my favourite is Ewan Hoosami)
It makes it easier for us.

zeddy

Reply | Quote

When you send an .xlsX file you automatically strip out all macros. Try sending your file with the macros.

Reply | Quote

When you send an .xlsX file you automatically strip out all macros. Try sending your file with the macros.

That might help, right? Sorry…

I played with this a little during some down time and I think I have it about ready, but there’s one gremlin I can’t figure out. When I set it to .Display the emails, I can see it cycling through all of the emails. But when I set it to .Send the emails, it only sends the first one. Any idea how to get past this? Do I need to set a delay so Outlook can keep up? If I can get that done, I’m gold.

Thanks for everything.

(Important Note: I haven’t gone through the trouble of updating all the comments, or even the name of the original Macro. I made my changes to the “sendUnexcusedEmails” macro. The only real change was the test of whether the macro would send the email from a greater-than-a-reference-cell to an equal-to-a-reference-cell (a blank one) statement. The rest is just pulling different cells and the body of the email. I didn’t want to delete or change any of the other stuff for fear it would stop working.)

Reply | Quote

GOT IT!

The problem is actually with the original macro. Minor fix, instead of putting the line of code “Set OutMail = OutApp.CreateItem(0)” towards the beginning, it needs to be inside the iterative process. I put it right after all the “strbody = strbody &…” definitions. Works like a charm. I think that if it’s outside that process, it only creates one email and sends it, or continues to modify it in the case of the “.display” function. Inside the process, it creates a new email for each cell it encounters. Just a warning, at least for my purposes, that may mean it pops out hundred of emails at once, so if you’re “.display”ing them, it may crash you. For the example attached, there’s only 9 emails generated, not as big a deal. But unlike the original, it doesn’t just change around the information on one email when you display it, it generates 9 separate emails.

I’ve attached my fixed template. I still didn’t clean up the comments, sorry. I’ve spent enough time on this today. Thanks again to the OP and to all the contributors, this is going to free up a lot of time for my people. Cheers!

Reply | Quote

I would of been lost if it wasn’t for your post and high calibre macro/VBA coding. Admiration to you stranger.

Reply | Quote

Hi John

Welcome to the Lounge as a new poster.

Are you talking about that file I posted a year ago?
If so I’m glad you found it useful.

zeddy

Reply | Quote

It looks like you are talking about the tardies. I see no col G but if the macro and the data coincide maybe this is correct
.to= c

Reply | Quote

Dalanhurt,

Also, one of your blank cells in col G has a space in it throwing you blank cell number from 10 to 9 in your sample

HTH,
Maud

Reply | Quote

Dalanhurt,

Also, one of your blank cells in col G has a space in it throwing you blank cell number from 10 to 9 in your sample

HTH,
Maud

Good catch. I’ll still take 9 out of 10 over putting together the reminders individually . I used this to send 105 reminders for March incidents in SECONDS today (Now I know I missed at least one…). But still, that would have been a boring day’s work for my admin. And with your modification, someone might see that the email didn’t get sent and investigate further. I’m not good enough to make the macro robust to the point it would catch a ” ” or a ” “, so I’ll probably handle it by just pre-screening the data a little better. Maybe sorting by the field, highlighting all the fields that APPEAR to be blank, and deleting them just to be sure.

Anyhow, I went ahead and “cleaned up” the macro name, pulled out the unnecessary bits, and added comments for posterity. In case some future unfortunate is looking for a similar solution, they can see how I muddled through to make it work for my application. Can’t say enough thanks to you guys for putting the original together.

Reply | Quote

Hi

I have an updated sample file which uses the email address from column [A] as in your previous data sample.
This new version uses an Excel Table.
(To create a Table, start with your initial sample, select a cell in the header, and press Ctrl-L)

I think a Table would be more useful for you.
This update will also log when an email is sent (an idea that Maud suggested previously).

I am away for the weekend.
Any questions, post back.

zeddy

Reply | Quote

Looks like another good solution, probably more efficient than the one I landed on, and one that might be more helpful if someone else ever needs to borrow this solution for their own application. If I was more patient, I guess I could have just waited for you to build the better mousetrap. For my part, though, I don’t mind interrogating every cell in the “target” column for blanks, since I’d imagine checking even 20,000 lines would only take a fraction of a second longer than only pulling the blanks in the first place.

This is off-topic, except that I’m trying to add some bells and whistles to this. Is there a way to look up information from a SEPARATE reference excel spreadsheet at a static address, or failing that, from a separate worksheet within the workbook? I imagine a list that shows the terminal in one column, then a distribution list as text in the next column. Just working to improve the monster, it’s just a little copying and pasting and a dummy tracker column in order to set up a “Send2ndreminder” and “Send3rdreminder” macro, so now I’m wondering how to build the escalating management tiers for each reminder. This is getting more academic, since the return on that kind of effort is minimal. I could just VLOOKUP the relevant distribution lists into a hidden cell on each row and pull them into the “cc” field in the macro, but I’d rather maintain everything elsewhere and have the macro pull it automatically. Also, I’d generally like to make this robust enough that I wouldn’t have to rely on updating the macro as the months change and the links to current information are different. Similar problem, the only solution I can find is to throw the address into a hidden cell in the worksheet, rather than polling a reference based on the month. Now that I’m thinking about it, there’s probably a way to just piece the addresses together using the date from the first incident and solve that problem. I’ll stop thinking out loud. This has opened some doors, I’m hoping to start moving some of our processes out of the 80’s and get my folks into more meaningful work.

Reply | Quote

Hi

The best solution is to get the guys to stop spilling stuff.
Then you don’t need to send any reminder emails.

But if you do, Maud is the guy that knows the emailing tricks and stuff.

zeddy

Reply | Quote

Hi sorry guys for reopening this post. I am new to the forum and found a lot of helpful trips and tricks in here that would really help me at work but one thing that I needed most I can’t resolve on my own. I tried to copy some of the macro here to fit my work needs but to my dismay I’m on a dead end again.

I really need to create a VBA that will automatically sends out an email when a multiple cells in Excel 2013 meet a criteria. This is a worksheet that monitors employee’s # of hours # of months, age and status. This worksheet is updated 2x weekly and becoming too much for me to handle. Automating the worksheet will help ease up my workload and I’ll be able to work on other stuff.
Here is what is suppose to happen:
Column U to X should equal to Yes in order for it to generate an email automatically. ( is it possible for it to go automatically even if excel is not open).
If an employee meets all the criteria on column u to x, excel will automatically sends out an email and the body of the email will show the info on column A to G for those that met the criteria in column U to X.

After an email is generated it will mark column G as “Sent” to avoid duplicates in the future when other employees meet the criteria and for macro to know not to send it again.

I know little excel , if you guys have any suggestions to make my worksheet a little bit more efficient I will really really appreciate it.

Thanks in advance for your assistance.

Reply | Quote

Hi, I realize this thread goes a wee way back now but I am hoping someone is able to help me!
I have a similar question. I have an excel database with over 2000 staff. Staff are identified by a 5 letter/number code called a QID; example APEV70. The database has 15+ columns each with different Certification expiry dates in them (for example: First Aid Certification Expiry), I need to automatically generate an email via Outlook to a specific email address (ie The Boss) listing all the QID’s that have expiration dates that fall within 30 days of ‘now’, and what the expiry date is. I would need to send a separate email for each certificate (column). I then would like to be able to send a group email to all the individual QID’s that have expiry dates within that 30 day window. Can anyone help/give advice?

Reply | Quote

Hi

..re:your database of 15 columns: does this include a column for the email address for the staff (QID) member?

Just to be clear, if a particular QID has, say, four Certification expiry dates falling within the next 30 days, do you want them to get four individual emails, or just one email listing all the Certificates due to expire?

Is the email sent to The Boss sent weekly? monthly? daily? on last Friday of the month??
If there are say, 20 Certificate types, the Boss gets 20 emails (one per Certificate type).
What if there are NO certificates (of a particular type) expiring in the next 30 days – does the Boss get a ‘None expiring’ email????

zeddy

Reply | Quote

Hi all,

This tread is somewhat similar to what I need. I need coding that will look at
the value on column B (Truck #), get all the rows that match to each truck
number and then copy those rows on to an email. On the second tab I have the
list of emails for each truck #. Also I would like to add to the subject line
tomorrows date.
[TABLE=”width: 832″]
[/TABLE]
[TABLE=”width: 832″]
Below is the header of the dispatch board that I want to send out.
[/TABLE]
[TABLE=”width: 832″]


[TR]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Sequence[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Truck #[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]ID[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Unique number[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]WorkOrderNumber[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Has GPS[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]WorkOrderName[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]DriverComment[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Address[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]City[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Zip[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]State[/TD]

[TD=”class: xl67, width: 64, bgcolor: transparent”]Nation[/TD]

[/TR]

[/TABLE]
[TABLE=”width: 832″]

[/TABLE]
[/COLOR][/SIZE][/FONT][TABLE=”width: 832″]
Thanks 🙂
[TR]

[TD=”width: 64, bgcolor: transparent”][/TD]
[/TR]

[/TABLE]

Reply | Quote

Hi Alex

Welcome to the Lounge as a new poster.

I’m testing a solution for post#19, so will have a look at yours tomorrow.

zeddy

Reply | Quote

Hi Alex

Welcome to the Lounge as a new poster.

I’m testing a solution for post#19, so will have a look at yours tomorrow.

zeddy

Hey Zeddy,

I appreciate you quick response, I’ve been working with it and this is what I have so far.
The code below is looking at row B for the email address instead of using a separate tab, and when I run the code it only brings the values of the cells. can I get it the add borders so it looks better?
Lastly my code is only bringing one of the values but a truck will have many loads. I need the code to find and add all rows that Column C (truck #) matches.

Sub test()
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
LastRow = Range(“B” & Rows.Count).End(xlUp).Row
For Each cell In Range(“B2:B” & LastRow)
If WorksheetFunction.CountIf(Range(“B2:B” & cell.Row), cell) = 1 Then
If Cells(cell.Row, 7) = “N” Then
Set OutApp = CreateObject(“Outlook.Application”)
Set OutMail = OutApp.CreateItem(0)
strbody = “Dispatch for tomorrow is:” & vbNewLine & vbNewLine & _
Cells(cell.Row, 1) & ” – ” & Cells(cell.Row, 3) & ” – ” & Cells(cell.Row, 4) & ” – ” & Cells(cell.Row, 8) & ” – ” & Cells(cell.Row, 12) & ” – ” & Cells(cell.Row, 13) & ” – ” & Cells(cell.Row, 14) & ” – ” & Cells(cell.Row, 15)

On Error Resume Next
With OutMail
.To = Cells(cell.Row, 2)
.CC = “”
.BCC = “”
.Subject = “Dispatch for ” & Range(“S1”)
.Body = strbody
.Display
End With
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End If
End If
Next cell
End Sub

Reply | Quote

Hi Alex

..thanks for the info.
I’m a bit behind schedule here.
I’m looking at your code now, and will post back tomorrow.

Regards
zeddy

Reply | Quote

Hi Alex

Your requirement is simpler than thatkiwigirl, so should have you a working solution tomorrow.
But, please check your sample file headings again, as they don’t match what’s in your test code.
(For example..
.Subject = “Dispatch for ” & Range(“S1”)
..you don’t have anything in column

If you want to post another example file with some sample dummy data, that would be easier.
I know what you want, so getting the headings right would be good for you too.

zeddy

Reply | Quote

Hi Alex

I have tested this OK on my system, using Outlook as my mail system, and sending test emails to my gmail account.

To test this version, enter your own email address in column of the sheet named [addresses]
(Note: Sheet named [Data] only has data for the first three trucks listed on [addresses], so you only need your own email address for the first three entries).

1. Start up your Outlook mail system.
2. Load this attached file (with macros enabled)
3. On sheet [emailer], use the ‘clicker’ to select and extract data for each Truck.
4. Click the relevant button to send emails

NOTES:
The data from source sheet [Data] is extracted to sheet [emailer] using AdvancedFilter.
The extracted data matches the column headings used in the named range [outputHeaders].
To ensure an exact match, as per RetiredGeek’s top class recommendations, we use a formula in the cells of the [outputHeaders] range to ensure an exact match of the headings (as required by AdvancedFilter).

can I get it the add borders so it looks better?

In the vba code, I have added some automated formatting of the extracted data.

If you have additional/different column headers in your data range, you should be able to adjust this demo file to suit.
Any questions, just ask.

zeddy

Reply | Quote

Zeddy!!!!!

YOU ARE AMAZING!!!! this is just what we needed and more!!!!

Reply | Quote

Hi Alex

Glad you found it useful.
May the Fourth be with you.

zeddy

Reply | Quote

Hi Alex

Glad you found it useful.
May the Fourth be with you.

zeddy

Zeddy,

One last question, please tell me how can i remove the Unique number column from going in the emails? in General where can i edit the columns that go out?

Thanks again!!

Alex

Reply | Quote

Hi Alex

The data block that is sent in the email body is defined by the headings used in row 8 of the sheet named [emailer].
These are the ‘filter-extract’ headings. You can put these headings in any order you like, but the heading used must match the same heading exactly as per the sheet [Data].

So, for example, if you wanted to include County in the email, you would need to have this as one of the columns in row 8. To make sure the filter-extract headings match exactly the corresponding heading on sheet [Data], you can use a simple formula ‘to point to the required data sheet heading’ e.g. =Data!K1 would be used to ‘fetch’ the County.

So, to exclude the Unique number from the email, just remove it from the [outputHeaders] range by deleting that column! see attached file.
Or, if you prefer, change the heading formula in your file from =Data!D1 to =Data!K1
etc etc etc

You can also adjust the column widths etc in the [outputHeaders] range as required.

You can ask as many questions as you like in this Forum.
This helps others too.

zeddy

Reply | Quote

Hey Zeddy,

I have another small project and I hope your genius abilities can help me.
We handle some shipper location reports that get sent out every afternoon. The information comes from one big database; we have people that actually go into this big file and separate it into location specific files in order to send them out. The idea is to paste the data into an emailer file that could do this for them, it does have some different parameters from the other emailer you did for me. On this one I need to create a new file for every location, the name should carry the location name plus tomorrows date, they need to be emailed as an attachment and saved on the desktop.

Some of the other actions I would like to do are:

Delete Column A “Ship date”
Each Schedule ends up sorted by Shipper and Consignee and commodity
We need to be able to send what Shipper need to be send individually, maybe like a check box and select which ones are ready to go out. Different personnel send different location schedules at different times.
Let me know if you need any additional information.
44828-Shipper-location-Report-Sample

Thanks you very much,

Alex S. 😀

Reply | Quote

Hi Alex

I was still in hospital recovering from major abdominal surgery when you posted your New Project in post#36 above.
Turned out I had a subsequent internal bleed so crash-cart boom and a follow-up emergency operation and then another set of 36 chest staples etc etc etc.
I’m now OK of course, and next week it’s my Staying Alive Tour 2.0 to the Caribbean.

..So, I’m looking at your post today!

zeddy
..more lives than a cat

Reply | Quote

Hi Zeddy,

I am new to this forum and saw your SS Dispatch files which suits my need, but when I run the macro I can just see the glimpse that mail is being getting created. But then I cannot see any mail in my draft or sent items. Just want to know is there any option to display the msg before sending and also using outlook mail instead of mail envelope.

Reply | Quote

Hi Zeddy,

Thanks for your quick response! I have been nutting this out for ages & I am just not knowledgeable enough to figure it out!
Hopefully below is a small example of the spreadsheet/database I am working with. There is more certificates than what is shown, but have just used this one as it is small sized & shows what I am working with.
44360-Coding-Tester

Apologies about not explaining it in full when I first asked the question, here goes nothing:
Via our internal email system the QID is linked to the specific persons email address when placed into Outlook. I am assuming that writing a code that auto generates an email with the QID in the ‘To/Addressee’ in Outlook, it will auto generate the persons email address like it does normally. If it WON’T then go no further as I will have adjust the entire spreadsheet if we cannot get it working from QID’s alone and re-look at it again.

Also, there is a column to the left of each Certificates Expiry date that contains either a ‘Y’ – if the person is certified, OR it will contain a date. This is a ‘booking date’ – meaning that person is currently booked in to re-sit for example a First Aid Cert on that date (booked in obviously because their expiry date is coming up). IF there is a date in this column for the individual certificate, I do not want an email sent, to either the individual or included in ‘The Boss’ email.
Yes if an individual has 4 expiring dates coming up then I would want them to receive 4 individual emails.
Yes I would like The Boss to receive emails per Certificate Type. I would want this sent out at the start of each month. I don’t think there will ever be a situation where ‘none’ are expiring, however if this did occur then yes, a none expiring email would be required.
There will be some Certification dates that are in the past. This is due for example to someone having a broken leg & therefore cannot re-sit first aid etc. If it is possible & not too nitty gritty I would want the past dates excluded from the individual QID email send out, but INCLUDED in The Boss email (I realize this may be pushing my luck haha).

A very big and genuine thank you for any help you can give me, I really do appreciate it! 🙂

Reply | Quote

Hi kiwigirl

Thanks for the info. I believe your assumption is correct about putting the QID into the Outlook addressee field – it should ‘resolve’ to the correct email address for your organisation. I’ve used this method before.

I’ve attached an example file for you to test, with some demo records I created.
On the sheet [emailer], you can use a ‘clicker’ to filter and extract Certification Expiry data. This filtered data would be what you would send to the Boss. For testing, put your own email address in cell [B3]

This demo file doesn’t include the sending of emails to all the individual QIDs. I’m still looking at that – will post another updated file after I’ve tested that.

Any questions, please ask.

zeddy

Reply | Quote

Hi kiwigirl

Thanks for the info. I believe your assumption is correct about putting the QID into the Outlook addressee field – it should ‘resolve’ to the correct email address for your organisation. I’ve used this method before.

I’ve attached an example file for you to test, with some demo records I created.
On the sheet [emailer], you can use a ‘clicker’ to filter and extract Certification Expiry data. This filtered data would be what you would send to the Boss. For testing, put your own email address in cell [B3]

This demo file doesn’t include the sending of emails to all the individual QIDs. I’m still looking at that – will post another updated file after I’ve tested that.

Any questions, please ask.

zeddy

I love what you did here. I played with it and it emails perfectly on my system. Yet I need something a little different…

I want to send individual emails via Outlook to staff that have training unrecorded or about to expire within 45, 30, and 15 days of the end of the training period. I also want to send one email via Outlook to the supervisors the day after the end of each training period about staff that have expired or unrecorded training.

For example…if the training period ends on 9/30/2016, emails should have went out on 9/16/2016 (15 days out), 9/1/2016 (30 days out), and 8/17/2016 (45 days out). An email to the supervisor should go out on 10/1/2016.

Normally I manually create these emails, but we just merged with another company and I have inherited 180 more employees and am pressed for time as the upcoming period is about to end.

Therefore I will need several emailer tabs… One for 45 days, one for 30 days, one for 15 days, one for expired, and one for the supervisors (I like the one you already have, lol).

I will attach a sample of my spreadsheet. I don’t have and Y’s to go on, just dates and blank (unrecorded) spaces. I like the expired and pending colors. I will need the pending colors, one color for expired, and one color for unrecorded spaces. If the space is blacked out that means it’s NA for that person; the space will say NA even though it is blacked out, so no one will need an email for those.

Again, I don’t know enough about VB to attempt this. Please help!

45651-Training

Reply | Quote

Hi steamfunk

I’ll have a look at this and try and post back as soon as I can.

zeddy

Reply | Quote

Hi

OK, have to get my head clear on the request.

So, you have four training periods in your posted sample file.
Each period is Quarterly, i.e covering three months.

The first Quarterly training period ends on 31-Dec-2016 (second quarterly training period ends 31-Mar-2017, etc etc.)
So, for the first training Quarter, emails are due to go out on:
Nov 16th 2016 (45 days out}
Dec 1st 2016 (30 days out)
Dec 16th 2016 (15 days out)
..and Supervisor is due to get a summary email on Jan 1st 2017

I assume the Training Course full names will be in row 2 (“Training 1”, “Training 2” etc etc etc).

For each employee, the record will have NA in the Training columns [E] to [T] for any training course that doesn’t apply to them. If the Training column is empty, then this needs to be reported in the email.

If there is a date in the Training column, what does this date mean? Is it the Expiry date? Is it the Training start date?
Can you give me some more info please.

zeddy

Reply | Quote

Hi

OK, have to get my head clear on the request.

So, you have four training periods in your posted sample file.
Each period is Quarterly, i.e covering three months.

The first Quarterly training period ends on 31-Dec-2016 (second quarterly training period ends 31-Mar-2017, etc etc.)
So, for the first training Quarter, emails are due to go out on:
Nov 16th 2016 (45 days out}
Dec 1st 2016 (30 days out)
Dec 16th 2016 (15 days out)
..and Supervisor is due to get a summary email on Jan 1st 2017

I assume the Training Course full names will be in row 2 (“Training 1”, “Training 2” etc etc etc).

For each employee, the record will have NA in the Training columns [E] to [T] for any training course that doesn’t apply to them. If the Training column is empty, then this needs to be reported in the email.

If there is a date in the Training column, what does this date mean? Is it the Expiry date? Is it the Training start date?
Can you give me some more info please.

zeddy

You have everything basically correct! However, the columns are [D] to , lol.

The date that is listed is the date of last completed training on record, so you can treat it like an expiry date. (Technically, all training that doesn’t have a date within a particular training quarter is technically expired.) So when a person gets a reminder email XX days out it should simply say that their recorded training date is [expiry date] (or if blank then say [unrecorded]); your training will officially expire on [end of quarter date]. Uncompleted training will be reported to management on [day after end quarter date] for non-compliance… or something to that effect.

If there is a training date or NA entered that falls within the training quarter then that person should not get an email or reported to management.

I hope that clears it up a little more; I think I may have confused myself, lol. This thing wasn’t my idea, per se; the merger just happened a couple of weeks ago. Personally, I don’t even think I am making sense, but I don’t make up the “rules” here, lol.

Reply | Quote

Hi

You have everything basically correct! However, the columns are [D] to , lol.

..are you looking at the same file you posted in post#51?????
..it look like E T to me.
Call home!!!

Anyway, I think I now have what I need to work on a solution.
Give me while though – seems I’m in the middle of a tropical wave/storm here.

zeddy

Reply | Quote

Hi

..are you looking at the same file you posted in post#51?????
..it look like E T to me.
Call home!!!

Anyway, I think I now have what I need to work on a solution.
Give me while though – seems I’m in the middle of a tropical wave/storm here.

zeddy

You are right…it is ET…OUCH!!!

I need more coffee to get through today, lol…

I pray all is well where you are; be safe in the weather!

Reply | Quote

Hi

The airport is closed, it’s belting cats and dogs here, no, wait, just dogs.
Looks like it might turn into hurricane Mathew after it passes us. Watch out America next week!
I’ve already had to cut short my Staying Alive 2.0 Tour, returning back to UK for treatment soon.
In the meantime, a drop of Vitamin R* is required.

zeddy
(*that’s Rum)

Reply | Quote

Hi

The airport is closed, it’s belting cats and dogs here, no, wait, just dogs.
Looks like it might turn into hurricane Mathew after it passes us. Watch out America next week!
I’ve already had to cut short my Staying Alive 2.0 Tour, returning back to UK for treatment soon.
In the meantime, a drop of Vitamin R* is required.

zeddy
(*that’s Rum)

Wait…did I just miss a BeeGees reference?! #beegeelove

Reply | Quote

Hi steamfunk

Apologies for the delay! I had to cut short my trip to the Caribbean.
Back at the hospital next day after landing. My surgeons know me inside and out. Especially inside.

Anyway, back to the Training Emailer tool..
I have attached first version for you to look at.
I have tested this OK on my system, with Outlook 2016 and Excel 2016.
It should work with Excel 2010 and Excel 2007, as long as your Outlook version IS NOT newer than your Excel version.

Sheet [FY16 Mandatory Trng Rpt]
I have added some dummy records for testing the emailing.
I hope you liked my sample names. This was the best bit for me.

To enter sample dates, I included a Form to display a Quarterly Calendar for each of the Training Quarters. Click any of the [show Calendar..] buttons in row1 to display the Form.
This will allow you to quickly enter a date into the currently selected cell.

If you want to mark the cell as NA, click the [NA] button on the Calendar Form (bottom-left-corner)
The Calendar Form is displayed as a ‘modeless’ Form, which means it will stay displayed until you decide to close it. This allows you to select cells and click required dates. You can ‘move’ the Calendar Form by dragging it with the mouse. You close the Calendar Form by clicking the button [close this Form] (bottom-right-corner).

Sheet [emailToEmployee]
In cell [B3], enter your own email address for testing the tool. This will send the test emails to yourself.
When you are happy with your tests, just copy cell [E3] to cell [B3] to have the emails sent to the Employee’s email address.

Before you begin:
Use the clicker [1] to show the manual selection of Employee records. Note how the data changes in rows 11 to 16
Use the clicker [2] to select the Training Quarter. Note how the Subject line changes in cell [B4] as you use this clicker.

NOTE:
I have not done the sheet [emailToSupervisor] yet.

Please let me know how you get on.
zeddy
Never Give In
Never Give Up
Never Go Back

Reply | Quote

Greetings:

I am working with Outlook and Excel 2013; I have attached screenshots of the two error messages that I got, both pertaining to the Quarterly Calendar.

Other than that it worked perfectly! YOU ARE THE MAN!!! ;););););););)

V/r,

Steamfunk

45795-error145796-error2

Reply | Quote

Hi Steamfunk

This is what it should look like:
45797-training-1
OK, so I need to check what you have to do to get that Calendar Control available on your system.
It is generally available when you have Access installed, but Microsoft has made it more awkward lately.
I have lots of versions installed on my system, so sometimes I forget about the Calendar Control issue.
Let me check it out tomorrow, and I’ll post back.

zeddy

Reply | Quote

Hi

This is from a post I did here three years ago (Sep 2013)
The Calendar Control (called MSCAL.OCX) was last shipped with Office 2007.
(It is usually included with Access, so would be part of Office Professional, which includes Access)
It is not included in later versions of Office.

In Office 2010 they have replaced this date picker with an updated version in the
Active-X library, called MSCOMCT2.OCX. This is for 32-bit Office, NOT Office-64-bit.
(The MSCOMCT2.OCX is readily available for download from Microsoft)

1.Display the Developer tab of the ribbon.
2.In the [Controls] section, click the ‘Insert’ dropdown. Excel displays a palette of tools you can insert in your worksheet.
3.In the ActiveX Controls section of the palette (bottom), click the More Controls option. (It is the very bottom-right tool.)
Excel displays the More Controls scroll list.
4.Scroll through the alphabetic-sorted contols list..
Depending on your system, you might see..
Calendar Control 11.0
Microsoft Date and Time Picker Control 6.0 (SP6)
Microsoft MonthView Control 6.0 (SP6)

5.Click OK.

zeddy

Reply | Quote

It would be just my luck that I have MS Office 2013 on a Windows 7 64-bit system…:(

Reply | Quote

Hi steamfunk

..all is not lost, as you can use the MSCOMCT2.OCX on a Windows 7 64-bit system!
Its the Office-64bit that is tricky.
Your Office 2013 is almost definitely Office 2013 32-bit.
You can check this by selecting File>Account and then clicking the ‘About Excel’

zeddy

Reply | Quote

Waiting on IT to approve downloading the MSCOMCT2.OCX… :-/

Reply | Quote

Hi steamfunk

The Excel three-month calendar is very useful in an HR Dept as you can use them for things like scheduling interviews, scheduling security clearance tasks etc etc etc.
I hope you get the approval.
Post back to let us know how you get on.

zeddy

Reply | Quote

Hi steamfunk

The Excel three-month calendar is very useful in an HR Dept as you can use them for things like scheduling interviews, scheduling security clearance tasks etc etc etc.
I hope you get the approval.
Post back to let us know how you get on.

zeddy

I’m sorry I have been away; my hubby had emergency hernia surgery, which nearly killed him…So I have been at home for a bit.

So, let’s get back on track… the IT Dept gave me a huge thumbs down on the download…(not surprised…they are software hoarders…lol); I moved offices so I am on a new-to-me PC…with the same old network tomfoolery (again, not surprised).

MS Office here is 32 bit; however I did see Calendar Controls 12.0 in the More Controls…so I tried to play with it and the calendar is still a problem. As much as I could use the calendar function, can I just do without it and manually enter dates?

Looking forward to hearing from you again soon and I pray all is well with you!

45920-email

Reply | Quote

Hi Steamfunk

..hope all is well with your hubby! I’m being admitted to hospital tomorrow morning for some drainage work.
I too had an exciting experience following my incisional hernia repair to my abdomen last June – crash carts, blood transfusions etc etc and re-opened my chest to stop an internal blood leak. It went belly up, as we say.

So this is a bit of a rush job, but now that you have a Calendar Control available, see attached file.

zeddy

Reply | Quote

Hi Steamfunk

So, in the last attached file, I was trying to re-create a three-month-calendar view, using three copies of the ‘single-month-calendar’ on the Form.

This is more complicated than using just a single-month-calendar on a Form.
I just wanted to see if it could be done.
When this 3-month Form is displayed, you can drag it to a convenient location on your display.
It will stay on the screen until you dismiss it (either by clicking the top-right-corner [X] or by clicking the [close this Form] button).
If you select a cell, clicking on a date in any of the ‘month-calendars’ on the 3-month-Form will put that date into that selected cell.
If you move the cellpointer to another cell that has a date in it, that date will be highlighted in the appropriate month on the Form (provided the cell’s date is within the 3-month Quarter of the displayed Form).
This uses the sheet-code selection-change event.
To view that code, right-click on the sheet-tab-name, and select ‘View code’.
The Form also has a button to set a cell to NA (this will show as a black cell via conditional-formatting)

zeddy

Reply | Quote

zeddy,

Very clean and lean code in post #25.

Maud

Reply | Quote

Hi Maud

..many thanks for the compliment. Much appreciated.

zeddy

Reply | Quote

Hi Maud

A long time ago I used a method to get staffnames from a Corporate Email System, using the staff ID.
When the staff ID was placed into the Outlook ‘addressee’ field, it automatically ‘resolved’ this into the corresponding name.

I have attached a sample workbook with my untested code.

I’m unable to test this, but thought you might be able to check if it works or not, or have a better solution.
In matters of emails, you are de man.

This issue was raised by thatkiwigirl in a previous post.

zeddy

Reply | Quote

Hi everyone,

This is a fantastic thread, thank you for all your hard work guys! I have a similar request but I’m not really sure if it’s possible or if it is, it might be out of my technical reach (but I’m certainly willing to give it a shot!). I’m wondering if I could get help on making the attached excel sheet send out an email to the following recipients once its status is changed. Specifically,

• If any of the cells in column L are changed to complete, it’ll email the person in the inform column and the next responsible person in the process. For example, I change K10 to “complete”, it’ll email the person in cell G10 and D19. If I change L19 to “complete”, it’ll email the people in G19 and D20, and so on. If it could leave a time stamp of when it sent the email in the “email confirmation” column that would be great!
o PS: There are 20 different people involved in the project and it’ll need to be able to email any one of the employees if their name is in the cell. I was thinking maybe have the emails be pulled from the first sheet (Name-Email List), so that changing who it emails or the people involved in the project is user friendly. As in, it’ll look for the person’s last name in the cell in the June/July sheet and then search for it in the name-email sheet, and if they are the same, it’ll pull the respective email next to the name. Just brainstorming!
• Additionally, if a row was behind schedule is it possible to highlight the entire row red? For example, column I states the project was due on the 12th, but it’s currently the 13th, and it notices column L is not changed to “complete”. As a result, it’ll highlight the entire row red and send an email out to people in column D, E, and G within that row. However, once the status is altered to “complete”, it’ll remove the highlight. Is that possible?
• Lastly, if it would be possible to color code the Gantt chart to reflect the colors picked in each row, that would be fantastic! Or if you could tell me how to do that, I’m sure I can get something going!

I sincerely appreciate any help or guidance anyone can give. I definitely have stretched beyond my current skill set (thankfully I had a template to modify), but I’d like to deliver on those functionalities to give my team a tool that’ll help them focus more on producing content and not having to worry about tracking team members. Thanks again!

Reply | Quote

Hi everyone,

This is a fantastic thread, thank you for all your hard work Zeddy and Maudibe! I have a similar request but I’m not really sure if it’s possible or if it is, it might be out of my technical reach (but I’m certainly willing to give it a shot!). I’m wondering if I could get help on making the attached excel sheet send out an email to the following recipients once its status is changed. Specifically,

[*]If any of the cells in column L are changed to complete, it’ll email the person in the inform column and the next responsible person in the process. For example, I change K10 to “complete”, it’ll email the person in cell G10 and D19. If I change L19 to “complete”, it’ll email the people in G19 and D20, and so on. If it could leave a time stamp of when it sent the email in the “email confirmation” column that would be great!
[*]PS: There are 20 different people involved in the project and it’ll need to be able to email any one of the employees if their name is in the cell. I was thinking maybe have the emails be pulled from the first sheet (Name-Email List), so that changing who it emails or the people involved in the project is user friendly. As in, it’ll look for the person’s last name in the cell in the June/July sheet and then search for it in the name-email sheet, and if they are the same, it’ll pull the respective email next to the name. Just brainstorming!
[*]Additionally, if a row was behind schedule is it possible to highlight the entire row red? For example, column I states the project was due on the 12th, but it’s currently the 13th, and it notices column L is not changed to “complete”. As a result, it’ll highlight the entire row red and send an email out to people in column D, E, and G within that row. However, once the status is altered to “complete”, it’ll remove the highlight. Is that possible?
[*]Lastly, if it would be possible to color code the Gantt chart to reflect the colors picked in each row, that would be fantastic! Or if you could tell me how to do that, I’m sure I can get something going!

I sincerely appreciate any help or guidance anyone can give. I definitely have stretched beyond my current skill set (thankfully I had a template to modify), but I’d like to deliver on those functionalities to give my team a tool that’ll help them focus more on producing content and not having to worry about tracking team members. Thanks again!

Danny

44878-ProcessMap_Matrix_Template

Reply | Quote

Hi Danny

..I’m looking at your post too!

zeddy

Reply | Quote

Good day guys.

Thank you so much for this lovely topic. it covers pretty much exactly what i want, but my knowledge of visual basic is little to basically none. i have tried to interpret the coding into my scenario but managed to get my outlook to bomb out or just keep on sending a mail (almost like an endless loop). so i am stuck. I have attached the excel spreadsheet file with all needed notes.

I am sure that i will be able to make amendments to coding if it is relevant to my spreadsheet.

Please help!!!!

Reply | Quote

Hi Uncle Charlie

..see posts above.
I’m looking at yours too!

zeddy

Reply | Quote

Thank you for the reply

Good to hear that you are ok.

Reply | Quote

Hi Uncle Charlie

..I’m a bit behind schedule (hospital appointments).
I’ve made great progress on your request.
Just a bit more zooshing up the file and a couple of tests before posting.
I expect to post it tomorrow.
Thanks for being patient.

zeddy

Reply | Quote

Hi Uncle Charlie

..I’m a bit behind schedule (hospital appointments).
I’ve made great progress on your request.
Just a bit more zooshing up the file and a couple of tests before posting.
I expect to post it tomorrow.
Thanks for being patient.

zeddy

No Sir. Thank you for taking the time to look at my situation.

Reply | Quote

Hi Uncle Charlie

So, welcome to the Lounge (I should’ve said that after your first post).

In the attached file, I’ve added some stuff that might make life easier.
I’ve included a ‘datestamp’ that shows when emails were sent.
This is a feature that Maud taught me (it helps prevent sending too many duplicated emails otherwise).
I used two sheets to allow for setup of different email body text etc etc.

To test this file, you could put your own email address in the vba code for To:
..and also comment out the code for the .CC staff email when testing.
You need to have Outlook as your mailing system.
And, as I have discovered during testing, your Outlook version must NOT be newer than the Excel version you are using!

have a look at this, and post back for any questions etc etc.

zeddy

Reply | Quote

Hi Uncle Charlie

So, welcome to the Lounge (I should’ve said that after your first post).

In the attached file, I’ve added some stuff that might make life easier.
I’ve included a ‘datestamp’ that shows when emails were sent.
This is a feature that Maud taught me (it helps prevent sending too many duplicated emails otherwise).
I used two sheets to allow for setup of different email body text etc etc.

To test this file, you could put your own email address in the vba code for To:
..and also comment out the code for the .CC staff email when testing.
You need to have Outlook as your mailing system.
And, as I have discovered during testing, your Outlook version must NOT be newer than the Excel version you are using!

have a look at this, and post back for any questions etc etc.

zeddy

Thank you so much. i will play around with it as soon as i am back from course. i hope i get it to work, but will ask if there is any more questions.

yet again… thank you.

Reply | Quote

Hi Uncle Charlie

I hope you got certified OK.
Please let us know how you got on.

zeddy
We must believe in free will. We have no choice

Reply | Quote

C’mon zeddy, don’t you mean 36 chest ztaples, and your ztaying alive tour. I’m glad you’re OK now!

Eliminate spare time: start programming PowerShell

Reply | Quote

Hi access-mdb

..re “Caffeine, the ultimate debugging tool”

..this is the one I thought they were going to use on me:
45412-rz-stapler

zeddy

Reply | Quote

Very Nice zeddy!

Reply | Quote

Rather than asking for someone to write it for you, tell us where you are stuck and we will help you get past the blockage.

cheers, Paul

Reply | Quote

Hi Paul,

You are right, sorry about this.

It’s my 1st attempt, so please go easy on me! :rolleyes:

What I’m trying to do is to send an e-mail reminder but without having to open the excel file, and the hard part is that I need in the subject line to mention the contents of row 2 (i.e. A, B, C, D etc).

If someone can share his wisdom, I’ll be very grateful!!!

Many thanks in advance.

Reply | Quote

I can see from your macro that you are new to VBA and possibly to programming.
It may be worth waiting for someone with a little more experience to get you started – I can only give you hints as I don’t have Excel these days.

cheers, Paul

Reply | Quote

I am yes, but I am a quick learner.

I just need this first time some help, then I’ll figure it out.

I’ll be waiting for someone more experienced.

Thanks anyway!

Reply | Quote

Hi gogo

Happy to help.
Is the email a reminder to just one Marine Surveyor?
Or is there a different email address per bulk carrier?

I’ll look at it later today and will post something to get you started.

zeddy

Reply | Quote

Hi zeddy! Nice to hear from you!

Just one e-mail address.

I can’t thank you enough…

Reply | Quote

Hi gogo

I’ve been away.
I’m posting what I have so far for you to have a look at.
A good way of learning is to see how someone else would do it.

It’s not finished yet.

Have a look at my layout, formatting etc etc etc.
Click a few buttons; look at the vba code etc etc

Now, what version of Excel are you using?
Are you using Outlook? What version?
You can include file attachements to an email, but we would need to know how to identify them and where they are located (i.e. what folder etc etc etc)

zeddy

Reply | Quote

Hi gogo

I tested the file in post#82 with Excel2010 and the ‘clicker’ (to select the vessel) on the [emailer] sheet worked OK.
Today, I tested the posted file with Excel2016 and the ‘clicker’ generates a ‘paste’ error.
I tested the file with Excel2013 and the ‘clicker’ generates the same error.
So there is something different between how Excel2010 deals with copying and pasting ‘visible filtered cells’ and how Excel 2013 and Excel2016 does it.
I’m looking at this.

zeddy

Reply | Quote

Hi gogo

In the attached version, I added a line of code which seems to fix the reported paste error for Excel2013/2016

Code:

'v1b:
'The following code line seems to be required for Excel2013, Excel2016
'to re-select the source sheet after the previous paste to a different sheet..
Sheets("Surveys Expiry dates").Select       're-select after previous paste

Set zDest = Worksheets("emailer").[B9]                      'paste location

I tested this with Excel 2016 and Outlook 2016, and, after adding my own email address (in cell [C3] on the [emailer] sheet), I was able to send a test email for the AVRA vessel.

zeddy

Reply | Quote

Hi gogo

So, use the file I posted in post#84

Now, to explain the layout:
I used some conditional formatting to put some ‘traffic light’s’ on the due dates:
Red – overdue
Yellow-due in the current calendar month
Green – OK

We need to discuss the existing red (overdue) entries – should a separate email be used for those overdue items?
Also, we need to have some method of keeping track of email reminders that have been sent.
You will note that, currently, in my posted file, I opted to ‘lump’ all items due in the current month into one email (rather than create say ten or twenty emails in the same month for the same vessel).

zeddy

Reply | Quote

Good day Zeddy!

I honestly cannot thank you enough for all your hard work and prompt reply. In fact, I think you should teach me how to do it!

I use ms office 2016, and I think you got it all right (the traffic lights, the lump items etc.) Everything is perfect, although it will take me some time to study all your programming / coding, thus I will have to revert on this.

Re. the overdue items, there is no need of a separate e-mail, as they will be none. And I can see that you have inserted a button, which I assume will sum all reminders sent up to “today”, a great idea!!! Finally, there is no need of inserting an attachment, since I will include a hyperlink in the body of the message.

Please let me know if I can do smt for you…

MANY THANKS!!!:D

Reply | Quote

Hi gogo

Many thanks for your reply.
Well, if you are happy then I shall consider this as a good result.
If you have any questions later, just ask here.

zeddy

Reply | Quote

Just one thing zeddy,

When I press the “send emal” button in the 1st sheet I get the “this routine hasn’t been defined yet”, whereas when I press the button in the emailer (although I have inserted in c3 the correct address) I get nothing.

Do I have to do smt? Am I doing smt wrong?

Reply | Quote

Hi gogo

You aren’t doing anything wrong.

On the first sheet I had planned to have a [Send Email..] button for each vessel (with a matching button colour of course), but this task can now be done on the [emailer] sheet using the ‘clicker’ etc.

To assign a macro to any shape, you simply right-click on the shape and choose the ‘Assign Macro…‘ option from the displayed right-click-menu. If a macro has already been assigned, you can select the [Edit] option to take you to that assigned vba routine, otherwise you pick the required vba routine from the available list i.e. from the routines you’ve already created.

On the first sheet, we could still create [Send Email..] buttons for each of the vessels if you wish.

The routine would examine ‘where’ the [Send Email..] button is located, i.e. check which column it is in, then we can use this info to get the relevant corresponding vessel. We would also need to check whether there were any reminders due in the current month for that particular vessel, and display a message if there weren’t any reminders due. If there were reminders due, we could set the required corresponding ‘clicker’ value on the [emailer] sheet to match that required for that vessel, and then call that clickerFilter routine that was assigned to the clicker shape.

zeddy

Reply | Quote

Good Evening,

I found this forum and thread and it looks very like what i need some assistance with.

I have just started learning VBA, seems you can do some pretty cool things with it!

I am trying to create a spreadsheet that automatically sends an email once a value is changed in cell E5 based on the fact it is overdue a service by however many hours.The problem is I can’t figure out how to then send another email if the cell changes again without resetting the cell… if i put 0 in it will send another email but i just want to type the updated hours in without having to do that every time.

For instance the unit is due a service at 7001 according to current service schedule.
eg current hours 7000

type in cell current hours now say 7005…thus overdue by 4 hrs
email sends…

then a few days later the machine hours are input again and if i’m not given a service sheet to update service hrs, which should be a set amount untill the next service, the overdue hours are now, lets say 7015. At this point i would like another email sent showing the increase in hours

first email reads

Hi Duncan
A service is required on the following piece of machinery : B.4.61.001
Equipment hours are over the recomended service schedule by : 4
Thanks for your assistance

After i input 7015 directly in to the sheet i want another email sent saying

Hi Duncan
A service is required on the following piece of machinery : B.4.61.001
Equipment hours are over the recomended service schedule by : 14
Thanks for your assistance

I have attached a portion of the sheet

Any help would be great!

Thanks in advance

Reply | Quote

Hi Absinthe

Welcome to the Lounge as a new poster!

I’m looking at your example now, and will post back.

zeddy

Reply | Quote

Hi Absinthe

..see attached.

Essentially, I changed the event trapping to detect whenever a User makes a change to the Current Hours range, rather than using the Calculation event. This is easier to follow. And you can use the method for other things.

I also added ‘data validation’ to ensure User can only enter (whole numbers) in range 1 to 10000 for the Current Hours data entry range.

We record (and keep) the date that the last email was sent in column F.
(I assumed you are not expected to send two emails for same Plant Code on the same day).

Note, to test the emailing twice on the same day for the same Item, you will need to clear the ‘last date email sent’ in col F.

Hope this helps.

zeddy

Reply | Quote

Excellent Effort Zeddy!!! 😎

Much obliged, this functions they way I was wanting it to!

Could you recommend any good literature on the subject of VBA? websites to learn from?

Thanks Again!!!

Reply | Quote

Hi Absinthe

My feeling is that any book on VBA is worth whatever you pay for it, even for just a few insights.
A good way of learning vba is to study code written by others.
I’ll have a think about my favourites, but would encourage others to post their suggestions for you too.

zeddy

Reply | Quote

Plenty of free sources to learn in Internet. Just google “VBA Tutorial”

Reply | Quote

Hello Zeddy,

I am a new poster to this site. I found it while searching to see if there’s a way to automate emails in excel based on the values of cells. I see there is. I am thoroughly impressed at the help that you and others have provided to seekers such as myself. I also see that you have some beautiful functionality in your sample excel files of:

•”Please help-zeddy1.xlsm” of post #47
•”emailer-kiwigirl-zeddy-1.xlsm” of post #25

I have a similar need to send out the emails and believe that my request is probably simpler to adapt. I, unfortunately, can’t seem to figure how to edit the vb code for my needs. You will see that I tried tinker with the vb code of an earlier example I found and adopt it for my needs, but not sure of what I’m doing. :mellow: Essentially, I used conditional formating to highlight when a 3 year training comes due, and now want to be able to send out emails for when it’s due and when it’s expired. To be able to add filtering would be neat/great.

I would also be interested in the functionality of auto or manually sending out emails based on the date, to see when the last email was sent, and not sending an email if it was recently sent. Currently, my example file is set up with individual emails, but am looking at using a safety coordinator per company as an option of who to send the list of employees that need to have updated training.

Any assistance that you can lend to this would be greatly appreciated. I’m on a tight timeframe of the end of the year and being out of the office, so if it’s a fast turnaround it would be great, but can also wait with respect to your time and priorities.

46211-Train-date-test

By the way, I saw the post regarding your health and I’m glad to know that you are okay and hope you don’t have any lasting issues, being a person who’s gone through a few surgeries myself!

Thanks in advance,

Arras

Reply | Quote

Hi Arras

Welcome to the Lounge as a new poster!

I’ll have a look at this today and will post back as soon as possible.

zeddy

Reply | Quote

Hi Arras

..I’ve got the filters working and I’m testing sending a list of names to the Safety Coordinator for those whose Training has expired, and those whose Training will expire within six months.

Not quite ready to post my solution.

zeddy

Reply | Quote

Hi Arras

This is what I have so far.

I changed the layout to give some room at the top on sheet [Data], and added some columns.
You can use Filters on this [Data] sheet, and I’ve assigned some vba to shapes to quickly filter-by-conditional-format colours. I added some formulas in row 8 to show counts for unique entries in a filtered list.

In column [K] of sheet [Data] I added a formula to show the ‘visible-record-number’
This ‘visible-record-number’ adjusts automatically as data is filtered.

We can now use a MATCH formula in cell [C1] on sheet [emailToStaff] to return the actual row-number for filtered-records. This allows us to use a clicker button to ‘cycle’ through the filtered records and fetch various items of interest for the email. The max clicker value is set via the sheet activate event for sheet [emailToStaff]. Right-click on that sheet tabname and select ‘View Code] to see this code.

I haven’t properly tested the emailing yet with this posted file, but this assumes you will be using Outlook.

Please let me know how you get on with this example. And if anyone wants to chip in that would be good too.

zeddy

Reply | Quote

A similar issue and can’t get this code to work for the life of me. I have attached a speadsheet with what im lookign for if anyone can assist.

I need Excel to send out emails 90,60,30 and 10 days prior to an expiration date. The email would go to the employee’s supervisor and the HR Partners based on the data from the table.
The email would have to include the Employee’s name, ID and Date of expiration.

The body would have to include 3 word document attachments

And also a detailed list of instructions, within the body, it would need to generate the employee name and expiration date as well from the table in specific areas.

I have attached an example of the spreadsheet with also the wording that the email would need. If anyone could please assist!

46359-Exp-Test

Reply | Quote

Kyle,

Attached is your modified spreadsheet that will send emails as you requested based on a time period between today’s date and the expiration date. The code will cycle through the list of names (unlimited number). The emails will be sent to the supervisor and cc’d to the HR partners. A return receipt will be generted when the supervisor views the email. A comment is placed in the appropriate column D-G time stamping the date that the email was sent. The comment also acts as an inhibitor to resend an email that was already sent. If you would like to resend the email then just delete the comment.

The email will be generated and displayed for 3 seconds allowing you to have a quick view then is automatically sent. It is assumed your email client is Outlook and it is open at the time you run the code.

HTH,
Maud

generated email:
46360-Kyle1

Comment added as a time stamp:
46361-Kyle2

Reply | Quote

Kyle,

Attached is your modified spreadsheet that will send emails as you requested based on a time period between today’s date and the expiration date. The code will cycle through the list of names (unlimited number). The emails will be sent to the supervisor and cc’d to the HR partners. A return receipt will be generted when the supervisor views the email. A comment is placed in the appropriate column D-G time stamping the date that the email was sent. The comment also acts as an inhibitor to resend an email that was already sent. If you would like to resend the email then just delete the comment.

The email will be generated and displayed for 3 seconds allowing you to have a quick view then is automatically sent. It is assumed your email client is Outlook and it is open at the time you run the code.

HTH,
Maud

generated email:
46360-Kyle1

Comment added as a time stamp:
46361-Kyle2

you are a life saver! you have no idea how much time this will save!

Paul – Yes, I work for a company that has around 90,000 empoyees with 32 sub companies. Lots of people to track! You would think they would have come up with a better way by now, but they have been doing the E-mails manually for years!

Reply | Quote

you have no idea how much time this will save!

I suspect Maud has some idea, been around long enough to get a feel for these things. 🙂

cheers, Paul

Reply | Quote

Wow, you really want to fill up people’s inboxes. 🙂

cheers, Paul

Reply | Quote

Without looking at the macro, there should be a line that says “mail.Send” or similar. Change it to “mail.Display”.

cheers, Paul

Reply | Quote

Hi Paul,

Thanks for the prompt reply, I have already tried that before but its still not working. Can you pls help??

Also how to change to code – instead of mailenvelope I want to use outlook for sending mails.

Reply | Quote

Hi Avinash

What version of Excel and Outlook are you using?
Do you have Outlook running when you try the Excel SS Dispatch file?
(Note: The MailEnvelope method will send the email using Outlook)

zeddy

Reply | Quote

Hi Zeddy,

I am using office 2016, I have tried with both outlook open and closed.

When I run the macro, I only get the glimpse. Rest no idea whether mail was generated or not as I cannot see it in either outbox or in sent item.

Reply | Quote

Hi Avinash

I’ll have another look and see if I can figure out what is happening with your system

zeddy

Reply | Quote

Hi Avinash

I’ll have another look and see if I can figure out what is happening with your system

zeddy

ThanksZeddy

Reply | Quote

..still working on it.

zeddy

Reply | Quote

Not working how? Does the mail display?
Try adding some debug points before the mail section to see where it gets to.

cheers, Paul

Reply | Quote

Hi Zeddy,

I am working on a similar macro – I need to send an email to an individual based on the below when I refresh the sheet:

If E2 =”Yes”

Then send:
E-mail To = F2
Subject = I2
Message Content = G2

Would you be able to suggest how to make this macro? I’m hoping it would be much more simple than the previous items, but I’m very new to this – never made a macro before without recording it.

I would need this to continue through all rows. I’ve added a censored version of the file I’m working on.

Reply | Quote

What should happen to E2 when an email has been sent – to prevent email being sent every time?

cheers, Paul

Reply | Quote

What should happen to E2 when an email has been sent – to prevent email being sent every time?

cheers, Paul

Hi Paul,

If it would cause a looping issue, then I suppose it should be cleared – if not, I would prefer it to keep the same value as it is a calculated field. I would be running the macro daily and want it to send for the same item if it has been run again.

Thanks

Reply | Quote

So you want the same email sent every day if E2 = “Yes”?
Are you likely to be running the macro more than once per day? If so we need a “mail sent today” column.

cheers, Paul

Reply | Quote

So you want the same email sent every day if E2 = “Yes”?
Are you likely to be running the macro more than once per day? If so we need a “mail sent today” column.

cheers, Paul

I would run the macro once per day. I suppose it would be better if there was a 2nd email for items that required a second follow-up to the same person, such as adding “2nd Request” to the email, but it is not absolutely necessary as the user may change. The whole data set will change each day, I will be pasting It into Columns H through U, which will remain constant, but the number of rows will change.

Reply | Quote

After much research, this is what I was able to make work: J1 = a count of the total rows so the macro knows when to stop.

Private Declare Function ShellExecute Lib “shell32.dll” _
Alias “ShellExecuteA” (ByVal hwnd As Long, ByVal lpOperation As String, _
ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Sub SendEMail()
Dim Email As String, Subj As String
Dim Msg As String, URL As String
Dim r As Integer, x As Double
CC = Range(“J1:J1”).Value
For r = 2 To CC
Email = Cells(r, 1)
Subj = Cells(r, 3)
Msg = “”
Msg = Msg & “Hi ” & Cells(r, 2) & “,” & vbCrLf & vbCrLf
Msg = Msg & “” & vbCrLf & vbCrLf

Msg = Msg & “Regards” & “,” & vbCrLf & vbCrLf
Msg = Msg & “My Name” & vbCrLf

Subj = Application.WorksheetFunction.Substitute(Subj, ” “, “%20″)
Msg = Application.WorksheetFunction.Substitute(Msg, ” “, “%20”)

Msg = Application.WorksheetFunction.Substitute(Msg, vbCrLf, “%0D%0A”)
URL = “mailto:” & Email & “?subject=” & Subj & “&body=” & Msg

ShellExecute 0&, vbNullString, URL, vbNullString, vbNullString, vbNormalFocus

Application.Wait (Now + TimeValue(“0:00:02”))
Application.SendKeys “%s”
Next r
End Sub

Reply | Quote

Sorry, I shouldn’t have left you hanging, but I don’t have Excel these days so can’t do any macro work. Normally one of our Excel experts would step in with specific information.

Did you download one of the spreadsheets in this thread for some ideas?

cheers, Paul

Reply | Quote

No worries Paul. I did download some, but did not find what I was looking for. Ultimately I used what was created here:

http://spreadsheetpage.com/index.php/tip/sending_personalized_email_from_excel/

Reply | Quote