Tech Support Guy banner
Status
Not open for further replies.

Solved: Excel - Auto Email Problem

2K views 20 replies 2 participants last post by  bomb #21 
#1 ·
Hi, I have download a template from http://www.rondebruin.nl/sendmail.htm and make some modification to create a tracking program for own use. Now, I would like to add in some features to my tracking program which hope it will serve better. I have few questions here which would like to seek for the expert support.

Refer to the attachment:
1. How could I make the email to send out the OPEN and OVERDUE items only(excluded the closed items)?
2. I have set the conditional format, it work in the Excel but not work in the email. How could I solve this (It's just shown differently)?

Hope to hear from you soon.
Thanks in advance.

Regards
Ykit
P/S : I am using Excel 2007 and Outlook 2007.
 

Attachments

#2 ·
"How could I make the email to send out the OPEN and OVERDUE items only(excluded the closed items)?"

Ron's code has a line:

FilterRange.AutoFilter Field:=FieldNum, _
Criteria1:=Cws.Cells(Rnum, 1).Value

You could add a line after like:

FilterRange.AutoFilter Field:=8, Criteria1:="<>Closed"

to filter out any closed records. But if that's the only change made, blank emails (i.e. just the headers w/no actual records) will be generated (or even sent, depending on how you're using it).

Since the Status formulas don't cater for "Closed", how is that being updated?

(welcome to the board :) )

PS: I don't understand "it work in the Excel but not work in the email."; the email is HTML, not Excel.
 
#3 ·
Step 1.

Create 2 dynamic named ranges for "FilterExample". Press CTRL+F3, enter Assigneds for the name and:

=OFFSET(FilterExample!$B$17,1,0,COUNT(FilterExample!$A:$A),1)

for "Refers To". For the second, enter Statuses for the name and:

=OFFSET(Assigneds,,6)

for "Refers To". Click OK.

Step 2.

Mod the code (sorry Ron :eek: )

You're looking for the Send_Row_Or_Rows_1() sub, it's on Module 7. Find Ron's comment:

'Count of the unique values + the header cell

, then paste the following directly below it:

Range("A1").CurrentRegion.Offset(1, 1).FormulaR1C1 = _
"=IF(SUMPRODUCT((Assigneds=RC[-1])*(Statuses<>""Closed""))=0,#N/A,1)"
Columns(2).SpecialCells(xlCellTypeFormulas, 16).EntireRow.Delete
Columns(2).Delete

That should filter out (on the temp added sheet) any "assignee" with "Closed" records only.

To test, change H20 on "FilterExample" to "Closed", then run the code. You should get 2 emails only (i.e. nothing for "assignee" Dave).

HTH
 
#4 ·
Thanks for the fast reply. It's work very well....:)

Regarding my second question : "it work in the Excel but not work in the email." the problem is what it show in the Excel and in the email are different. E.g. When the item overdue, it will show in RED font in the column. It's work well in the Excel but when the email generated, those items that have not overdue, it's also show in RED font.

MAy cause by bugs....
anyway, really thanks a lot and appreciate your time for support.
Thank you.
 
#5 ·
You tested it that quickly?!? :eek:

About the fonts: sometimes. I see that for Tom, 6 & 7 Due Date is red in the worksheet, black in the email. But for 8 it's red in both.

Have you checked Ron's codes for one that places the worksheet range in the email body as a picture maybe? He probably has something like that, he's a genius after all. :)
 
#17 ·
While I hardly ever check my TSG mailbox, on this occasion I made an exception. There was nothing new.

Post it in here. I'm off out soon, there are other coders way more proficient than me, & it might be something really basic. :)
 
#18 ·
Hi, thanks for your help yesterday. Today, when I try to put in more data (more than 100)in my Excel file, I face a issue. Is there any limitation?
Also, I had created 16 person name and email address but it not send out the email to everybody. It just send to 14 person. Another 2 person do not have the email eventhought they still have OPEN items. How I should fix this? Thanks in advance.
 
#19 ·
"any limitation", none officially that I know of -- however, Ron's code includes automated autofiltering which I personally have found can be ... "glitchy".

"How I should fix this?"

PM me your email address -- I guess.
 
#20 ·


BTW, I found something strengh for me. Now all name are workable except the name Ain. I do little modification and change the name to Aink then its work.

I think overall I had fix the problem. If you have better suggestion, it's always welcome.

Thanks a lot.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top