Tech Support Guy banner
Status
Not open for further replies.

Request Help: Further study on Auto-mail for due date reminder

1K views 8 replies 2 participants last post by  samd007 
#1 ·
I had made one excel to send automatic due date reminder mails via Groupwise (made with the help of your forums only).
The problem I am facing is as below:
[Also note, problem is not whether Groupwise or Outlook, problem is its generating multiple emails for each due item]

This is generating multiple emails and even the items not due are generating blank mails.
I would like to generate only one mail with different items different expiration dates.
After that group the identical addressees and compose one message with all the relevant data.

Right now its sending 3 mails as:

Mail 1: Please take notice of the following expiration date(s):
-Equipment A Job aaaaa expiration date : 19-Aug-12 -394 days.
-Equipment A Job aaaaa OVERDUE : 19-Aug-12 -394 days.
Sent at 17-Sep-13 11:11:04 AM

Mail 3: Please take notice of the following expiration date(s):
-Equipment C Job cccccc expiration date : 19-May-13 -121 days.
-Equipment C Job cccccc OVERDUE : 19-May-13 -121 days.
Sent at 17-Sep-13 11:11:04 AM

And the ones not due, going blank, i.e: the mail body (Mail 2) text reads:
"Please take notice of the following expiration date(s):
Sent at 17-Sep-13 11:11:04 AM"

What I wanted was a single mail with following in the mail body (Example):

" Please take notice of the following expiration date(s):
-Equipment A Job aaaaa expiration date : 19-Aug-12 -394 days.
-Equipment A Job aaaaa OVERDUE : 19-Aug-12 -394 days.
-Equipment C Job cccccc expiration date : 19-May-13 -121 days.
-Equipment C Job cccccc OVERDUE : 19-May-13 -121 days.

Sent at 17-Sep-13 11:11:04 AM"
Is this possible? Please check my VBA code & kindly help me resolve this.
 

Attachments

See less See more
#3 ·
Good morning again,
I cannot really test it because I do not have the Gropuware library reference her, I could edit the code and add the Outlook reference and do it but that won't be necessary.
I added the following code

Code:
    'Added fields
    Dim eDefault As Range
    Dim isData  As Boolean      ' Boolean to be set to true if there is data to be sent
You need an extra check that sets the isData to True and then at the edn of the run if IsData = True do the rest of the sending tasks.

I've attached the editted file so you can check is.
 

Attachments

#4 ·
Good morning again,
I cannot really test it because I do not have the Gropuware library reference her, I could edit the code and add the Outlook reference and do it but that won't be necessary.
I added the following code

Code:
    'Added fields
    Dim eDefault As Range
    Dim isData  As Boolean      ' Boolean to be set to true if there is data to be sent
You need an extra check that sets the isData to True and then at the edn of the run if IsData = True do the rest of the sending tasks.

I've attached the editted file so you can check is.
@Keebellah, many thanks. But now I am getting compile error as seen in the screenshot
 

Attachments

#7 ·
The value in Column K is 1 or 0.
I notice that you set the value in K at the end of the routine.
Shouldn't this be the first step?
Set a 1 if it's due or not?
The whole code could use a good makeover, why the 331 rows, what happend when you have more data?
Now you go through 331 rows and there is nothing, that costs time.

To be specific:
One run though all the rows and create one single message that is to be sent to all the recipients?
 
#9 ·
Dear Keebellah,

I tried my level best & made a few spelling etc corrections to remove the error messages. Now the file is like as attached.
However, in the "This Workbook" view code, after
mailMessage = getReminders
there is a code:
If Len(Trim(mailMessage)) = 0 Then Exit Sub ' if there is no message exit sub and stop

If I keep this, then when I open excel, nothing happens, and if I remove this clause,
Then for the yellow hi-lited rows, mail sent becomes True, date sent is also updated, but status check remains "0" (supposed to change to "1"), and one mail generates but sadly its a blank mail with "To" and "cc" field blank & message body reads:
"
Sent at 17/09/2013 11:35:27 PM" - that's it? Please help. I think you are directing towards the right path, only some tricky part missing.
 

Attachments

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