Alert when an email hasn't arrived

[nugby]

Hi All, have spent a couple of hours Googling this hoping to find a third-party app to do this for me but I've given up.

I'm meant to receive a certain email every day called *** ODS JPM VD Loaded - VD_ALL_D2_20090223_N.csv***, where the 20090223 is a date in yyyymmdd format. It's so rare that I don't receive it that I tend not to notice when it's missing.

Obviously, I can't set Rule Wizard to tell me if the files not there (and I don't just want a reminder in Calendar to check it daily) so is there a way I can schedule Outlook to check if I've received an email called "*** ODS JPM VD Loaded - VD_ALL_D2" and then a wildcard for that day.

Alternatively, is there a way I can schedule Outlook to run an SQL Query (already built - it just shows all files loaded to the Database in question but I could expand it to a BOOLEAN check) at a set time.

Thanks,
Nathan

[Charlize]

At what time do you have to know that a certain email hasn't arrived ?

Charlize

[nugby]

I'd probably set it to run at 2:30pm for the email check or 7:30pm for the SQL check

[Charlize]

I'd probably set it to run at 2:30pm for the email check or 7:30pm for the SQL check
1. You could use the event-handler in outlook to check the subject of the emails that arrive. If a certain e-mail arrives (check on the subject), save it by using the subject (if the subject has always the same format (only the date is different) and if it's a unique subject (not more then one mail a day with this kind of subject).
2. Using the task scheduler of windows, you can autorun certain programs (batfile is what I have in my mind).
3. The batfile uses the current date to check in the directory of the saved e-mails that there is a file with a certain name. Since only the date is variable, it's doable to write a cryptic command in the good old msdos-language to see for a certain file using today's date.

Probably at monday I see some spare time for this. If you can wait until then for an answer.

Charlize

[nugby]

I presume by Batfile you mean Batch File? Figured I'd start with this as I've never used one and been meaning to learn what they can do. If I can create a file called C:\MailHist\tempfile.txt when I receive a file with '*** VD' etc in it, this Batch File will do exactly what I want (I wasted so much time trying to get an ELSE statement working on the end of this but realised I could just break the query apart and for some reason it worked:


@echo off
if not exist C:\MailHist\tempfile.txt echo VD File Not Received
if not exist C:\MailHist\tempfile.txt Pause
IF EXIST C:\MailHist\tempfile.txt del C:\MailHist\tempfile.txt

I'll see if I can find something in the forums on Event Handlers (why the Rule Wizard won't let you export an email to a text file is beyond me) and finally look up Windows Scheduler but I think you've definitely come across a solution! No huge hurry as you can see from me waiting until Monday morning (it's 10:28 AM Australian time on Monday at the moment).

Edit - 11:05 - Scheduler part set up and tested.

[nugby]

Finally found an answer on the attached link:


Sub saveemail(myItem As Outlook.MailItem)
Dim m As MailItem
Set m = CreateItem(olMailItem)
m.SaveAs "c:\temp\New Power Indices.html", olhtml
End Sub

I can create a blank html file using 'Rule Wizard - Run a Script' which will get hit by the Batch File at 2:30 pm each day. Thanks for putting me in the right direction, Charlize, and for getting me to find out about Batch Files.

Edit: Looks like I can't post links but the topic was "Save Email message as HTML file on Harddrive " in Microsoft's "Outlook Programming - VBA" Forum (http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.outlook.program_vba&tid=180d1150-2073-44f4-ad70-70d22b4e8e74&mid=cd5bb5ac-8cc1-4462-9d39-80b30a1c566c&cat=en_US_f2349ded-ffa8-474e-a28d-ef611f1d45b4&lang=en&cr=US&sloc=&p=2)

[Charlize]

Paste this code in the application_newmailex event handler ie. only de coding in between sub ... end sub and not the declaration of the sub.
It will check on every new mail that arrives and will handle the unread items with a check on the subject. I've had some problems with receiving multiple mails all at once so decided to do a check on whole the inbox. Hope you clean your box from time to time ie. not more then a couple of 1000 items in it. I think that this event will fire after the processing of the rules that a user has set up (if someone is certain about this, just inform me).


Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
'*** ODS JPM VD Loaded - VD_ALL_D2_20090223_N.csv***
'the mailitem
Dim myItem As Outlook.MailItem
'loop through mailitems
Dim myloop As Long
'the inboxfolder
Dim myfolder As Outlook.MAPIFolder
Set myfolder = Application.Session.GetDefaultFolder(olFolderInbox)
'loop through all the mailitems in inbox
For myloop = 1 To myfolder.Items.Count
'if it's a mailitem do something
If myfolder.Items.Item(myloop).Class = olMail Then
'declare myItem as the mailitem we are going to handle
Set myItem = myfolder.Items.Item(myloop)
'we only want to look at the unread items
If myItem.UnRead = True Then
'if subject equals the string we want
If Left(myItem.Subject, 34) = "*** ODS JPM VD Loaded - VD_ALL_D2_" Then
MsgBox "This is the one : " & myItem.Subject
myItem.UnRead = False
Else
MsgBox "This is something else : " & myItem.Subject
End If
End If
End If
Next myloop
End Sub

Charlize

[Charlize]

A little variation on this theme. The message that you want to check will be saved to a certain directory in a certain format. The batfile you'll need will be attached to this message. You can choose to modify the file before running it to see the commands that I've used for checking a msg file in a directory. The code must be placed in the ThisOutlookSession under Application_NewMailEx. The directory must exists (I've used C:\Data)



Private Sub Application_NewMailEx(ByVal EntryIDCollection As String)
'*** ODS JPM VD Loaded - VD_ALL_D2_20090223_N.csv***
'the mailitem
Dim myItem As Outlook.MailItem
'loop through mailitems
Dim myloop As Long
'the path to save to
Const mypath As String = "C:\Data\ODS_JPM_VD_Loaded_-_"
'the inboxfolder
Dim myfolder As Outlook.MAPIFolder
Set myfolder = Application.Session.GetDefaultFolder(olFolderInbox)
'loop through all the mailitems in inbox
For myloop = 1 To myfolder.Items.Count
'if it's a mailitem do something
If myfolder.Items.Item(myloop).Class = olMail Then
'declare myItem as the mailitem we are going to handle
Set myItem = myfolder.Items.Item(myloop)
'we only want to look at the unread items
If myItem.UnRead = True Then
'if subject equals the string we want
If Left(myItem.Subject, 34) = "*** ODS JPM VD Loaded - VD_ALL_D2_" Then
myItem.SaveAs mypath & Split(myItem.Subject, "_")(3) & ".msg", olMSG
myItem.UnRead = False
End If
End If
End If
Next myloop
End Sub

Charlize

[nugby]

Ah, finally worked out NewMailEx. I'm running Outlook 2002 (I.T. have 2003 but they never rolled it out to the rest of company. I'm a Business Analyst in Corporate Operations but enjoy a bit of this kind of work) so haven't been able to find/run this.

Looks like my Rules Wizard / Script thing's working and I have thousands of emails in the Inbox (I've got Auto-Archive set for six months and I still need to access those old emails at times!) anyway. I get at least twenty-something Database generated emails every Business day and it can get over fifty if things are being reloaded. Having said that, your Batch File's a bit better than mine so I might take a few things from that. Thanks Charlize!

EDIT: Btw, I never really explained it but the Date on the email is actually two Business days beforehand. We get the confirmed trades back from the Custodian (who buy the Stock for us) two days after the "effective" day of the trade with all the values shown for that effective date. I wasn't going to bother checking the date was valid as the purpose of all of this is to alert to the days when the Custodian's FTP Connection is down (that's when I don't get the email), just needed the Process to work even though there was a Wildcard inherent in the email name.

[Charlize]

You can always count backwards. So not 1 to the no of items in inbox but no of items in inbox to 1. Inside this loop a check on the today date. If your mails are sorted on date, you can skip the loop when the date of the mailmessage is earlier then today's date.

Charlize

[Charlize]

I pushed it a little further. Substract two days of the current day in a batfile and check on that file in the directory by using the resulting day.

But what if the day is a monday, do we need to substract 3 days ... ?

Charlize

[nugby]

Yeah, being Business Days, on Monday's, I'd theoretically expect Thursdays, on Tuesday's, Fridays, etc. However, I more need to see that any VD email is being sent from this source (at all) rather than verifying the date on the email, so I think you'd given me everything I need for this Project (I'll use an SQL query as part of an SQL Server SSIS package when I'm eventually given access here to start checking if Service Level Agreements are being met)! I still wouldn't be able to use Application_NewMailEx though, so I couldn't utilise the majority of these new features.

[corne99]

Hi Nathan. A bit late response but checkout smtpviewer.com .The site does what you require by warning you when a specific period has passed without you receiving an e-mail form a specified address. It also allows for warning you about e-mails arriving with specified keywords in the subject or body of the e-mail.
Have fun. Corne

[burgDD]

If you set a filter to alert you when you receive a message, and possibly even move it to a special folder then you can also set a reminder to ask you, if you have received the email you need today. And you would have a visual indicator, if there is not an unread message.