Replies

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.

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.

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!

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

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!